Count days inside date period, count the first 7 days separately

TomMRiddle 0
Viewed 81

I use SQL Server 2017 and have a table with date periods with a "From date" and a "To date" columns

After I have selected all date periods that overlap with a chosen date period I want to count only the number of days that are inside the chosen date period. I also want to count the first 7 days separate from the remaining days (only the days that fall inside the chosen period).

example:

Chosen period 2022-01-01 to 2022-01-31

SELECT * FROM Table
WHERE [From date] <= '2022-01-01'
AND [To Date] >= '2022-01-31'
ID From date To date Comment
1 2021-12-26 2022-02-09 cut off from and to date
2 2022-01-21 2022-02-09 cut off to date
3 2022-01-10 2022-01-14 no cut off
4 2021-01-15 2022-02-11 cut off from date
5 2020-05-10 2020-10-20 outside chosen period

Wanted results:

ID from date To date first 7 days remaining days Comment
1 2022-01-01 2022-01-31 1 30 cut off from and to date
2 2022-01-21 2022-01-31 7 4 cut off to date
3 2022-01-10 2022-01-14 5 0 no cut off
4 2022-01-01 2022-01-31 0 31 cut off from date

Only the ID, [First 7 days] and [Remaining days] columns in the results table are important and the other columns are only shown in the above example for the purpose of clarity.

I have started writing a series of "case when then" using datediff but it feels overly complicated and I hope there is a better way to do it.

sql-server-2017 tsql sql-server sql
1 Answers
select  from_date
       ,to_date
       
       ,datediff(day, from_date, to_date)+1-case when datediff(day,fd_plus_7, to_date) <= 0 then 0
             when datediff(day,fd_plus_7, to_date) >= day_max then day_max
             else datediff(day,fd_plus_7, to_date)+1 end as first_7_days
       
       ,case when datediff(day,fd_plus_7, to_date) <= 0 then 0
             when datediff(day,fd_plus_7, to_date) >= day_max then day_max
             else datediff(day,fd_plus_7, to_date)+1 end as remaining_days  

       ,Comment  
from         
        (    
        select *
               ,datediff(day, min(from_date) over(order by from_date) ,max(to_date) over(order by to_date desc)) + 1 as day_max  
        from   (
                select *
                       ,case when [From date] < '2022-01-01' then '2022-01-01' else [From date]  end as from_date
                       ,case when [To date]   > '2022-01-31' then '2022-01-31' else [To date]    end as to_date  
                       ,dateadd(day, 7, [From date]) as fd_plus_7
                from t
                where [From date] <= '2022-01-31'
                  and [To date]   >= '2022-01-01'
              ) t ) t
order by ID
from_date to_date first_7_days remaining_days Comment
2022-01-01 00:00:00.000 2022-01-31 00:00:00.000 0 31 cut off from and to date
2022-01-21 00:00:00.000 2022-01-31 00:00:00.000 7 4 cut off to date
2022-01-10 00:00:00.000 2022-01-14 00:00:00.000 5 0 no cut off
2022-01-01 00:00:00.000 2022-01-31 00:00:00.000 0 31 cut off from date

Fiddle

DannySlor0
Related