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'

Wanted results:

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.

CodePudding user response：

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

Fiddle