sql how many days one period contains in other given period-CodePudding

I need to calculate how many days one set period contains in another set period. I have table with

create table #test
(
,project nvarchar(10),
startProjectDate date,
endProjectDate date)

insert into #test values
('EE43213','2021-12-31','2022-01-06') ,
('EE0211213','2022-01-09','2022-03-14'), 
('EE53134','2022-02-18','2022-02-22')

I have parameters with dates (user input in the future)

DECLARE @startDate DATE = N'2021-12-16' 
DECLARE @endDate DATE = N'2022-03-02'

For every project I need to calculate, how many days of their running time will be set on user chosen period and then * this count on some koeff.

I have case when in mind, if the whole project was in parameter-set period, I just find datediff between two project dates and * it.

case when (startProjectDate BETWEEN @startDate and @endDate) 
and (endProjectDate BETWEEN @startDate and @endDate)
 then DATEDIFF(day, startProjectDate , endProjectDate)   1 * coeff else ...

But how to find an amount of days if they only partially set on this period?

CodePudding user response：

Seems you just need a DATEDIFF and some CASE expressions here:

DECLARE @StartDate DATE = N'20211216',
        @EndDate DATE = N'20220302';

SELECT project,
       DATEDIFF(DAY, CASE WHEN startProjectDate < @StartDate THEN @StartDate ELSE startProjectDate END, CASE WHEN endProjectDate > @EndDate THEN @EndDate ELSE endProjectDate END) AS DaysInRange,
       DATEDIFF(DAY,startProjectDate, endProjectDate) AS DaysInProject
FROM #test
WHERE startProjectDate <= @EndDate
  AND endProjectDate >= @StartDate;