-- Synthetic Theory fixture 2. No customer data.
CREATE PROCEDURE report.usp_month_end
AS
BEGIN
    DECLARE @period nvarchar(6);
    DECLARE @sql nvarchar(200);

    INSERT INTO report.month_end (position_id, gross_amount, as_at_month)
    SELECT
        position_id,
        position_amount AS gross_amount,
        as_at_month
    FROM stg.positions;

    SET @period = FORMAT(GETDATE(), 'yyyyMM');
    SET @sql = N'SELECT COUNT(*) FROM archive.month_end_' + @period;
    EXEC sp_executesql @sql;
END