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]
insert into dbo.SUM_TEST
This is the result I am looking for:
07/31/2020 07/31/2020 20
08/01/2020 08/15/2020 30
08/16/2020 08/31/2020 20
09/01/2020 09/15/2020 25
09/16/2020 09/30/2020 15
I don't know where you are getting the date ranges from, so I created a temp table to store them
Create table #Dates (StartDate date, EndDate Date)
insert into #Dates Values
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
select ENDDATE from SUM_TEST
, cte_fin as
, dateadd(dd,-1,lead(startdate) over (order by startdate)) as nx_Startdate
nx_Startdate is not null
That is a big help. I can now use this pull the values where the dates are include in the range. Let me see what I can do as well. Thanks!
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
select dateadd(dd,1,enddate) from GAK_TEST
--select * from cte
, cte_fin as
, dateadd(dd,-1,lead(startdate) over (order by startdate)) as EndDate
-- EndDate is not null
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
|(No column name)