Query su tabella eventi

domenica 31 ottobre 2010 - 20.51
Tag Elenco Tags  SQL Server 2005

peska78 Profilo | Newbie

Ho una tabella in cui vengono memorizzati delle segnalazioni di allarme, sia quando un allarme si attiva, sia quando si disattiva.
In pratica una cosa del tipo:
DataOra, Segnalazione, Stato
31/10/2010 12:00:00, Allarme 1, On
31/10/2010 12:30:00, Allarme 1, Off

E' possibile fare una query che mi ritorni una cosa simile?

DataOraAttivazione, DataOraDisattivazione, Segnalazione
31/10/2010 12:00:00, 31/10/2010 12:30:00, Allarme 1

Marco

lbenaglia Profilo | Guru

>E' possibile fare una query che mi ritorni una cosa simile?
>
>DataOraAttivazione, DataOraDisattivazione, Segnalazione
>31/10/2010 12:00:00, 31/10/2010 12:30:00, Allarme 1

Ciao Marco,

La difficoltà della query consiste nel definire delle sezioni che indentifichino la coppia di righe riferite ad un determinato allarme.
Per fare questo puoi ricorrere alle funzioni di ranking introdotte con SQL Server 2005.
Nell'esempio che sto per proporti, la differenza tra ROW_NUMBER() OVER(ORDER BY Segnalazione, Stato, DataOra) e RANK() OVER(ORDER BY Segnalazione, Stato) ci restituirà una costante che identifica ogni coppia di allarmi. A questo punto la query risulta banale:

USE tempdb; CREATE TABLE dbo.foo( DataOra datetime NOT NULL, Segnalazione varchar(10) NOT NULL, Stato bit NOT NULL ); INSERT dbo.foo VALUES ('20101031 12:00:00', 'Allarme 1', 1) , ('20101031 12:30:00', 'Allarme 1', 0) , ('20101101 10:30:00', 'Allarme 2', 0) , ('20101101 10:00:00', 'Allarme 2', 1) , ('20101101 09:30:00', 'Allarme 1', 0) , ('20101101 09:00:00', 'Allarme 1', 1) , ('20101101 12:30:00', 'Allarme 2', 0) , ('20101101 12:00:00', 'Allarme 2', 1) , ('20101101 13:00:00', 'Allarme 2', 1) , ('20101101 13:30:00', 'Allarme 2', 0) , ('20101101 13:00:00', 'Allarme 3', 1) , ('20101101 13:30:00', 'Allarme 3', 0); SET ANSI_WARNINGS OFF; WITH CTE_Pivot(Attivazione, Disattivazione, Segnalazione, Sezione) AS ( SELECT CASE Stato WHEN 1 THEN DataOra ELSE NULL END , CASE Stato WHEN 0 THEN DataOra ELSE NULL END , Segnalazione , ROW_NUMBER() OVER(ORDER BY Segnalazione, Stato, DataOra) - RANK() OVER(ORDER BY Segnalazione, Stato) FROM dbo.foo ) SELECT MAX(Attivazione) AS DataOraAttivazione , MAX(Disattivazione) AS DataOraDisattivazione , Segnalazione FROM CTE_Pivot GROUP BY Segnalazione, Sezione ORDER BY Segnalazione, DataOraAttivazione; /* Output: DataOraAttivazione DataOraDisattivazione Segnalazione ----------------------- ----------------------- ------------ 2010-10-31 12:00:00.000 2010-10-31 12:30:00.000 Allarme 1 2010-11-01 09:00:00.000 2010-11-01 09:30:00.000 Allarme 1 2010-11-01 10:00:00.000 2010-11-01 10:30:00.000 Allarme 2 2010-11-01 12:00:00.000 2010-11-01 12:30:00.000 Allarme 2 2010-11-01 13:00:00.000 2010-11-01 13:30:00.000 Allarme 2 2010-11-01 13:00:00.000 2010-11-01 13:30:00.000 Allarme 3 (6 row(s) affected) */ DROP TABLE dbo.foo;

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/

peska78 Profilo | Newbie

Ciao Lorenzo,
grazie tante per l'aiuto. Proverò subito la soluzione che mi hai indicato, e vedrò anche di cercare di capirla oltre che utilizzarla e basta.
Marco

