From 4031cede20c17c15772d8c4f17064dbc1f90234f Mon Sep 17 00:00:00 2001
From: zwl <1051256694@qq.com>
Date: 星期一, 20 四月 2026 21:43:23 +0800
Subject: [PATCH] 1.12天入库体积统计
---
src/main/resources/sql/20260415_wrk_cube_day_stat.sql | 47 +++++++++++++++++++++++++++++++++--------------
1 files changed, 33 insertions(+), 14 deletions(-)
diff --git a/src/main/resources/sql/20260415_wrk_cube_day_stat.sql b/src/main/resources/sql/20260415_wrk_cube_day_stat.sql
index 20c8bb4..a89dc5d 100644
--- a/src/main/resources/sql/20260415_wrk_cube_day_stat.sql
+++ b/src/main/resources/sql/20260415_wrk_cube_day_stat.sql
@@ -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 (
--
Gitblit v1.9.1