You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
51 lines
2.3 KiB
Transact-SQL
51 lines
2.3 KiB
Transact-SQL
CREATE PROCEDURE [dbo].[Record_SteamPointData]
|
|
AS
|
|
declare @begin_time datetime
|
|
declare @end_time datetime
|
|
BEGIN
|
|
set @begin_time = DATEADD(hh, -1, GETDATE());
|
|
set @end_time = DATEADD(hh, -0, GETDATE());
|
|
declare @dateinfo datetime;
|
|
set @dateinfo = GETDATE();
|
|
INSERT INTO ems_report_point_steam (monitor_code, instrument_value,begin_time,end_time,record_time)
|
|
SELECT
|
|
t1.monitor_code AS monitor_code,
|
|
CONVERT(DECIMAL(18, 2), ISNULL((t3.steam_flow-t4.steam_flow),0)) AS expend,
|
|
ISNULL(t3.steam_flow,0) AS instrument_value,
|
|
|
|
DATEADD(HH, -1, @dateinfo) AS begin_time,
|
|
DATEADD(HH, 0, @dateinfo) AS end_time,
|
|
CONVERT(VARCHAR(10), DATEADD(HH, -1, @dateinfo), 120) AS record_time
|
|
FROM ems_base_monitor_info t1
|
|
LEFT JOIN (
|
|
SELECT T1.monitor_code, T1.steam_flow,T1.collect_time
|
|
FROM ems_record_steam_instant T1
|
|
left JOIN (
|
|
SELECT monitor_code, MAX(collect_time) AS collect_time
|
|
FROM ems_record_steam_instant
|
|
WHERE collect_time BETWEEN DATEADD(HH, -1, @dateinfo) AND DATEADD(HH, 0, @dateinfo)
|
|
GROUP BY monitor_code) T2
|
|
on T1.collect_time = T2.collect_time AND T1.monitor_code = T2.monitor_code
|
|
WHERE T1.collect_time BETWEEN DATEADD(HH, -1, @dateinfo) AND DATEADD(HH, 0, @dateinfo)
|
|
) t3 ON t1.monitor_code = t3.monitor_code
|
|
|
|
|
|
LEFT JOIN (
|
|
SELECT E1.monitor_code, E1.steam_flow,E1.collect_time
|
|
FROM ems_record_steam_instant E1
|
|
JOIN (
|
|
SELECT monitor_code, MIN(collect_time) AS collect_time
|
|
FROM ems_record_steam_instant
|
|
WHERE collect_time BETWEEN DATEADD(HH, -1, @dateinfo) AND DATEADD(HH, 0, @dateinfo)
|
|
GROUP BY monitor_code) E2
|
|
on E1.collect_time = E2.collect_time AND E1.monitor_code = E2.monitor_code
|
|
WHERE E1.collect_time BETWEEN DATEADD(HH, -1, @dateinfo) AND DATEADD(HH, 0, @dateinfo)
|
|
)t4 ON t1.monitor_code = t4.monitor_code
|
|
END;
|
|
GO
|
|
|
|
|
|
|
|
|
|
|