Search code examples
sqlsql-serverwhile-loopsumgrouping

SQL, How to correctly sum in while loop and write it to table by weeks number

I have data that I need to sum properly, but can't quite figure out how:

create table #Test
(
Job [nvarchar](20) 
,[Order] [nvarchar](20) 
,WeekNumber int
,startday date
,endday date
,WeeklyHrs decimal(17, 5)
,WhatTheClientWants decimal(17, 5)
,PreviousHrs decimal(17, 5)
)

INSERT INTO #Test
VALUES 
(1,1,1,'2022-05-09','2022-05-15',172,NULL,0),
(1,1,2,'2022-05-16','2022-05-22',9,NULL,0),
(1,2,1,'2022-05-09','2022-05-15',9,NULL,0),
(1,2,999,NULL,NULL,32.5,NULL,32.5),
(1,5,1,'2022-05-09','2022-05-15',162,NULL,0),
(1,5,2,'2022-05-16','2022-05-22',20,NULL,0),
(1,5,3,'2022-05-16','2022-05-22',0,NULL,0),
(1,6,2,'2022-05-16','2022-05-22',1,NULL,0),
(1,3,999,NULL,NULL,32.5,NULL,54)

So client has two parameters, @startdate, @enddate, and between those parameters I form weeks. Like 09.05.2022 - 15.05.2022 - week 1, 16.05.2022, 22.05.2022 - week 2. Depends on that, I had my grouping later, but now, new task appeared. I need so that Week Two had its hours plus hours form week one, week three (there could be a lot of weeks) has Hours from week one and two and three and so on... I tried Update with while but it didn't quiet work, then I tried Window Function, but I am doing something wrong:

declare @MaxWeek int = (select top 1 max(WeekNumber) over (PARTITION BY job) from #Test where WeekNumber <> 999)
DECLARE @i int = 0

WHILE (@i <= @MaxWeek)
BEGIN
UPDATE #Test
set WhatTheClientWants = (select sum(FullHrs) from #Test group by Job, WeekNumber)
set @i= @i+1
END

I imagine that there should be may be some kind of case that if week one, its just hours, but if not, some kind of sum

WHILE (@i <= @MaxWeek)
BEGIN
UPDATE #Test
set WhatTheClientWants = FullHrs from #Test where WeekNumber = @i 
set @i= @i+1
END

but what case I can't figure out...

What it should looks like: 1

Would appreciate any help!

Solution

  • Consider using a window function to do this:

    SELECT testtable.*, 
    SUM(WeeklyHours) OVER (PARTITION BY [Order] ORDER BY startday ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS WhatTheClientWants
FROM #test as testtable

    That's taking each group of records that share the same "partition" (or [Order] column value), sorting them by startday and then summing them all together up to the current row. A cumulative sum inside of a partition.

    While it's always tempting to think "WHILE LOOP!" as a solution, in a database that's almost never needed. In over a decade in this field, I've only had to resort to a while loop once for a very procedural solution that had no set based alternative.

    Just noticed the nulls for NULL startday in the new column. Clobbering this with a UNION ALL should suffice:

    SELECT testtable.*, 
    SUM(WeeklyHrs) OVER (PARTITION BY [Order] ORDER BY startday ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS WhatTheClientWants
FROM #test as testtable
WHERE StartDay IS NOT NULL 
UNION ALL
SELECT testtable.*, NULL
FROM #test as testtable
WHERE StartDay IS NULL