How to calculate no. of days in an year in particular time period in sql server-CodePudding

Below is a sample of medical insurance details of members in an organization. And we have to find out no. of days each member is insured in particular year. date format for below data is mm-dd-yyyy

SELECT \* FROM MED_INSURANCE

INSERT INTO MED_INSURANCE VALUES

('M1','1-1-2017','11-20-2017')
,('M1','12-31-2017','02-01-2018')
,('M1','02-15-2018','04-30-2018')
,('M1','06-10-2018','12-31-2018')
,('M2','1-1-2017 ','11-20-2017')
,('M2','12-31-2017','02-01-2018')
,('M3','02-15-2018','04-30-2018')
,('M3','06-10-2018','12-31-2018')
,('M4','1-1-2017','11-20-2017')
,('M4','12-31-2017','02-01-2018')
,('M5','02-15-2018','04-30-2018')
,('M5','06-10-2018','12-31-2018')
,('M6','01-01-2017','12-31-2019')
,('M7','12-31-2017','12-30-2018')
,('M8','1-1-2020','12-31-2020')
,('M9','06-30-2018','01-31-2020')

Output should be something like

memberid        no_of_days_insured_in_2018
m1                      309
.
.
.
.
m7                      363

CodePudding user response：

If this is for a specific year, as you imply, then you can DATEDIFF with some CASE expressions:

SELECT YD.ID,
       SUM(DATEDIFF(DAY,
                    CASE WHEN YD.StartDate < '20180101' THEN '20180101'
                         WHEN YD.StartDate >= '20190101' THEN NULL
                         ELSE YD.StartDate
                    END,
                    CASE WHEN YD.EndDate < '20180101' THEN NULL
                         WHEN YD.EndDate >= '20190101' THEN '20181231'
                         ELSE YD.EndDate
                    END)   1) AS DaysIn2018
FROM (VALUES ('M1', '1-1-2017', '11-20-2017'),
             ('M1', '12-31-2017', '02-01-2018'),
             ('M1', '02-15-2018', '04-30-2018'),
             ('M1', '06-10-2018', '12-31-2018'),
             ('M2', '1-1-2017 ', '11-20-2017'),
             ('M2', '12-31-2017', '02-01-2018'),
             ('M3', '02-15-2018', '04-30-2018'),
             ('M3', '06-10-2018', '12-31-2018'),
             ('M4', '1-1-2017', '11-20-2017'),
             ('M4', '12-31-2017', '02-01-2018'),
             ('M5', '02-15-2018', '04-30-2018'),
             ('M5', '06-10-2018', '12-31-2018'),
             ('M6', '01-01-2017', '12-31-2019'),
             ('M7', '12-31-2017', '12-30-2018'),
             ('M8', '1-1-2020', '12-31-2020'),
             ('M9', '06-30-2018', '01-31-2020')) V (ID, StartDate, EndDate)
     CROSS APPLY(VALUES(V.ID, CONVERT(date,StartDate,101), CONVERT(date,V.EndDate,101)))YD(ID, StartDate, EndDate)
GROUP BY YD.ID;