|
Queries |
| 2.28 Come creare una query per normalizzare una tabella timbrature da badge |
| Roberto, Giorgio Rancati |
Ipotiziamo di avere una tabella chiamata Movimenti che abbia la seguente struttura:IdBadge Numerico DataOra Data/ora TipoMovimento NumericoIn questa tabella vengono memorizzate le singole timbrature di entrata ed uscita ottenute da un rilevatore di badge; in questa tabella il campo TipoMovimento assumerà il valore 0 per le entrate e 1 per le uscite. Ipotiziamo che si vogliano normalizzare i dati di questa tabella in una query che contenga i seguenti campi: IdBadge Giorno (data della timbratura) OraIn (ora di entrata) OraOut (ora di uscita) Totale (differenza tra OraIn ed OraOut espresso in minuti)Questo può essere ottenuto da una query che abbia il seguente codice SQL:
SELECT Movimenti.IdBadge,
CDate(Fix([DataOra])) AS Giorno,
Format([DataOra],"hh:nn") AS OraIn,
IIf(IsNull(DLookUp("DataOra","Movimenti","IdBadge=" & [IdBadge] &
" And TipoMovimento = 1 And Int(DataOra) =#" & Format(Int([DataOra]),"mm/dd/yyyy") & "#")),Null,Format(DLookUp("DataOra","Movimenti","IdBadge=" &
[IdBadge] & " And TipoMovimento = 1 And Int(DataOra) =#" & Format(Int([DataOra]),"mm/dd/yyyy") & "#"),"hh\:nn")) AS OraOut,
DateDiff("n",[OraIn],[OraOut]) AS Totale
FROM Movimenti
WHERE (((Movimenti.TipoMovimento)=0))
ORDER BY Movimenti.DataOra;
Questo codice SQL è contenuto nel database di esempio allegato a questa FAQ nella query chiamata ConAggregazione.Questa soluzione però, pur dando i risultati voluti, presenta gli inconvenienti che non è trasportabile verso altre piattaforme in quanto usa le funzione di aggregazione sui dominii che sono proprietarie di Access ed inoltre, a causa dell'uso di tali funzioni, potrà risultare lenta. In alternativa si può creare una query che non usa le funzioni di aggregazione sui dominii e che avrà il seguente codice SQL:
SELECT IdBadge,
Giorno,
OraIn,
OraOut,
DateDiff("n", OraIn, OraOut) AS Totale
FROM [SELECT IdBadge,
CDate(Fix(DataOra)) AS Giorno,
Format(DataOra,"hh:nn") AS OraIn,
(SELECT TOP 1 Format(DataOra,"hh:nn") AS OraOut1
FROM Movimenti AS M2
WHERE M2.TipoMovimento = 1
AND M2.IdBadge = M1.IdBadge
AND M2.DataOra > M1.DataOra
AND CDate(Fix(M2.DataOra)) = CDate(Fix(M1.DataOra))
ORDER BY M2.DataOra) AS OraOut
FROM Movimenti AS M1
WHERE (((M1.TipoMovimento)=0))
ORDER BY M1.DataOra]. AS Tx
ORDER BY IdBadge, Giorno, OraIn;
Nel codice SQL di cui sopra è previsto anche che per errore non venga effettuata la timbratura di uscita.Questo codice è contenuto nel database di esempio allegato a questa FAQ nella query chiamata QueryCompleta. Sicuramente questa query è bbastanza complessa e può risultare di non facile comprensione: può risultare anche abbastanza difficile immaginare come possa essere costruita. Il segreto per costruire una query come questa è quello di analizzare e risolvere un problema alla volta, al limite scrivendo per ogni problema elementare queries separate che verranno poi fuse o con la tecnica della query/subquery o applicando la tecnica delle tabelle derivate. Come prima cosa si crea una semplice query che visualizzi, relativamente ai soli record entrata (TipoMovimento = 0), solo i campi IdBadge, la data della timbratura e l'ora di entrata: tutto questo è ottenibile da una query che abbia il seguente codice SQL:
SELECT IdBadge,
CDate(Fix([DataOra])) AS Giorno,
Format([DataOra],"hh.nn") AS OraIn
FROM Movimenti AS M1
WHERE (((TipoMovimento)=0))
ORDER BY DataOra;
Nello scrivere questo codice SQL si è già tenuto conto che in seguito questo codice SQL dovrà essere fuso con quelli di altre queries basate sulla tabella Movimenti, quindi nella FROM si è assegnato l'alias M1 alla tabella Movimenti, alias che servirà a qualificare in maniera univoca i nomi dei campi.Questo codice SQL è contenuto nel database di esempio allegato a questa FAQ nella query chiamata QueryOraIn. Come seconda cosa si può creare una query che determini solo l'orario di uscita, rilevandolo dal primo record uscita (TipoMovimento = 1) successivo alla timbratura di entrata cui appartiene (stessa matricola e stessa data di timbratura); questo può essere realizzato con una query che abbia il seguente codice SQL:
SELECT TOP 1 Format([DataOra],"hh.nn") AS OraOut
FROM Movimenti AS M2
WHERE M2.TipoMovimento = 1
AND M2.IdBadge = M1.IdBadge
AND M2.DataOra > M1.DataOra
AND CDate(Fix(DataOra)) = CDate(Fix(DataOra))
ORDER BY M2.DataOra;
Nello scrivere questo codice SQL si ègià tenuto conto che in seguito questo codice SQL dovrà essere fuso con quelli di altre queries basate sulla tabella Movimenti, quindi nella FROM si è assegnato l'alias M2 alla tabella Movimenti, alias che servirà a qualificare in maniera univoca i nomi dei campi.Questo codice SQL è contenuto nel database di esempio allegato a questa FAQ nella query chiamata QueryOraOut. A questo punto, inserendo come subquery il codice SQL di QueryOraOut in un campo calcolato della QueryOraIn, si otterrà il seguente codice SQL:
SELECT IdBadge,
CDate(Fix(DataOra)) AS Giorno,
Format(DataOra,"hh:nn") AS OraIn,
(SELECT TOP 1 Format(DataOra,"hh:nn") AS OraOut1
FROM Movimenti AS M2
WHERE M2.TipoMovimento = 1
AND M2.IdBadge = M1.IdBadge
AND M2.DataOra > M1.DataOra
AND CDate(Fix(M2.DataOra)) = CDate(Fix(M1.DataOra))
ORDER BY M2.DataOra) AS OraOut
FROM Movimenti AS M1
WHERE (((M1.TipoMovimento)=0))
ORDER BY M1.DataOra;
Questo codice SQL è contenuto nel database di esempio allegato a questa FAQ nella query chiamata QueryOraIn_OraOutCome è evidenta aprendo tale query nel database di esempio, a questo punto essa visualizza tutte le informazioni richieste ad eccezione della differenza tra i due orari espressa in minuti. Per ottenere anche questo è sufficiente creare una query che usi la QueryOraIn_OraOut come tabella derivata (ovvero inserendola come subquery nella proposizione FROM); nella SELECT di questa nuova query saranno indicati tutti i campi di QueryOraIn_OraOut oltre al campo calcolato Totale che effettua la differenza tra gli orari tramite una funzione DateDiff. Quest'ultima query altro non è che la query chiamata QueryCompleta e di cui qui sopra si è già dato il suo codice SQL. Download: |
