以下是組織中成員的醫療保險詳細資訊示例。我們必須找出不。每個成員在特定年份投保的天數。 以下資料的日期格式為 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')
輸出應該類似于
memberid no_of_days_insured_in_2018
m1 309
.
.
.
.
m7 363
uj5u.com熱心網友回復：
如果這是針對特定年份的，正如您所暗示的，那么您可以DATEDIFF使用一些CASE運算式：
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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/451043.html