(

SubmitterID int

, SubmitterName varchar(50)

, DateReceived date

, ClaimCount int

);

dbo.FalloutClaimReport

(

SubmitterID

, SubmitterName

, DateReceived

, ClaimCount

)

VALUES

(1, 'A', '2019-07-01', 1)

, (1, 'A', '2019-07-02', 2)

, (1, 'A', '2019-07-03', 3)

, (1, 'A', '2019-07-04', 4)

-- Nothing happened on the fifth for "reasons"

, (1, 'A', '2019-07-06', 6)

, (1, 'A', '2019-07-07', 7)

, (1, 'A', '2019-07-08', 8)

, (1, 'A', '2019-07-09', 9)

, (1, 'A', '2019-07-10', 10)

, (1, 'A', '2019-07-11', 11)

FCR.SubmitterID

, FCR.SubmitterName

, CONVERT(varchar(15), ED.DateReceived, 101) AS DateReceived

, SUM(FCR.ClaimCount) AS TotalCount

FROM

(

-- This logic builds out a list of all the dates that must exist on the report

-- I used the logic for TheLastSundayOfTheFullWeek

SELECT

DATEADD(DAY, D.DayOffset, DATEADD(WEEK, -1, CAST(DATEADD(WEEK, DATEDIFF(WEEK, 0, CAST(GETDATE() AS date)), -1) AS date))) AS DateReceived

FROM

(

-- Generate a series of 7 numbers from 0 t 6

SELECT TOP 7

-1 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn

FROM

sys.all_columns AS AC

) D(DayOffset)

) AS ED

LEFT OUTER JOIN

dbo.FalloutClaimReport AS FCR

ON FCR.DateReceived = ED.DateReceived

GROUP BY

CONVERT(varchar(15), ED.DateReceived, 101)

, FCR.SubmitterID

, FCR.SubmitterName;