tags:

views:

21

answers:

0

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 ALMDB.
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 | Priority, ...

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 Alarmgroup 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?

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TempALMDB') 
DROP TABLE TempALMDB

Select TOP 500 Alarmgroup, AlarmON, AlarmOFF, Priority INTO TempALMDB FROM QV_Alarms  
WHERE Alarmgroup = 'A1_1_Alarms' AND Priority =1  
ORDER BY Alarmgroup, AlarmON

SELECT X.Alarmgroup, X.AlarmON, X.AlarmOFF, Datediff(minute, X.AlarmON, X.AlarmOFF)    
    AS  Minutes FROM (
     SELECT  G2.Alarmgroup, Min(G2.Start1) AS AlarmON, Max(G2.Stop1) AS AlarmOFF FROM (
     SELECT  G1.Alarmgroup, Min(G1.Start2) AS Start1, Max(G1.Stop2) AS Stop1 FROM (
        SELECT T1.Alarmgroup, Min(T1.AlarmON) AS Start2, Max(T1.AlarmOFF) AS Stop2 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.Stop2) 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.AlarmON, X.AlarmOFF