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
|
