Hope I am not so late.
Lemme know if its not working with other sample data. I am sure it can be fix if its giving wrong output for other sample data.
I have normal calendar table which store only FOM (first day of each month)
CREATE TABLE tblcalender (
DATEVal DATETIME
PRIMARY KEY (DATEVal)
);
INSERT INTO tblcalender WITH (TABLOCK)
SELECT DATEADD(MONTH, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), '18991201')
FROM master..spt_values;
Sample data,
CREATE TABLE #TMP(StartDate DATE, EndDate DATE,Type1 CHAR(1))
INSERT INTO #TMP VALUES
('2017-09-28','2017-10-02','L')
,('2017-10-03','2017-10-10','R')
,('2016-11-10','2016-11-11','L')
,('2017-08-17','2017-12-25','R')
--SELECT * FROM #TMP
For Sql server 2008 and below,
;WITH CTE
AS (
SELECT DATEVal
,StartDate
,EndDate
,Type1
,CASE
WHEN year(StartDate) = year(EndDate)
AND month(StartDate) = month(EndDate)
THEN '1'
WHEN month(DATEVal) = month(StartDate)
AND year(DATEVal) = year(ca.StartDate)
THEN 'S'
WHEN DATEVal > StartDate
AND DATEVal < dateadd(month, - 1, DATEADD(month, datediff(month, 0, ca.EndDate) + 1, 0))
THEN 'W'
ELSE 'E'
END flag
FROM tblcalender
CROSS APPLY (
SELECT StartDate
,EndDate
,Type1
FROM #tmp
WHERE DATEVal >= dateadd(month, - 1, DATEADD(month, datediff(month, 0, StartDate) + 1, 0))
AND DATEVal <= dateadd(day, - 1, DATEADD(month, datediff(month, 0, EndDate) + 1, 0))
) ca
)
,CTE1
AS (
SELECT DATEVal
,CASE
WHEN flag = '1'
THEN datediff(day, StartDate, EndDate)
WHEN flag = 'S'
THEN datediff(day, StartDate, dateadd(day, - 1, DATEADD(month, datediff(month, 0, StartDate) + 1, 0))) + 1
WHEN flag = 'E'
THEN datediff(day, dateadd(month, - 1, DATEADD(month, datediff(month, 0, endDate) + 1, 0)), endDate)
ELSE day(dateadd(day, - 1, DATEADD(month, datediff(month, 0, DATEVal) + 1, 0))) --W
END [Days]
,Type1
,StartDate
,EndDate
,Flag
FROM CTE
)
--select * from CTE1
SELECT DATEVal
,Type1
,SUM([Days]) [Days]
FROM CTE1
GROUP BY DATEVal
,Type1
DROP TABLE #TMP
For Sql server 2012 and above,just minor change.using EOMonth and FORMAT to make the code shorter.
;with CTE as
(
select DATEVal,StartDate,EndDate,Type1
,case when FORMAT(StartDate,'MMyyyy')=FORMAT(EndDate,'MMyyyy')
THEN '1'
when FORMAT(StartDate,'MMyyyy')=FORMAT(DATEVal,'MMyyyy')
then 'S'
when DATEVal>StartDate and DATEVal< dateadd(day,1,EOMONTH(EndDate,-1))--FOM
THEN 'W'
else 'E' END flag
from tblcalender
cross APPLY(
select StartDate,EndDate,Type1 from #tmp
where DATEVal>= dateadd(day,1,EOMONTH(StartDate,-1))
and
DATEVal<=EOMONTH(EndDate)
)ca
)
,CTE1 as
(
select DATEVal
, case when flag='1'
THEN datediff(day,StartDate,EndDate)
when flag='S'
THEN datediff(day,StartDate,EOMONTH(StartDate))+1
when flag='E'
THEN datediff(day,dateadd(day,1,EOMONTH(endDate,-1)),endDate)
else day(EOMONTH(DATEVal))--W
end [Days]
,Type1,StartDate,EndDate,Flag
from CTE
)
--select * from CTE1
SELECT DATEVal,Type1
,SUM([Days])[Days]
from CTE1
group by DATEVal,Type1
DROP TABLE #TMP
