How to calculate value for each calendar day?

Question

Data Sample:

IF OBJECT_ID('tempdb..#Data1') IS NOT NULL DROP TABLE #Data1
GO
create table #Data1 (ControlNo int, ClaimID int, DateCreated date, loss money)
insert into #Data1 values (51294, 54986,'2019-07-24', 3000),
                          (51294, 54986,'2019-07-25', 2963.41),
                          (51294, 54986,'2019-07-26', 2963.41),
                          (51294, 54986,'2019-08-19', 2963.41),
                          (51294, 54986,'2019-08-22', 2963.41),
                          (51294, 55027,'2019-07-25', 929),
                          (51294, 55027,'2019-07-26', 929),
                          (51294, 55027,'2019-08-19', 929),
                          (51294, 55027,'2019-08-22', 929)
select * from #Data1

Calendar Table:

DECLARE @MinDate DATE = CAST(DATEADD(YY, -1, getdate()) as DATE),  -- a year from today
        @MaxDate DATE =  CAST(GETDATE() as DATE);
;WITH cte_Calendar AS (
SELECT  TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
        Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)

FROM    sys.all_objects a
        CROSS JOIN sys.all_objects b
) select *  into #Calendar from  cte_Calendar
select  * from #Calendar

Each ControlNo can have more than one ClaimID, unique DateCreated and Loss amount, that tells us what loss we had at that particular date (point of time)

I also have a calendar table that have Date field 365 days back from today's date. For example if today is 08/24/2019, then calendar table would start from 08/24/2018 till 08/24/2019

How can I write a query that would return each day starting from first DateCreated (2019-07-24) till last DateCreated (2019-08-22) and Loss need to be summed up by ControlNo and DateCreated.

Or maybe I should say for each calendar date I need the sum of loss for each ClaimID and DateCreated

So calendar date 2019-07-24 we only have $3,000 For date '2019-07-25' we have two claims: $2,963.41 for ClaimID 54986 and $929 for ClaimID 55027 which gives us total for that date and ControlNo $3,892.41 And so on...

So the outcome should return 3 columns: Calendar Date, ControlNo, Loss

Answer 1

Not sure about its efficiency but you can test it:

with 
  cal as (
    select d.controlno, c.date
    from #Calendar c cross join (
      select distinct controlno from #Data1 
    ) d
  ),
  cte as (
    select c.controlno, c.date, sum(d.loss) loss
    from cal c left join #Data1 d
    on d.controlno = c.controlno and d.datecreated = c.date
    group by c.controlno, c.date
  )
select c.controlno, c.date, 
  coalesce(c.loss,
    (
      select cc.loss from cte cc 
      where cc.controlno = c.controlno and cc.date = (
        select max(date) from cte
        where controlno = c.controlno and date < c.date and loss is not null
      )
    )
  )
from cte c 

See the demo.