I need to sum values in intersect of range dates.

sample of source data

expected result

I use something code like this, but it is to simple, and results are not good

  select
  person
  ,item
  ,Min([start_date]) as [start_date]
  ,Max([end_date]) as [end_date]
  ,Sum([value]) as [value]
  FROM table
  Group by   person, item

I tried to use LAG() function, but i'm lost

CodePudding user response：

I have no access to Synapse , but assuming it's compatibile with SQL server...

db<>fiddle

Internal query build data ranges, creating additional dates for overlapping periods if needed. Main query just sum values.

select person, item, range_from, range_to, 
       (select sum(value) from test 
            where person = r.person 
              and item = r.item 
              and range_from between start_date and end_date) value
from ( 
  select 
     be, 
     person, 
     item, 
     date range_from, 
     lead(date,1) over(partition by person, item order by date,be) range_to 
  from (      
     select 1 be, person, item, start_date date from test
     union 
     select 2, person, item, end_date from test
     union  
     select 2, person, item, dateadd(day,-1,start_date) from test a
       where exists (select * from test where a.person = person and a.item = item and a.start_date > start_date and a.start_date < end_date)
     union 
     select 1, person, item, dateadd(day,1,end_date) from test b
       where exists (select * from test where b.person = person and b.item = item and b.end_date > start_date and b.end_date < end_date)
  ) k 
) r where r.be = 1 order by r.person, r.item, r.range_from 

column be contains:

• 1 - for period start
• 2 - for period end