CODEWITHSUNDEEP
sqlsql-server
pnetaly
pnetaly

Reputation: 67

Getting all month from a range based query

help

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?

Upvotes: 1

Views: 56

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 82020

You can use an ad-hoc Tally/Numbers table if you don't have a Calendar Table

Example

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)

Returns

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.

Upvotes: 1

Caius Jard
Caius Jard

Reputation: 74730

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271171

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.

Upvotes: 0

Related Questions