Quick questions
😎
1) By activity period of 3 months, do you mean three full calendar months, i.e. 1st. January to 1st. April, including both start and end dates?
2) Does the duration of each suspension include both the start and end dates, i.e. 1st. February to 10th. February would be a duration of 10 days?
3) If the suspension in 2) is applied to the activity period in 1), would the actual end date be 11th April?
This query will aggregate the duration of the suspensions and extend the activity period's end date accordingly.
;WITH BASE_DATA AS
(
SELECT
DMA.partyid
,DMA.roleid
,DMA.memberagreementid
,DMA.editablestartdate AS INITIAL_START_DATE
,CASE
WHEN DMA.editablestartdate < convert(datetime,'09/04/2007',101) THEN convert(datetime,'09/04/2007',101)
ELSE DMA.editablestartdate
END AS ACTUAL_START_DATE
FROM dbo.memberagreement DMA
)
,INITIAL_AGREEMENT_START_END AS
(
SELECT
BD.partyid
,BD.roleid
,BD.memberagreementid
,BD.INITIAL_START_DATE
,BD.ACTUAL_START_DATE
,DATEADD(MONTH,3,BD.ACTUAL_START_DATE) AS INITIAL_END_DATE
FROM BASE_DATA BD
)
,SUSPENSION_AND_DURATION AS
(
SELECT
SUS.suspensionid
,SUS.targetentityid
,SUS.begintime
,SUS.endtime
,DATEDIFF(DAY,SUS.begintime,DATEADD(DAY,1,SUS.endtime)) AS SUSPENSION_DURATION
,SUM(DATEDIFF(DAY,SUS.begintime,DATEADD(DAY,1,SUS.endtime))) OVER
(
PARTITION BY SUS.targetentityid
ORDER BY SUS.begintime ASC
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS ACCUM_SUSP_DURATION
,COUNT(SUS.targetentityid) OVER
(
PARTITION BY SUS.targetentityid
ORDER BY SUS.begintime ASC
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS ACCUM_COUNT_SUSP
FROM dbo.suspension SUS
)
SELECT
IASE.partyid
,IASE.roleid
,IASE.memberagreementid
,IASE.INITIAL_START_DATE
,IASE.ACTUAL_START_DATE
,IASE.INITIAL_END_DATE
,SAD.suspensionid
,SAD.begintime
,SAD.endtime
,SAD.SUSPENSION_DURATION
,SAD.ACCUM_SUSP_DURATION
,SAD.ACCUM_COUNT_SUSP
,DATEADD(DAY,SAD.ACCUM_SUSP_DURATION,IASE.INITIAL_END_DATE) AS NEW_END_DATE
FROM INITIAL_AGREEMENT_START_END IASE
LEFT OUTER JOIN SUSPENSION_AND_DURATION SAD
ON IASE.memberagreementid = SAD.targetentityid
WHERE SAD.targetentityid = 12345;
Output using the sample data
partyid roleid memberagreementid INITIAL_START_DATE ACTUAL_START_DATE INITIAL_END_DATE suspensionid begintime endtime SUSPENSION_DURATION ACCUM_SUSP_DURATION ACCUM_COUNT_SUSP NEW_END_DATE
----------- ----------- ----------------- ----------------------- ----------------------- ----------------------- ------------ ----------------------- ----------------------- ------------------- ------------------- ---------------- -----------------------
555 777 12345 2016-01-22 00:00:00.000 2016-01-22 00:00:00.000 2016-04-22 00:00:00.000 1 2016-03-12 00:00:00.000 2016-03-30 00:00:00.000 19 19 1 2016-05-11 00:00:00.000
555 777 12345 2016-01-22 00:00:00.000 2016-01-22 00:00:00.000 2016-04-22 00:00:00.000 2 2016-05-10 00:00:00.000 2016-05-30 00:00:00.000 21 40 2 2016-06-01 00:00:00.000
555 777 12345 2016-01-22 00:00:00.000 2016-01-22 00:00:00.000 2016-04-22 00:00:00.000 3 2016-07-10 00:00:00.000 2016-07-20 00:00:00.000 11 51 3 2016-06-12 00:00:00.000
555 777 12345 2016-01-22 00:00:00.000 2016-01-22 00:00:00.000 2016-04-22 00:00:00.000 4 2016-09-10 00:00:00.000 2016-09-30 00:00:00.000 21 72 4 2016-07-03 00:00:00.000
555 777 12345 2016-01-22 00:00:00.000 2016-01-22 00:00:00.000 2016-04-22 00:00:00.000 5 2016-11-10 00:00:00.000 2016-11-30 00:00:00.000 21 93 5 2016-07-24 00:00:00.000