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

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

ส่วนคอลัมน์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)

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

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)