I have a table that is updated every hour (RPT.SummaryAggregates). I need to create a stored procedure that fills a table (RPT.WeeklyAggregates) with a snapshot of the total records in (RPT.SummaryAggregates) once a week (this SP will be setup with a SQL Agent Job). I need the Week column of (RPT.WeeklyAggregates) to automatically increase by 1 incrementally every time the stored procedure is run. There is one week of data in the table currently.
The stored procedure currently looks like this:
INSERT INTO RPT.WeeklyAggregates SELECT 1 AS Week, SUM(BX), SUM(BK), SUM(MN), SUM(QN), SUM(SI), SUM(CF), (SUM(BX)+SUM(BK)+SUM(MN)+SUM(QN)+SUM(SI)+SUM(CF)) as Total FROM RPT.SummaryAggregates END;
The table columns are Week, BX, BK, MN, QN, SI, CF, Total.
Advertisement
Answer
If I followed you correctly, you want:
INSERT INTO RPT.WeeklyAggregates(Week, BX, BK, MN, QN, SI, CF, Total) SELECT (SELECT COALESCE(MAX(Week), 0) + 1 FROM WeeklyAggregates), SUM(BX), SUM(BK), SUM(MN), SUM(QN), SUM(SI), SUM(CF), SUM(BX)+SUM(BK)+SUM(MN)+SUM(QN)+SUM(SI)+SUM(CF) FROM RPT.SummaryAggregates
The subquery retrieves the maximum Week
value from the target table, and increments it; COALESCE()
handles the case when the table is initially empty (in which case 1
is inserted).