开发者

Time range with overlapping --- without temporary table result is OK otherwise wrong

Below is a working SQL query for calculation of time differences with overlapping.

For certain reasons I need to create a view - therefore I cannot use the temporary table TempALMDB. Instead the view begins with SELECT X.Alarmgroup and I have to use the already created view QV_Alarms . But in this case the output is different to the version with the temporary table.

The original view contains the following colums: Alarmgroup, AlarmON, AlarmOFF, Priorit开发者_Python百科y, ...

AlarmOn and AlarmOFF is the arrival and returning time of an alarm. The calculation is done for each alarm group. To reduce the calculation time per query I included the WHERE clauses for Alarmgroup and Priority (in the temporary table). The clause for the group is also possible in the second part .

My questions:

1. Why do I get different and wrong results when I only use the PART 2 (having replaced 4 times TempALMDB by QV_Alarms)?

2. How is it possibly to include the WHERE clause for the priority in PART 2?

-- PART 1: Temporary table
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TempALMDB')   
DROP TABLE TempALMDB

Select TOP 500 Alarmgroup, AlarmON, AlarmOff INTO TempALMDB FROM QV_Alarms   
WHERE Alarmgroup = 'A1_1_Alarms' 
ORDER BY Alarmgroup, AlarmON  

--PART 2: Time difference calculation 
SELECT X.Alarmgroup, X.Coming, X.Going, Datediff(minute, X.Coming, X.Going)  
AS minutes FROM (
  SELECT  G2.Alarmgroup, Min(G2.Start1) AS Coming, Max(G2.Going1)   
  AS Going FROM (
     SELECT  G1.Alarmgroup, Min(G1.Start2) AS Start1, Max(G1.Going2)   
     AS Going1 FROM (
         SELECT T1.Alarmgroup, Min(T1.AlarmON) AS Start2, Max(T1.AlarmOff) 
         AS Going2 FROM TempALMDB AS T1 INNER JOIN  TempALMDB AS T2 
         ON (T1.Alarmgroup = T2.Alarmgroup AND T1.AlarmON < T2.AlarmOff 
         AND T1.AlarmOff > T2.AlarmON AND NOT (T1.AlarmON = T2.AlarmON 
         AND T1.AlarmOff = T2.AlarmOff) )
         GROUP BY  T1.Alarmgroup,  
         CASE WHEN T1.AlarmON BETWEEN T2.AlarmON AND T2.AlarmOff 
       THEN T2.AlarmON ELSE T1.AlarmON END,
         CASE WHEN T1.AlarmOff BETWEEN T2.AlarmON AND T2.AlarmOff 
       THEN T2.AlarmOff ELSE T1.AlarmOff END
     ) AS G1
     GROUP BY G1.Alarmgroup, G1.Going2) AS G2
     GROUP BY G2.Alarmgroup, G2.Start1

UNION ALL

SELECT U1.Alarmgroup, U1.AlarmON, U1.AlarmOff
FROM TempALMDB AS U1 LEFT JOIN TempALMDB AS U2 
ON (U1.Alarmgroup = U2.Alarmgroup AND U1.AlarmON < U2.AlarmOff 
AND U1.AlarmOff > U2.AlarmON 
AND NOT (U1.AlarmON = U2.AlarmON AND U1.AlarmOff = U2.AlarmOff))
WHERE U2.Alarmgroup IS NULL 
) AS X
WHERE X.Alarmgroup = 'A1_1_Alarms' 
ORDER BY  X.Alarmgroup, X.Coming, X.Going


Have you tried replacing all instances of TempALMDB in part 2 with this?

(Select TOP 500 Alarmgroup, AlarmON, AlarmOff FROM QV_Alarms
WHERE Alarmgroup = 'A1_1_Alarms'
ORDER BY Alarmgroup, AlarmON)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