I am trying to figure out how I would best do the following in SQL Server.

Say I have a table Sales:

My goal is I want to be able to track every day, from the start of the year until today, a sum of all there sales up to today.

So it should show every day from 1/1/2022 to 4/1/2022. As an example the value of sales every day from 1/3 to 1/31, it should show 1 for Peter, 2 for Ben, 3 for Sally, but by 2/3, it should show 4 for Ben, 2 for Peter, and 5 for Sally. The row for today's date should show 3 for Peter, 6 for Ben, 7 for Sally.

Any help or guidance with this is greatly appreciated!

Thank you!

CodePudding user response：

Given this sample data (with unambiguous date formats please!):

CREATE TABLE dbo.SalesData(Name varchar(32), Sales int, Date date);

INSERT dbo.SalesData(Name, Sales, Date) VALUES
(N'Peter',  1, '20220101'),
(N'Ben',    2, '20220102'),
(N'Sally',  3, '20220101'),
(N'Ben',    2, '20220202'),
(N'Peter',  1, '20220201'),
(N'Sally',  2, '20220202'),
(N'Peter',  1, '20220301'),
(N'Ben',    2, '20220302'),
(N'Sally',  2, '20220302');

We can use a recursive CTE to get all the dates from the beginning of the year to now, cross join that the individual names from the sales table to get a row per salesperson per date, then use a window clause to get a standard running total to date for each person:

DECLARE @today date = GETDATE();
DECLARE @yearstart date = DATEFROMPARTS(YEAR(@today), 1, 1);

;WITH Days([day]) AS 
(
  SELECT @yearstart UNION ALL 
  SELECT DATEADD(DAY, 1, [day]) FROM Days
    WHERE [day] < @today
),
AllDatesPerPerson AS
(
  SELECT Days.[day], sd.Name FROM Days 
  CROSS JOIN (SELECT DISTINCT Name FROM dbo.SalesData
      WHERE Date >= @yearstart) AS sd
)
SELECT dpp.Name, dpp.[day], COALESCE(SUM(sd.Sales) OVER
  (PARTITION BY dpp.Name ORDER BY dpp.[day] ROWS UNBOUNDED PRECEDING),0)
FROM AllDatesPerPerson AS dpp
LEFT OUTER JOIN dbo.SalesData AS sd
ON dpp.Name = sd.Name AND sd.Date = dpp.[day]
ORDER BY dpp.Name, dpp.[day]
OPTION (MAXRECURSION 366); -- covers up to Dec 31 even on leap years

• Example

  This might make a good interview question, because it's deceptively simple sounding, but a little tricky to code. The source query picks up the list of dates, the list of sellers, and the SaleCount, or zero if no sales were recorded. We need to us a window function ( SUM ... OVER ) in order to get a running total, and not just a total for that day.