peska78 Profilo | Newbie

Ho un ulteriore domanda:
se voglio creare una vista, invece che fare una query esplicita, devo cambiare qualcosa nello script oppure va bene così?
Inoltre se volessi creare una colonna che contiene la differenza tra le due date di attivazione e disattivazione, è possibile crearla direttamente con la query?

Grazie.
Marco

lbenaglia Profilo | Guru

>se voglio creare una vista, invece che fare una query esplicita,
>devo cambiare qualcosa nello script oppure va bene così?
Devi togliere la clausola ORDER BY in quanto non è ammessa (e non ha senso) in una vista.

>Inoltre se volessi creare una colonna che contiene la differenza
>tra le due date di attivazione e disattivazione, è possibile
>crearla direttamente con la query?
Certo, osserva il seguente esempio:

USE tempdb; CREATE TABLE dbo.foo( DataOra datetime NOT NULL, Segnalazione varchar(10) NOT NULL, Stato bit NOT NULL ); INSERT dbo.foo VALUES ('20101031 12:00:00', 'Allarme 1', 1) , ('20101031 12:30:00', 'Allarme 1', 0) , ('20101101 10:30:00', 'Allarme 2', 0) , ('20101101 10:00:00', 'Allarme 2', 1) , ('20101101 09:30:00', 'Allarme 1', 0) , ('20101101 09:00:00', 'Allarme 1', 1) , ('20101101 12:30:00', 'Allarme 2', 0) , ('20101101 12:00:00', 'Allarme 2', 1) , ('20101101 13:00:00', 'Allarme 2', 1) , ('20101101 13:30:00', 'Allarme 2', 0) , ('20101101 13:00:00', 'Allarme 3', 1) , ('20101101 13:30:00', 'Allarme 3', 0); SET ANSI_WARNINGS OFF; GO CREATE VIEW dbo.vw_Allarmi(DataOraAttivazione, DataOraDisattivazione, Minuti, Segnalazione) AS WITH CTE_Pivot(Attivazione, Disattivazione, Segnalazione, Sezione) AS ( SELECT CASE Stato WHEN 1 THEN DataOra ELSE NULL END , CASE Stato WHEN 0 THEN DataOra ELSE NULL END , Segnalazione , ROW_NUMBER() OVER(ORDER BY Segnalazione, Stato, DataOra) - RANK() OVER(ORDER BY Segnalazione, Stato) FROM dbo.foo ) SELECT MAX(Attivazione) , MAX(Disattivazione) , DATEDIFF(MINUTE, MAX(Attivazione), MAX(Disattivazione)) , Segnalazione FROM CTE_Pivot GROUP BY Segnalazione, Sezione; GO SELECT * FROM dbo.vw_Allarmi ORDER BY Segnalazione, DataOraAttivazione; /* Output: DataOraAttivazione DataOraDisattivazione Minuti Segnalazione ----------------------- ----------------------- ----------- ------------ 2010-10-31 12:00:00.000 2010-10-31 12:30:00.000 30 Allarme 1 2010-11-01 09:00:00.000 2010-11-01 09:30:00.000 30 Allarme 1 2010-11-01 10:00:00.000 2010-11-01 10:30:00.000 30 Allarme 2 2010-11-01 12:00:00.000 2010-11-01 12:30:00.000 30 Allarme 2 2010-11-01 13:00:00.000 2010-11-01 13:30:00.000 30 Allarme 2 2010-11-01 13:00:00.000 2010-11-01 13:30:00.000 30 Allarme 3 (6 row(s) affected) */ DROP VIEW dbo.vw_Allarmi; DROP TABLE dbo.foo;

>Grazie.
Prego.

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/

peska78 Profilo | Newbie

Ancora una volta, grazie mille per l'aiuto.

Un saluto.
Marco
Partecipa anche tu! Registrati!
Hai bisogno di aiuto ?
Perchè non ti registri subito?

Dopo esserti registrato potrai chiedere
aiuto sul nostro Forum oppure aiutare gli altri

Consulta le Stanze disponibili.

Registrati ora !
Copyright © dotNetHell.it 2002-2023
Running on Windows Server 2008 R2 Standard, SQL Server 2012 & ASP.NET 3.5