SQLTeam.com | Weblogs | Forums

Sum values in a timeline with overlapping dates

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')

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
('07/31/2020','07/31/2020'),
('08/01/2020','08/15/2020'),
('08/16/2020','08/31/2020'),
('09/01/2020','09/15/2020'),
('09/16/2020','09/30/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.

hi

i took a stab at this !! ( its not exactly what you want .. I have to fix it ) ...

hope this helps :slight_smile:

; 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

image

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
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

Results:

(No column name) startdate enddate
20.00 2020-07-31 00:00:00.000 2020-07-31 00:00:00.000
30.00 2020-08-01 00:00:00.000 2020-08-15 00:00:00.000
20.00 2020-08-16 00:00:00.000 2020-08-31 00:00:00.000
25.00 2020-09-01 00:00:00.000 2020-09-15 00:00:00.000
15.00 2020-09-16 00:00:00.000 2020-09-30 00:00:00.000

Njoy ..:+1::+1: