Getting all month from a range based query
I have this data where I have the start and the end dates of a rental. I need to get it by months like the the below table.
I tried by using a while loop and calling each month as parameter, then doing the calculation but I got many separated tables and union all didn't work or I didn't manage it right.
Do you have nice solution for this task?
You can use an ad-hoc Tally/Numbers table if you don't have a Calendar Table
Select name ,startRental ,endRental ,durationDays = sum(1) ,month = month(D) ,year = year(D) From ( Select * From YourTable A Join ( Select Top 10000 D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select NULL)),'2000-01-01') From master..spt_values n1, master..spt_values n2 ) B on D between startRental and endRental ) A Group by Name ,startRental ,endRental ,month(d) ,year(d)
name startRental endRental durationDays month year a 2019-06-30 2019-07-07 1 6 2019 a 2019-06-30 2019-07-07 7 7 2019 b 2019-03-02 2019-04-03 30 3 2019 b 2019-03-02 2019-04-03 3 4 2019 c 2019-01-01 2019-01-30 30 1 2019 d 2019-01-01 2019-05-01 31 1 2019 d 2019-01-01 2019-05-01 28 2 2019 d 2019-01-01 2019-05-01 31 3 2019 d 2019-01-01 2019-05-01 30 4 2019 d 2019-01-01 2019-05-01 1 5 2019
Note: I picked an arbitrary date 2000-01-01 and 10,000 days (max date of 2027-05-19).
Also, I'm not sure I agree with your June 2019 duration of 2 days. It should be 1.
Get Months Within a Date Range With SQL Query, SQL Query 2. DECLARE. @start DATE = '20120201' , @end DATE = '20120405' ;WITH Numbers (Number) AS. (SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_ID) FROM sys.all_objects) SELECT DATENAME(MONTH,DATEADD(MONTH, Number - 1, @start)) Name,MONTH(DATEADD(MONTH, Number - 1, @start)) MonthId. FROM Numbers. Power Query has some great functionality out of the box for working with date ranges when you want to do this in days, but takes some thought for other intervals. Here’s an example for months.
I am thinking a recursive CTE:
with cte as ( select name, startrental, endrental, durationdays, (case when eomonth(startrental) <= endrental then day(eomonth(startrental)) - day(startrental) + 1 else day(endrental) - day(startrental) + 1 end) as monthdays eomonth(startrental) as monthend from t union all select name, startrental, endrental, durationdays, (case when eomonth(monthend, 1) <= endrental then day(eomonth(monthend, 1)) - day(startrental) + 1 else day(endrental) end) as monthdays eomonth(monthend, 1) as monthend from cte where eomonth(monthend) < endrental ) select * from cte;
Note: This represents the month by the last day of the month rather than putting the year and month in separate columns.
Selecting records between two date range query, Query for Getting records between two years, month or date ranges from a The above query will return us records of all the months between February and� If there was no sale for a month, then there is no data. I'd like my query to return one row of data for each product for each year/month in my date range, regardless of whether there was actually an order. If there was no order, then I can return 0 for that product/year/month. Below is my example query.
From the info posted it looks like:
SELECT name, MIN(startrental) as startrental, MIN(endrental) as endrental, DATEDIFF(day, MIN(startrental), MIN(endrental)) as duration FROM table GROUP BY name
I didn't really understand your "from the table below" - there was no table below that sentence. I assumed your raw data is the source table and the smaller top table is what you're trying to create. I picked this way round because the larger table has inconsistencies in the data that must be human mistake; they wouldn't be algorithmically generated unless the algo was very complex and deliberately constructing them
Generating Rows by Month for Date Ranges in Power Query, Generating Rows by Month for Date Ranges in Power Query time-based data, but they all operate with different financial year start and end dates. to the same place (month start might be easier, but if I'm getting additional� I needed very similar thing, to get month's date range from specific date, which was possible to use in SQL search - that means not only dates must be correct, but time must be from 00:00:00 to 23:59:59 if in 24 hour system, but it just matter of displaying DATETIME format, it will work with 12 hours system too.
Get the last day of every month in a range , To do this, I needed to write a method that iterates over a date range and spits back the last day of each month, then builds a query based on that. I had a few� Definition and Usage. The MONTH() function returns the month part for a given date (a number from 1 to 12). Syntax
Examples of using dates as criteria in Access queries, When entering dates as query criteria, enclose them in pound signs (#) to date functions to extract different parts of a date to help you get just the results you want. Examples that work with a date or range of dates other than the current date Year([SalesDate]) = Year(Now()) And Month([SalesDate]) = Month(Now()). I have a dataframe full of dates and I would like to select all dates where the month==12 and the day==25 and add replace the zero in the xmas column with a 1.. Anyway to do this? the second line of my code errors out.
How To Import All Files In A Folder With Power Query. Step 1: Create a query to get the named range. Firstly create a named range to reference. I have called my named range FilePath and copied in a new folder location. Add a named range by typing in the name FilePath into the name box. Then copy in the folder path into this cell. Go to the
- Might help if you explain the logic of how duration is summed - why are c and d a day short? Why is a two days short? Are you ignoring the duration column and diffing the dates?
- yes i might made a mistake calculating the duration.. but that is not the main thing here... it should be enddate - start date
- I voted this answer up, but you should look at a date dimension table as well. They're wicked useful for many situations including this one.
- @RandySlavey Agree 100% ... a calendar table is well worth the effort