This method should work for multiple users with different date ranges.
If the user has same dates with different prices it should sum them up.
Not sure how efficient it is though - should work on SQL server 2012+
declare @tableA table (ID int, UserID int, StartDate datetime, EndDate DateTime, PricePW money );
insert into @tableA
select 157, 3, '20190102', '20190105', 56
union select 189, 3, '20190106', '20190109', 77
union select 200, 3, '20190110', '20190119', 35
union select 500, 10, '20190102', '20190105', 56
union select 502, 10, '20190110', '20190119', 35
;
with UserStartEnd as(
select
UserID,
min(StartDate) StartDt,
max(EndDate) EndDt
from
@tableA
group by
UserID
), UserDateRange as (
select
UserID,
StartDt TheDate,
EndDt EndDate
from UserStartEnd
union all
select
UserID,
dateadd (dd, 1, TheDate) TheDate,
EndDate
from UserDateRange
where dateadd (dd, 1, TheDate) <= EndDate
), UserDateRangePricePerDay as (
select
udr.UserID,
udr.TheDate,
sum (a.PricePW/7) PricePerDay
from UserDateRange udr
inner join @tableA a
on
udr.TheDate between a.StartDate and a.EndDate
and udr.UserID = a.UserID
group by
udr.UserID,
udr.TheDate
)
select
UserID,
TheDate,
PricePerDay,
sum(PricePerDay)
over(
partition by UserID
order by UserID, TheDate
rows between unbounded preceding and current row
) Accumulative
from UserDateRangePricePerDay
order by
UserID,
TheDate
option (maxrecursion 1000)
;
Accumulative can be worked out in multiple ways
I used this method https://stackoverflow.com/a/13331102