หน้าเว็บ

วันพุธที่ 12 พฤศจิกายน พ.ศ. 2557

สร้างTriggerคำนวนผลต่างระหว่างแถวในMS SQL

จากตัวอย่างตารางA1มีการเก็บข้อมูลจากSCADAเข้าไว้ในคอลัมน์ date_timeและv1 โดยdate_timeเก็บวันที่เวลา ส่วนv1เก็บค่าพลังงานไฟฟ้าWHจากมิเตอร์ หากต้องการคำนวนค่าความต่างระหว่างชั่วโมง วัน สามารถทำได้โดยใช้ Trigger ใน MS SQL Server ซึ่งสะดวกกว่าการใช้SCADAหรือเครื่องมืออื่นในการคำนวน

image

ส่วนคอลัมน์dv1จะใช้เก็บค่าความต่างของค่าที่บันทึกได้ขณะนั้นกับค่าสูงสุดของวันที่ผ่านมา และ hv1เก็บค่าความต่างของค่าที่บันทึกได้ขณะนั้นกับค่าสูงสุดของชั่วโมงที่ผ่านมา ดังนั้นสามารถสร้างTriggerด้วยSQL CommandในSQL Serverได้ดังนี้

CREATE Trigger dTot  
ON [dbo].[A1]
After Insert
AS
DECLARE @dat datetime
DECLARE @yes datetime
set @dat = (SELECT date_time from inserted)
set @yes = DATEADD(dd,-1,@dat)

Update dbo.[A1]
set [dv1] =
(Select v1 from inserted) -
(Select Max(v1) from [A1] where Day(date_time)=Day(@yes) and Month(date_time)=Month(@yes) and Year(date_time)=Year(@yes))
,
[hv1] =
(Select v1 from inserted)-
(Select Max(v1) from [A1] where Day(date_time)=Day(@dat) and Month(date_time)=Month(@dat) and Year(date_time)=Year(@dat) and datepart(hh,date_time)=datepart(hh,DATEADD(hh,-1,@dat)))
where date_time=@dat

Triggerข้างต้นจะทำงานเมื่อมีการเพิ่มข้อมูลใหม่เข้ามา


-----------------------------------------------------------------------------------------------------------------------------------

ถ้าต้องการเฉพาะรายชั่วโมง โดยคิดความต่างค่าสูงสุดของชั่วโมงนั้นกับชั่วโมงก่อนหน้านั้น ใช้Triggerดังนี้

 

CREATE Trigger dTot  
ON [dbo].[A1]
After Insert
AS
DECLARE @dat datetime
set @dat = (SELECT date_time from inserted)
Update dbo.[A1] set [dv1] =
 
(Select Max(v1) from [A1] where Day(date_time)=Day(@dat) and Month(date_time)=Month(@dat) and Year(date_time)=Year(@dat) and datepart(hh,date_time)=datepart(hh,@dat))
-
(Select Max(v1) from [A1] where Day(date_time)=Day(@dat) and Month(date_time)=Month(@dat) and Year(date_time)=Year(@dat) and datepart(hh,date_time)=datepart(hh,DATEADD(hh,-1,@dat)))
where date_time=@dat

 

 

 


สำหรับแสดงค่าเพื่อเอาไปใช้

แสดงแบบแยกวันที่
SELECT distinct convert(varchar, date_time, 103) as dt,MAX(dv1) as kwh
FROM [test].[dbo].[A1]
group by convert(varchar, date_time, 103)

 


แสดงแบบแยกวันที่และชั่วโมง

SELECT distinct convert(varchar, date_time, 103) as dt,datepart(hh,date_time) as hr,MAX(dv1) as kwh
FROM [test].[dbo].[A1]
group by convert(varchar, date_time, 103),datepart(hh,date_time)

image


แสดงแบบเฉพาะวันปัจจุบัน

SELECT distinct convert(varchar, date_time, 103) as dt,datepart(hh,date_time) as hr,MAX(dv1) as kwh
FROM [test].[dbo].[A1]
where Day(date_time)=Day(getdate()) and Month(date_time) = Month(getdate()) and Year(date_time)=Year(getdate())
group by convert(varchar, date_time, 103),datepart(hh,date_time)

image

ไม่มีความคิดเห็น:

แสดงความคิดเห็น