IF OBJECT_ID('dbo.v_asr_inout_hourly_stat', 'V') IS NULL
BEGIN
    EXEC('CREATE VIEW dbo.v_asr_inout_hourly_stat AS
        SELECT CONVERT(char(13), GETDATE(), 120) AS ymd,
               CAST(0 AS int) AS inqty,
               CAST(0 AS int) AS outqty,
               CAST(0 AS int) AS cube_inqty,
               CAST(0 AS int) AS cube_outqty,
               CAST(0 AS int) AS out_teu');
END
GO

ALTER VIEW dbo.v_asr_inout_hourly_stat
AS
WITH bounds AS (
    SELECT
        DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0) AS curr_hour,
        DATEADD(HOUR, -12, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0)) AS start_hour,
        DATEADD(HOUR,  1, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0)) AS end_hour
),
hours AS (
    SELECT DATEADD(HOUR, -v.n, b.curr_hour) AS hour_dt
    FROM bounds b
    CROSS JOIN (VALUES
        (0),(1),(2),(3),(4),(5),(6),
        (7),(8),(9),(10),(11),(12)
    ) v(n)
),
work_rows AS (
    SELECT
        m.wrk_no,
        m.wrk_date,
        m.wrk_sts,
        m.cube_number,
        m.teu,
        m.user_no,
        1 AS in_cnt,
        0 AS out_cnt,
        1 AS source_order
    FROM dbo.asr_wrk_mast m
    CROSS JOIN bounds b
    WHERE m.source_sta_no IS NOT NULL
      AND m.wrk_sts > 1
      AND m.wrk_sts <= 5
      AND m.wrk_date >= b.start_hour
      AND m.wrk_date <  b.end_hour

    UNION ALL

    SELECT
        m.wrk_no,
        m.wrk_date,
        m.wrk_sts,
        m.cube_number,
        m.teu,
        m.user_no,
        0 AS in_cnt,
        1 AS out_cnt,
        1 AS source_order
    FROM dbo.asr_wrk_mast m
    CROSS JOIN bounds b
    WHERE m.sta_no IS NOT NULL
      AND m.wrk_sts >= 14
      AND m.wrk_sts <= 15
      AND m.wrk_date >= b.start_hour
      AND m.wrk_date <  b.end_hour

    UNION ALL

    SELECT
        m.wrk_no,
        m.wrk_date,
        m.wrk_sts,
        m.cube_number,
        m.teu,
        m.user_no,
        1 AS in_cnt,
        0 AS out_cnt,
        2 AS source_order
    FROM dbo.asr_wrk_mast_log m
    CROSS JOIN bounds b
    WHERE m.source_sta_no IS NOT NULL
      AND m.wrk_sts > 1
      AND m.wrk_sts <= 5
      AND m.wrk_date >= b.start_hour
      AND m.wrk_date <  b.end_hour

    UNION ALL

    SELECT
        m.wrk_no,
        m.wrk_date,
        m.wrk_sts,
        m.cube_number,
        m.teu,
        m.user_no,
        0 AS in_cnt,
        1 AS out_cnt,
        2 AS source_order
    FROM dbo.asr_wrk_mast_log m
    CROSS JOIN bounds b
    WHERE m.sta_no IS NOT NULL
      AND m.wrk_sts >= 14
      AND m.wrk_sts <= 15
      AND m.wrk_date >= b.start_hour
      AND m.wrk_date <  b.end_hour
),
base_agg AS (
    SELECT
        DATEADD(HOUR, DATEDIFF(HOUR, 0, d.wrk_date), 0) AS hour_dt,
        CAST(SUM(d.in_cnt) AS INT) AS inqty,
        CAST(SUM(d.out_cnt) AS INT) AS outqty,
        CAST(SUM(CASE WHEN d.wrk_sts = 5  THEN ISNULL(d.cube_number, 0) ELSE 0 END) AS INT) AS cube_inqty,
        CAST(SUM(CASE WHEN d.wrk_sts = 15 THEN ISNULL(d.cube_number, 0) ELSE 0 END) AS INT) AS cube_outqty
    FROM work_rows d
    GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, d.wrk_date), 0)
),
out_teu_ranked AS (
    SELECT
        r.hour_dt,
        r.user_key,
        r.teu,
        ROW_NUMBER() OVER (
            PARTITION BY r.user_key
            ORDER BY CASE WHEN r.wrk_sts = 15 THEN 0 ELSE 1 END,
                     r.wrk_date DESC,
                     r.source_order,
                     r.wrk_no
        ) AS rn
    FROM (
        SELECT
            DATEADD(HOUR, DATEDIFF(HOUR, 0, d.wrk_date), 0) AS hour_dt,
            d.wrk_no,
            d.wrk_date,
            d.wrk_sts,
            ISNULL(d.teu, 0) AS teu,
            NULLIF(LTRIM(RTRIM(d.user_no)), '') AS user_key,
            d.source_order
        FROM work_rows d
        WHERE d.out_cnt = 1
    ) r
),
out_teu_agg AS (
    SELECT
        r.hour_dt,
        CAST(SUM(CASE WHEN r.user_key IS NULL OR r.rn = 1 THEN r.teu ELSE 0 END) AS INT) AS out_teu
    FROM out_teu_ranked r
    GROUP BY r.hour_dt
)
SELECT
    CONVERT(CHAR(13), h.hour_dt, 120) AS ymd,
    ISNULL(a.inqty, 0) AS inqty,
    ISNULL(a.outqty, 0) AS outqty,
    ISNULL(a.cube_inqty, 0) AS cube_inqty,
    ISNULL(a.cube_outqty, 0) AS cube_outqty,
    ISNULL(t.out_teu, 0) AS out_teu
FROM hours h
LEFT JOIN base_agg a
    ON a.hour_dt = h.hour_dt
LEFT JOIN out_teu_agg t
    ON t.hour_dt = h.hour_dt;
GO