How do I add 1 to a column value on an insert into statement in a stored procedure?

Question

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.

Answer 1

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).