Hi, I am trying to figure out a good way to create a timeline and sum the values from a table based on their start and end dates where some overlap. Any help would be appreciated.
Here is an example and the expected results.
CREATE TABLE [dbo].[SUM_TEST](
[RECORD_ID] [bigint] NOT NULL,
[AMT] [money] NULL,
[STARTDATE] [datetime] NULL,
[ENDDATE] [datetime] NULL
) ON [PRIMARY]
GO
insert into dbo.SUM_TEST
(RECORD_ID,AMT,STARTDATE,ENDDATE)
Values
(1,20,'07/31/2020','08/31/2020'),
(2,10,'08/01/2020','08/15/2020'),
(3,15,'09/01/2020','09/30/2020'),
(4,10,'09/01/2020','09/15/2020')
Select d.StartDate, d.EndDate, sum(s.Amt)
from #SUM_TEST s
join #Dates d
on d.StartDate >= s.StartDate
and d.EndDate <= s.EndDate
group by d.StartDate, d.EndDate
The date ranges are in the table. If we just take the first two records, one is 7/31 - 8/31 and the second is 8/1 - 8/15. The first range has a value of 20 and the second of 10. So when summed together for each day the value on 7/31 is 20, but on 8/1 we now have to include the value of the second record, so the value on 8/1 is 30, etc... until 8/16 when the value goes back to 20 since the record with the 10 value has ended. Hope that makes sense.
i took a stab at this !! ( its not exactly what you want .. I have to fix it ) ...
hope this helps
; with cte as
(
select startdate from SUM_TEST
union
select ENDDATE from SUM_TEST
)
, cte_fin as
(
select
startdate
, dateadd(dd,-1,lead(startdate) over (order by startdate)) as nx_Startdate
from
cte
)
select
*
from
cte_fin
where
nx_Startdate is not null
order by
startdate
It is close but there are a few issues:
row 2 should end on the 15th
row 3 should start on the 16th and end on the 31st
row 4 should not exist
row 5 should end on the 15th
row 6 should start on the 16th
I am looking at this as well, but you have put me on a promising path to getting this data in the format I require.
Sweet! I think I have it. I just added a day to the end date in the first CTE. This solved my enddate issue. Then a simple cross apply appears to have done the trick, Thanks for you help!
; with cte as
(
select startdate from GAK_TEST
union
select dateadd(dd,1,enddate) from GAK_TEST
)
--select * from cte
, cte_fin as
(
select
startdate
, dateadd(dd,-1,lead(startdate) over (order by startdate)) as EndDate
from
cte
)
--select
-- *
--from
-- cte_fin
--where
-- EndDate is not null
--order by
-- startdate
select SUM(AMT), c.startdate, c.enddate
from cte_fin c
cross apply GAK_TEST g
where c.EndDate is not null
and c.STARTDATE between g.STARTDATE and g.ENDDATE
group by c.startdate, c.enddate
