How do I get the month number with the maximum number of days from the date range?

user_11358642 0
Viewed 112

I have a table with 10 million rows, where there are two columns that contain the start date and the end date of the range. For example, 2019-09-25 and 2019-10-20. I want to extract the month number with the maximum number of days, in this example it will be 10. In addition to dates that are separated by one month, there are also such examples: 2019-07-01 and 2019-07-29 (within one month), as well as 2019-07-01 and 2019-09-05 (more than one month). How can I implement this?

sql-server sql
3 Answers

Seems like you could do something like this:

SELECT CASE WHEN DATEDIFF(DAY, DATEFROMPARTS(YEAR(EndDate),MONTH(EndDate),1),EndDate) >= DATEDIFF(DAY, StartDate, EOMONTH(StartDate)) THEN DATEPART(MONTH,EndDate)
            ELSE DATEPART(MONTH,StartDate)
       END
FROM (VALUES('20190925','20191020'))V(StartDate,EndDate);
user_20299830

You could try something like this

with
l0(n) as (
    select 1 n 
    from (values (1),(1),(1),(1),(1),(1),(1),(1)) as v(n))
select top(1) with ties
       vTable.*, calc.dt month_with_most_days
from (values ('20190925','20191020'),
             ('20190925','20191120')) vTable(startdate, enddate)
      cross apply (values (datediff(month, vTable.startdate, vTable.enddate))) diff(mo_count)
      cross apply (select top (diff.mo_count+1)
                          row_number() over (order by (select null)) n
                   from l0 l1, l0 l2, l0 l3, l0 l4) tally /* 8^4 months possible */
      cross apply (values (cast(case when tally.n=1 then startdate
                                     when tally.n=diff.mo_count+1 then enddate
                                    else eomonth(dateadd(month, tally.n-1, startdate)) end as date))) calc(dt)
order by row_number() over (partition by startdate, enddate
                            order by day(calc.dt) desc);

startdate   enddate     month_with_most_days
20190925    20191020    2019-09-25
20190925    20191120    2019-10-31
user_67487580

Does the following fit your requirements?

You can build a table of days-in-month (this would be permanent ideally)

and then join to it using the month numbers of your min and max dates.

declare @start date='20190925', @end date='20191020';
--declare @start date='20190701', @end date='20190729';
--declare @start date='20190701', @end date='20190905';
with dim as (
    select m,DAY(DATEADD(DD,-1,DATEADD(mm, DATEDIFF(mm, 0, DateFromParts(Year(GetDate()),m,1)  )+1, 0)))d
    from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12))m(m)
)
select top(1) with ties m
from dim
where m between Month(@start) and Month(@end)
order by d desc

You don't state how you determin the most days where there are several months with the same number of months, so with ties includes all qualifying months.

Edit

So I don't know if there is a requirement to span years - the sample data suggests not - however with a permanent list of dates and corresponding days in month values (this is often part of a calendar table) a slight tweak will accomodate it.

with dim as (
    select Year(@start)*100 + m m, Day(DATEADD(DD,-1,DATEADD(mm, DATEDIFF(mm, 0, DateFromParts(Year(@start),m,1)  )+1, 0)))d
    from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12))m(m)
    union all
    select Year(@end)*100 + m m, Day(DATEADD(DD,-1,DATEADD(mm, DATEDIFF(mm, 0, DateFromParts(Year(@end),m,1)  )+1, 0)))d
    from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12))m(m)
)
select top(1) with ties m
from dim
where m between Year(@start)*100 + Month(@start) and Year(@end)*100 + Month(@end) 
order by d desc
user_153326500
Related