zy-asrs.git - Gitblit

			@@ -10,6 +10,33 @@
			END
			GO

			IF OBJECT_ID('dbo.asr_wrk_mast_log', 'U') IS NOT NULL
			AND NOT EXISTS (
			SELECT 1
			FROM sys.indexes
			WHERE name = 'IX_asr_wrk_mast_log_sts_iotime_wrkno'
			AND object_id = OBJECT_ID('dbo.asr_wrk_mast_log')
			)
			BEGIN
			CREATE INDEX IX_asr_wrk_mast_log_sts_iotime_wrkno
			ON dbo.asr_wrk_mast_log (wrk_sts, io_time, wrk_no);
			END
			GO

			IF OBJECT_ID('dbo.asr_wrk_detl_log', 'U') IS NOT NULL
			AND NOT EXISTS (
			SELECT 1
			FROM sys.indexes
			WHERE name = 'IX_asr_wrk_detl_log_wrkno_iotime_cube'
			AND object_id = OBJECT_ID('dbo.asr_wrk_detl_log')
			)
			BEGIN
			CREATE INDEX IX_asr_wrk_detl_log_wrkno_iotime_cube
			ON dbo.asr_wrk_detl_log (wrk_no, io_time)
			INCLUDE (cube_number);
			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
			@@ -37,7 +64,7 @@
			FROM n
			),
			mast AS (
			SELECT
			SELECT DISTINCT
			m.wrk_no,
			m.io_time,
			m.wrk_sts
			@@ -46,25 +73,17 @@
			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
			SUM(CASE WHEN m.wrk_sts = 5 THEN ISNULL(d.cube_number, 0) ELSE 0 END) AS wrk_sts_5_cube_sum,
			SUM(CASE WHEN m.wrk_sts = 15 THEN ISNULL(d.cube_number, 0) ELSE 0 END) AS wrk_sts_15_cube_sum
			FROM mast m
			INNER JOIN detl d
			INNER JOIN dbo.asr_wrk_detl_log d
			ON d.wrk_no = m.wrk_no
			AND d.io_time = m.io_time
			AND d.io_time >= @start_dt
			AND d.io_time < @end_dt
			GROUP BY CONVERT(date, m.io_time)
			)
			INSERT INTO dbo.asr_wrk_cube_day_stat (