IF OBJECT_ID('dbo.asr_wrk_cube_day_stat', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.asr_wrk_cube_day_stat (
        stat_date date NOT NULL,
        wrk_sts_5_cube_sum decimal(18,4) NOT NULL CONSTRAINT DF_asr_wrk_cube_day_stat_sts5 DEFAULT (0),
        wrk_sts_15_cube_sum decimal(18,4) NOT NULL CONSTRAINT DF_asr_wrk_cube_day_stat_sts15 DEFAULT (0),
        refresh_time datetime NOT NULL CONSTRAINT DF_asr_wrk_cube_day_stat_refresh DEFAULT (GETDATE()),
        CONSTRAINT PK_asr_wrk_cube_day_stat PRIMARY KEY CLUSTERED (stat_date)
    );
END
GO

IF OBJECT_ID('dbo.usp_refresh_wrk_cube_day_stat', 'P') IS NOT NULL
    DROP PROCEDURE dbo.usp_refresh_wrk_cube_day_stat;
GO

CREATE PROCEDURE dbo.usp_refresh_wrk_cube_day_stat
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @start_dt date = DATEADD(DAY, -11, CONVERT(date, GETDATE()));
    DECLARE @end_dt date = DATEADD(DAY, 1, CONVERT(date, GETDATE()));

    BEGIN TRY
        BEGIN TRAN;

        DELETE FROM dbo.asr_wrk_cube_day_stat;

        ;WITH n AS (
            SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
            UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
            UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11
        ),
        dates AS (
            SELECT DATEADD(DAY, n.n, @start_dt) AS stat_date
            FROM n
        ),
        mast AS (
            SELECT
                m.wrk_no,
                m.io_time,
                m.wrk_sts
            FROM dbo.asr_wrk_mast_log m
            WHERE m.wrk_sts IN (5, 15)
              AND m.io_time >= @start_dt
              AND m.io_time < @end_dt
        ),
        detl AS (
            SELECT
                d.wrk_no,
                d.io_time,
                SUM(ISNULL(d.cube_number, 0)) AS cube_sum
            FROM dbo.asr_wrk_detl_log d
            WHERE d.io_time >= @start_dt
              AND d.io_time < @end_dt
            GROUP BY d.wrk_no, d.io_time
        ),
        agg AS (
            SELECT
                CONVERT(date, m.io_time) AS stat_date,
                SUM(CASE WHEN m.wrk_sts = 5 THEN d.cube_sum ELSE 0 END) AS wrk_sts_5_cube_sum,
                SUM(CASE WHEN m.wrk_sts = 15 THEN d.cube_sum ELSE 0 END) AS wrk_sts_15_cube_sum
            FROM mast m
            INNER JOIN detl d
                ON d.wrk_no = m.wrk_no
               AND d.io_time = m.io_time
            GROUP BY CONVERT(date, m.io_time)
        )
        INSERT INTO dbo.asr_wrk_cube_day_stat (
            stat_date,
            wrk_sts_5_cube_sum,
            wrk_sts_15_cube_sum,
            refresh_time
        )
        SELECT
            ds.stat_date,
            CAST(ISNULL(a.wrk_sts_5_cube_sum, 0) AS decimal(18,4)),
            CAST(ISNULL(a.wrk_sts_15_cube_sum, 0) AS decimal(18,4)),
            GETDATE()
        FROM dates ds
        LEFT JOIN agg a
            ON a.stat_date = ds.stat_date;

        COMMIT TRAN;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRAN;
        THROW;
    END CATCH
END
GO

EXEC dbo.usp_refresh_wrk_cube_day_stat;
GO