Newsgroup: it.comp.software.database
Titolo: Query SQL:((

Salve gente,
ho un problemino che mi infastidisce.
ho una tabella con i seguenti campi:
cod_mezzo,
DataOraRil,
Verso (Entrata 1/ Uscita 2)
Es:
ID,Cod_mezzo,DataOraRil,Verso
1,1250,12/10/2002 10:10,1
2,1230,12/10/2002 10:12,1
3,1250,12/10/2002 10:15,2
4,1250,12/10/2002 10:20,1
5,1230,12/10/2002 15:12,2
6,1250,12/10/2002 15:20,2
Vorrei, utilizzando solo SQL senza cursori, ottenere l'elenco dei
mezzi in
cui il delta tra l'entrata e l'uscita sia maggiore di 2 ore.
Secondo voi č possibile senza iniziare a fare cicli con i cursori.
Grazie.

 Secondo voi č possibile senza iniziare a fare cicli con i cursori.
Se il tuo DB supporta la clausola HAVING sě. Prova a dare un'occhio alla 
documentazione... se ce la trovi, dovresti anche capire facilmente come 
usarla ;-)
bye!
-- 
Fabrizio "Hermooz" Ermini

 
'spetta mo': vedo ora che hai piů passaggi ingresso/uscita per ogni 
mezzo... questo complica non poco le cose, in quanto c'e' da abbinare ad 
ogni uscita il relativo ingresso (se possibile) e cosě via.
In tal caso mi sa che č assolutamente necessario utilizzare un cursore, 
non vedo come sia possibile farlo con una query "secca".
Sorry per la precedente risposta troppo affrettata...
-- 
Fabrizio "Hermooz" Ermini

Sorry per la precedente risposta troppo affrettata...
Nessun problema ;)
Comunque il mio DB è MSDE2000 (un quasi SQL 2000).
Speravo in qualche sintassi strana ma mi sa che dovro' iniziare ad
usare i cursori :((
Se qualcuno a qualche brillante idea si faccia avanti :))
CIAO

 usare i cursori :((
Ti tocca, anche perchè devi prevedere una gestione degli errori 
abbastanza accurata: se per un qualche motivo ti salta la corrispondenza 
1:1 tra ingressi e uscite e non te ne accorgi rischi di tirare fuori 
fischi per fiaschi...


 di 1/12 (2 ore ?)

 >
Ho fatto una piccola prova e mi pare che questa semplice soluzione funzioni.
Non conoscendo il data base sul quale sara' eseguita ho semplificato la
clausola
DELTA > 2 0re con DIFF_ORE(b.data-a.data) > 2
in generale e' sempre possibile scrivere delle clausole di where per
calcolare
questa condizione (trunc delle date ... ecc )
QUESTA e' la tavola con data campo intero.
1> select * from biagio
2> go
 cod_mezzo data verso
 ----------- ----------- -----------
        1250 1 1
        1250 2 2
        1350 1 1
        1350 10 2
(4 rows affected)
METTENDO IN SELF JOIN la tavola
1> select *
2> from biagio a,
3> biagio b
4> where
5> a.cod_mezzo=b.cod_mezzo
6> go
 cod_mezzo data verso cod_mezzo data verso
 ----------- ----------- ----------- ----------- ----------- -----------
        1250 1 1 1250 1 1
        1250 1 1 1250 2 2
        1250 2 2 1250 1 1
        1250 2 2 1250 2 2
        1350 1 1 1350 1 1
        1350 1 1 1350 10 2
        1350 10 2 1350 1 1
        1350 10 2 1350 10 2
SELEZIONANDO LE TUPLE ENTRATA --> USCITA :
1> select *
2> from biagio a,biagio b
3> where
4> a.verso = 1
5> and b.verso=2
6> and a.cod_mezzo=b.cod_mezzo
7> go
 cod_mezzo data verso cod_mezzo data verso
 ----------- ----------- ----------- ----------- ----------- -----------
        1250 1 1 1250 2 2
        1350 1 1 1350 10 2
LA SOLUZIONE FINALE E' LA SEGUENTE :
select distinct(a.cod_mezzo)
from
       tavola a,
       tavola b
where
      b.data-a.data > 2 /* Diff_ore(b.data-a.data ) > 2 */
and a.verso=1
and b.verso=2 /*basterebbe a.verso < b.verso ma nel caso di errori in
inserimento .. */
and a.cod_mezzo=b.cod_mezzo
/
 cod_mezzo
 -----------
        1350
Saluti
Gauss
--------------------------------
Inviato via 
http://usenet.libero.it

 Secondo voi è possibile senza iniziare a fare cicli con i cursori.
Ciao Fabio,
ti allego una possibile soluzione con anche alcune riflessioni
legate ai vincoli di integrita' che potresti voler implementare.
I requisiti per tali vincoli me li sono inventati, quindi
potrebbero anche non essere coerenti con quello che stai
facendo :-)
Il post e' piuttosto lungo ed in verita' lo sarebbe ancora di
piu' perche' ho inziato anche a fare delle prove modificando
lo schema, purtroppo pero' non ho avuto tempo di finire quindi
magari settimana prossima faro' un follow-up a questo post con
le altre soluzioni.
Una prevede l'utilizzo di due relazioni che esplicitamente
modellano i mezzi in entrata ed in uscita, una invece
prevede l'utilizzo di una singola relazione che pero'
definisce esplicitamente il periodo (in soldoni data di
entrata  e data di uscita).
Per il momento c'e' solo la prima parte e non escludo ci
possano essere degli errori, mancanze o quant'altro dato
ho fatto il post a piu' riprese.
Ciao,
-- 
Gianluca Hotz
Backoffice MVP (SQL Server) MCP SQL Server and MCP Windows
Vice-President of the Italian User Group for SQL Server
Founding Member of the Italian User Group for .NET
http://www.ghotz.com
http://www.ugiss.org
--
USE tempdb
GO
IF OBJECT_ID('dbo.RilevazioniRitardi') IS NOT NULL
 DROP VIEW dbo.RilevazioniRitardi
GO
IF OBJECT_ID('dbo.RilevazioneEntrateUscite') IS NOT NULL
 DROP VIEW dbo.RilevazioneEntrateUscite
GO
IF OBJECT_ID('dbo.rilevazioni') IS NOT NULL
 DROP TABLE dbo.rilevazioni
GO
/*
** creiamo la tabella
*/
CREATE TABLE dbo.rilevazioni(
 IDRilevazione int   NOT NULL,
 IDMezzo   int   NOT NULL,
 DataRilevazione datetime NOT NULL,
 Verso   int   NOT NULL,
 CONSTRAINT pk_rilevazioni
 PRIMARY KEY (IDRilevazione),
 CONSTRAINT ak_rilevazioni
 UNIQUE  (IDMezzo, DataRilevazione)
)
GO
/*
** dati di prova
*/
INSERT dbo.rilevazioni
VALUES (1, 1250, '2002-10-12 10:10', 1)
INSERT dbo.rilevazioni
VALUES (2, 1230, '2002-10-12 10:12', 1)
INSERT dbo.rilevazioni
VALUES (3, 1250, '2002-10-12 10:15', 2)
INSERT dbo.rilevazioni
VALUES (4, 1250, '2002-10-12 10:20', 1)
INSERT dbo.rilevazioni
VALUES (5, 1230, '2002-10-12 15:12', 2)
INSERT dbo.rilevazioni
VALUES (6, 1250, '2002-10-12 15:20', 2)
GO
/*
** il primo obiettivo e' quello di ottenere
** delle righe con degli intervalli al posto
** di righe con istanti
*/
CREATE VIEW dbo.RilevazioneEntrateUscite
AS
SELECT R1.IDMezzo
,  R1.DataRilevazione AS DataRilevazioneEntrata
,  (SELECT R2.DataRilevazione
   FROM dbo.rilevazioni AS R2
   WHERE R2.IDMezzo = R1.IDMezzo
     AND R2.Verso = 2
     AND R2.DataRilevazione =
    (SELECT MIN(R3.DataRilevazione)
     FROM dbo.rilevazioni AS R3
     WHERE R3.IDMezzo = R2.IDMezzo
       AND R3.Verso = 2
       AND R3.DataRilevazione > R1.DataRilevazione
    )
  ) AS DataRilevazioneUscita
FROM dbo.rilevazioni AS R1
WHERE R1.Verso = 1
GO
/*
** una prima versione della query potrebbe essere
** la seguente
*/
SELECT IDMezzo
,  DataRilevazioneEntrata
,  DataRilevazioneUscita
,  DATEDIFF(hh, DataRilevazioneEntrata,
  DataRilevazioneUscita) AS Ore
FROM dbo.RilevazioneEntrateUscite
WHERE DATEDIFF(hh, DataRilevazioneEntrata,
  DataRilevazioneUscita) >= 2
ORDER BY
  IDMezzo
,  DataRilevazioneEntrata
GO
/*
** inseriamo una rilevazione in piu':
** un mezzo uscito, ma non ancora rientrato
*/
INSERT dbo.rilevazioni
VALUES (7, 1230, '2002-10-12 16:00', 1)
GO
/*
** la query precedente non ritorna una valore per
** l'attributo DataRilevazioneUscita per l'ultima
** riga inserita
*/
SELECT IDMezzo
,  DataRilevazioneEntrata
,  DataRilevazioneUscita
FROM dbo.RilevazioneEntrateUscite
ORDER BY
  IDMezzo
,  DataRilevazioneEntrata
GO
/*
** dato che quel NULL signfica in realta' che il mezzo
** non e' mai uscito, posso pensare di includerlo
** tout court tra i risultati...
*/
SELECT IDMezzo
,  DataRilevazioneEntrata
,  DataRilevazioneUscita
,  DATEDIFF(hh, DataRilevazioneEntrata,
  DataRilevazioneUscita) AS Ore
FROM dbo.RilevazioneEntrateUscite
WHERE DATEDIFF(hh, DataRilevazioneEntrata,
  DataRilevazioneUscita) >= 2
   OR DataRilevazioneUscita IS NULL
ORDER BY
  IDMezzo
,  DataRilevazioneEntrata
GO
/*
** ... oppure, forse piu' realisticamente, posso
** considerare come estremo superiore del periodo
** la data corrente, calcolando da quanto e'
** uscito e visualizzarlo solo se e' uscito da
** almeno 2 ore
*/
SELECT IDMezzo
,  DataRilevazioneEntrata
,  DataRilevazioneUscita
,  DATEDIFF(hh, DataRilevazioneEntrata,
  DataRilevazioneUscita) AS Ore
FROM dbo.RilevazioneEntrateUscite
WHERE DATEDIFF(hh, DataRilevazioneEntrata,
  COALESCE(DataRilevazioneUscita, GETDATE()
  )) >= 2
ORDER BY
  IDMezzo
,  DataRilevazioneEntrata
GO
/*
** per praticita' mi costurisco una vista che tra
** l'altro mi ritorna i dati con valori
** piu' significativi
*/
CREATE VIEW dbo.RilevazioniRitardi
AS
SELECT IDMezzo
,  DataRilevazioneEntrata
,  COALESCE(DataRilevazioneUscita,
  GETDATE()) AS DataRilevazioneUscita
,  DATEDIFF(hh, DataRilevazioneEntrata,
  COALESCE(DataRilevazioneUscita,
  GETDATE())) AS Ore
FROM dbo.RilevazioneEntrateUscite
WHERE DATEDIFF(hh, DataRilevazioneEntrata,
  COALESCE(DataRilevazioneUscita, GETDATE()
  )) >= 2
GO
SELECT *
FROM dbo.RilevazioniRitardi
ORDER BY
  IDMezzo
,  DataRilevazioneEntrata
GO
/*
** ora dobbiamo risolvere una importante questione di cui
** non ci siamo curati perche' comunque non invalida la
** richiesta: come definire i vincoli di integrita'
**
** esiste la possiblita' che manchino alcune rilevazioni ?
** che quindi esistano dei mezzi che sono entrati e mai
** usciti e, viceversa, che esistano mezzi che sono usciti
** senza mai essere entrati ?
**
** per prima cosa verifichiamo che la presenza di un mezzo
** uscito senza mai essere entrato non invalida la
** richiesta
*/
INSERT dbo.rilevazioni
VALUES (8, 1250, '2002-10-12 16:00', 2)
GO
SELECT *
FROM dbo.RilevazioniRitardi
ORDER BY
  IDMezzo
,  DataRilevazioneEntrata
GO
DELETE dbo.rilevazioni
WHERE IDRilevazione = 8
GO
/*
** Proviamo a definire alcuni vincoli (sono solo a scopo
** esemplificativo, non so quali siano quelli reali)
**
** Requisiti
**
** R1. un mezzo non puo' uscire se non e' mai entrato
**
** R2. un mezzo non puo' entrare nuovamente se e' gia'
**     entrato senza mai essere uscito
**
** R3. un mezzo non puo' entrare ed uscire nello stesso
**     instante (ovvero esiste sempre un periodo, per
**     quanto breve, che passa tra l'entrata e l'uscita
**     di un mezzo)
**
** R4. la rilevazione di un entrata o uscita di un
**     mezzo deve essere sempre superiore all'ultima
**     data di rilevazione del mezzo in questione
**
** Note:
**
** N1. dato che SQL Server non supporta (ancora) le subquery
**     nei vincoli di tipo CHECK, dobbiamo usare i trigger
**
** N2. il requisito R3 e' gia' implementato tramite
**     il vincolo UNIQUE della tabella
**
** N5. i requisti R1 ed R2 possono essere ridotti al
**     fatto che per ogni mezzo deve sempre esistere
**     una coppia di rilevazioni (una in entrata ed una
**     in uscita), tranne per l'ultima rilevazione che
**     deve essere in entrata e per la quale puo'
**     non eisistere anche la corrispondente rilvazione
**     in uscita
**
** N4. l'implementazione dei requisiti R1 e R2 implica
**     gia' anche il requisito R4
*/
CREATE TRIGGER tgidu_rilevazioni
ON dbo.rilevazioni
FOR INSERT, DELETE, UPDATE
AS
 /*
 ** R1. un mezzo non puo' uscire se non e' mai entrato
 **
 ** In SQL: se esistono mezzi in uscita senza la
 ** corrispondente rilevazione in entrata, l'operazione
 ** non e' lecita
 */
 IF EXISTS(
  SELECT *
  FROM dbo.rilevazioni AS R1
  WHERE R1.Verso = 2
    AND NOT EXISTS
    (SELECT *
     FROM dbo.rilevazioni AS R2
     WHERE R2.IDMezzo = R1.IDMezzo
       AND R2.Verso = 1
       AND R2.DataRilevazione <
      R1.DataRilevazione
       AND (R2.DataRilevazione >
      (SELECT MAX(R3.DataRilevazione)
       FROM dbo.rilevazioni AS R3
       WHERE R3.IDMezzo = R2.IDMezzo
         AND R3.Verso = 2
         AND R3.DataRilevazione <
        R1.DataRilevazione
      )
      OR
      (SELECT MIN(R3.DataRilevazione)
       FROM dbo.rilevazioni AS R3
       WHERE R3.IDMezzo = R2.IDMezzo
         AND R3.Verso = 2
         AND R3.DataRilevazione <
        R1.DataRilevazione
      ) IS NULL)
    )
  )
 BEGIN
  RAISERROR('Uno o piu'' mezzi escono senza mai essere entrati'
  , 16, 1)
  ROLLBACK TRANSACTION
  RETURN
 END
 /*
 ** R2. un mezzo non puo' entrare nuovamente se e' gia'
 **     entrato senza mai essere uscito
 **
 ** In SQL: se esistono mezzi in entrata senza la
 ** corrispondente rilevazione in uscita, tranne
 ** per l'ultima rilevazione del mezzo (per la
 ** quale e' lecito che non sia stata ancora campionata
 ** l'uscita), l'operazione tentata non e' lecita
 */
 IF EXISTS(
  SELECT *
  FROM dbo.rilevazioni AS R1
  WHERE R1.Verso = 1
    AND NOT EXISTS
    (SELECT *
     FROM dbo.rilevazioni AS R2
     WHERE R2.IDMezzo = R1.IDMezzo
       AND R2.Verso = 2
       AND R2.DataRilevazione >
      R1.DataRilevazione
       AND (R2.DataRilevazione <
      (SELECT MIN(R3.DataRilevazione)
       FROM dbo.rilevazioni AS R3
       WHERE R3.IDMezzo = R2.IDMezzo
         AND R3.Verso = 1
         AND R3.DataRilevazione >
        R1.DataRilevazione
      )
      OR
      (SELECT MIN(R3.DataRilevazione)
       FROM dbo.rilevazioni AS R3
       WHERE R3.IDMezzo = R2.IDMezzo
         AND R3.Verso = 1
         AND R3.DataRilevazione >
        R1.DataRilevazione
      ) IS NULL)
    )
     AND R1.DataRilevazione <>
    (SELECT MAX(R4.DataRilevazione)
     FROM dbo.rilevazioni AS R4
     WHERE R4.IDMezzo = R1.IDMezzo
       AND R4.Verso = 1
    )
  )
 BEGIN
  RAISERROR('Uno o piu'' mezzi cercano di rientrare senza essere usciti'
  , 16, 1)
  ROLLBACK TRANSACTION
  RETURN
 END
GO
/*
** verifichiamo le regole
**
** Tentativi di violazione R1
**
** il mezzo esce senza essere mai entrato
*/
INSERT dbo.rilevazioni
VALUES (8, 1250, '2002-10-12 16:00', 2)
GO
/*
** ma anche eliminando l'entrata di un mezzo
** nel passato...
*/
DELETE dbo.rilevazioni WHERE IDRilevazione = 3
GO
/*
** per farlo dovrei eliminare sia l'entrara che
** l'uscita (sia la rilevazione 1 che la 3)
**
** Tentativo violazione R2
** il mezzo ri-entra senza essere uscito
*/
INSERT dbo.rilevazioni
VALUES (8, 1230, '2002-10-12 16:30', 1)
GO
/*
** Tentativo violazione R3
** il mezzo esce nello stesso istante in
** cui e' entrato
*/
INSERT dbo.rilevazioni
VALUES (8, 1230, '2002-10-12 16:00', 1)
GO
/*
** Tentativo violazione R4
** il mezzo cerca di entrare nel passato
*/
INSERT dbo.rilevazioni
VALUES (8, 1250, '2002-10-12 09:00', 1)
GO
/*
** passiamo ora ad alcune valutazioni relative
** alla decisione di modellare le rilevazioni
** tramite una sola tabella e tramite una singola
** data
**
** prima di tutto l'attributo IDRilevazione e'
** una chiave surrogata, dato che non possono
** esserci rilevazioni per lo stesso mezzo
** nello stesso istante, la chiave e' data
** dagli attributi IDMezzo e DataRilevazione
**
** in effetti non l'abbiamo mai usata negli
** esempi di prima (tranne per il tentativo
** di delete, ma solo per brevita')
**
** quindi se non ci sono tabelle che hanno
** foreign key verso questa tabella, possiamo
** anche eliminarla.
**
** <continua..>
**
*/

 usare i cursori :((
mai mollare :-)