Newsgroup: it.comp.appl.access
Titolo: Query di raggruppamento orari 2

Ciao a tutti.
Ci sto sbattendo la testa da giorni senza venirne a capo
Tabella:
IdBadge (numerico)
DataOra       (Data /Ora)
TipoMovimento (numerico) ' può essere 0 o 1
Records
1; 25/01/2005 07:30:00;0
1; 25/01/2005 12:30:00;1
1; 26/01/2005 07:37:00;0
1; 26/01/2005 12:58:00;1
Come ottenere questo output?:
Badge    Giorno         OraIn    OraOut   TOTALE (in minuti)
-------------------------------------------------- 
1          25/01/05        07.30    12.30     300
1          26/01/05        07.37    12.07     270
Quello che non so tradurre in SQL è:
1) selezionami tutti i record identificati dal BADGE n° x del giorno
XX/XX/XXXX
2) estraimi l'ora relativa al TipoMovimento 0 e segnamela come Ora di
Entrata
3) estraimi l'ora relativa al TipoMovimento 1 e segnamela come Ora di Uscita
So che non è così difficile, ma non mi viene. Il massimo che sono riuscito a
fare è:
SELECT DateValue([GiornoOraStamp]) AS Giorno,
    (SELECT TimeValue([GiornoOraStamp]) AS Uscita
    FROM Movimenti
    WHERE (((Movimenti.IdTessera)=1)
    AND ((DateValue([Movimenti].[GiornoOraStamp]))=[param])
    AND ((Movimenti.IDCausale)=1))) AS OrarioUscita,
    (SELECT TimeValue([GiornoOraStamp]) AS Ingresso
    FROM Movimenti
    WHERE (((Movimenti.IdTessera)=1)
    AND ((DateValue([Movimenti].[GiornoOraStamp]))=[param])
    AND ((Movimenti.IDCausale)=0))) AS OrarioEntrata
FROM Movimenti;
Questa query mi estrae due record uguali per giorno.
Il problema sarebbe risolvibile sparando tutto in una nuova tabella
con chiave primaria tutto il rigo in modo da non importare
automaticamente il secondo campo.
Rimane il fatto che debba passarli il parametro data (anche questo
risolvibile creando un ciclo)
Ma se ho una tabella con più ingressi/uscite in un giorno la subquery non mi
viene eseguita.
Ci sono soluzioni o suggerimenti?
Grazie a tutti
Ciao
Marco

 Ci sono soluzioni o suggerimenti?
Ciao Marco,
prova così
-----------------------
SELECT IdBadge,
       Giorno,
       Format(OI,"hh:mm") AS Orain,
       Format(OU,"hh:mm") AS OraOut,
       Datediff("n",OraIn,OraOut) AS Totale
FROM   (SELECT IdBadge,
               Cdate(Fix(DataOra)) AS Giorno,
               DataOra AS OI,
               (Select TOP 1 DataOra
                FROM   Movimenti M2
                WHERE M1.IdBadge=M2.IdBadge
                  AND Cdate(Fix(M1.DataOra)) =Cdate(Fix(M2.DataOra))
                  AND M2.DataOra>M1.DataOra
                ORDER BY M2.DataOra) AS OU
         FROM Movimenti M1
         WHERE TipoMovimento=0) AS Tx
ORDER BY IdBadge,
         Giorno,OI
-------------------------
con questi dati
--------------
1;25/01/2005 7.30.00;0
1;25/01/2005 12.30.00;1
1;25/01/2005 14.00.00;0
1;25/01/2005 18.30.00;1
1;26/01/2005 7.37.00;0
1;26/01/2005 12.58.00;1
1;26/01/2005 13.50.00;0
1;26/01/2005 18.15.00;1
---------------
si ottiene questo risultato
---------------
IdBadge Giorno Orain OraOut Totale
1;25/01/2005; 07.30; 12.30; 300
1;25/01/2005; 14.00; 18.30; 270
1;26/01/2005; 07.37; 12.58; 321
1;26/01/2005; 13.50; 18.15; 265
-----------------
Ciao Giorgio

 news:421a2c3a$0$32339$5fc...@news.tiscali.it...
[CUT]
oops
per sicurezza inserisci questa riga nella Where della subquery che calcola
l'ora di uscita
---------
AND M2.TipoMovimento=1
---------
in pratica la Where interna diventa
-------------
WHERE M1.IdBadge=M2.IdBadge
  AND Cdate(Fix(M1.DataOra)) =Cdate(Fix(M2.DataOra))
  AND M2.DataOra>M1.DataOra
  AND M2.TipoMovimento=1
-------------
Ciao Giorgio

 > news:421a2c3a$0$32339$5fc...@news.tiscali.it...
se vuoi aggiungere un controllo sull'eventuale mancanza della timbratura di
uscita .......
--------------------
SELECT IdBadge,
       Giorno,
       Format(OI,"hh:mm") AS Orain,
       Format(OU,"hh:mm") AS OraOut,
       Datediff("n",OraIn,OraOut) AS Totale
FROM   (SELECT IdBadge,
               Cdate(Fix(DataOra)) AS Giorno,
               DataOra AS OI,
               (SELECT TOP 1 DataOra
                FROM Movimenti M2
                Where M1.IdBadge=M2.IdBadge
                  AND Cdate(Fix(M1.DataOra)) =Cdate(Fix(M2.DataOra))
                  AND M2.DataOra>M1.DataOra
                  AND M2.TipoMovimento=1
                ORDER BY M2.DataOra)  +
                IIF ((SELECT Top 1 TipoMovimento
                      FROM Movimenti AS M3
                      WHERE M1.IdBadge=M3.IdBadge
                        AND M3.DataOra>M1.DataOra
                      ORDER BY M3.DataOra)=0,NULL,0) AS OU
         FROM Movimenti M1
         WHERE TipoMovimento=0)  AS Tx
ORDER BY IdBadge,
         Giorno,OI
---------------------------
per aiutarti a leggere la query copiala nel notepad e visualizzala con un
carattere a passo fisso.
Ciao Giorgio

> [CUT]
-----------------------
SELECT IdBadge,
Giorno,
Format(OI,"hh:mm") AS Orain,
Format(OU,"hh:mm") AS OraOut,
Datediff("n",OraIn,OraOut) AS Totale
FROM (SELECT IdBadge,
Cdate(Fix(DataOra)) AS Giorno,
DataOra AS OI,
(Select TOP 1 DataOra
FROM Movimenti M2
WHERE M1.IdBadge=M2.IdBadge
AND Cdate(Fix(M1.DataOra)) =Cdate(Fix(M2.DataOra))
AND M2.DataOra>M1.DataOra
ORDER BY M2.DataOra) AS OU
FROM Movimenti M1
WHERE TipoMovimento=0
AND M2.TipoMovimento=1
) AS Tx
ORDER BY IdBadge,
Giorno,OI
-------------------------
Ciao Giorgio e grazie della risposta.
Ma visto che ci sei... :-)
La tua soluzione va benissimo ed è quello che mi serve per access.
Se volessi utilizzare MSDE come dovrei modificarla? Ho visto che Fix e Cdate
non esistono. Ps Perché FIX? per eliminare i secondi?
Ho provato a modificarle usando CONVERT ma mi continua a dare un errore di
"INVALID NAME COLUMN" che non riesco a risolvere.
Quello che però vorrei capire è come sei arrivato alla soluzione, ovvero da
un lato come hai impostato il ragionamento e dall'altro come tecnicamente lo
hai messo in pratica.
Cioè semplificando se ho un magazzino in cui entra l'articolo X il giorno
10/01 e ne esce il giorno 12/01 come faccio a visualizzare le due date sullo
stesso record?
Ciao e Grazie
Marco


Ciao Marco,
per prima cosa, l'uso di Cdate e del Fix è un mio vecchio e obsoleto metodo
di trattare le date come numeri, avrei dovuto (come te) usare il DateValue
ma a volte il codice esce dalle dita in modo automatico :)
Adesso ti mostro il metodo che uso per risolvere le query, è molto semplice,
basta analizzare un problema alla volta.
Visto che vuoi utilizzare MSDE userò il linquaggio T-SQL, non cambia molto.
(ps. Spiegare le cose non è il mio forte, spero di non crearti confusione )
1^ Problema : Selezionare le righe con solo la data e l'ora di ingresso
(questa è facile)
--------------------
SELECT IdBadge,
       Data=Convert(varchar,Dataora,112) ,
       OraIn=Convert(char(5),Dataora,108)
FROM   Movimenti M1
WHERE  TipoMovimento=0
ORDER BY DataOra
--------------------
( Per il momento la data la tratto con il formato yyyymmdd)
2^ Problema aggiungere l'ora di uscita prendendo la prima sucessiva alla
data in ingresso.3
------------------
SELECT TOP 1 OraOut=Convert(char(5),Dataora,108)
FROM Movimenti M2
WHERE M2.TipoMovimento=1
    AND M2.IdBadge=M1.IdBadge
   AND Convert(varchar,M2.Dataora,112) =Convert(varchar,M1.Dataora,112)
   AND M2.Dataora>M1.DataOra
ORDER BY M2.DataOra
-----------------
andiamo a unire la subquery appena scritta nella query principale
-----------------------
SELECT IdBadge,
       Data=Convert(varchar,Dataora,112) ,
       OraIn=Convert(char(5),Dataora,108),
       OraOut=(SELECT TOP 1 Convert(char(5),Dataora,108)
               FROM Movimenti M2
               WHERE M2.TipoMovimento=1
                 AND M2.IdBadge=M1.IdBadge
                 AND Convert(varchar,M2.Dataora,112)
=Convert(varchar,M1.Dataora,112)
                 AND M2.Dataora>M1.DataOra
               ORDER BY M2.DataOra)
FROM   Movimenti M1
WHERE  TipoMovimento=0
ORDER BY DataOra
-------------------------
adesso abbiamo tutto, la data, l'ora in ingresso e l'ora di uscita, manca il
calcolo della differenza in minuti tra uscita e ingresso.
Le strade possono essere due, la più semplice è quella di creare una vista
da questa query e poi utilizzarla per il calcolo dei minuti.
--------------
SELECT IdBadge,
       Data=Convert(datetime,Data),
       OraIn,
       OraOut,
       Datediff(n,OraIn,OraOut) AS Totale
FROM **NomeVista**
ORDER BY IdBadge,Data,OraIn
--------------
La seconda strada è quella di usare la nostra query come una tabella
derivata
es
-----------------------------------
SELECT IdBadge,
       Data=Convert(datetime,Data),
       OraIn,
       OraOut,
       Datediff(n,OraIn,OraOut) AS Totale
FROM ( SELECT IdBadge,
              Data=Convert(varchar,Dataora,112) ,
              OraIn=Convert(char(5),Dataora,108),
              OraOut=(SELECT TOP 1 Convert(char(5),Dataora,108)
                      FROM Movimenti M2
                      WHERE M2.TipoMovimento=1
                        AND M2.IdBadge=M1.IdBadge
                        AND Convert(varchar,M2.Dataora,112)
=Convert(varchar,M1.Dataora,112)
                        AND M2.Dataora>M1.DataOra
                      ORDER BY M2.DataOra)
         FROM   Movimenti M1
         WHERE  TipoMovimento=0) Tx
ORDER BY IdBadge,Data,OraIn
-------------------------------------
(manca il controllo sull'eventuale mancanza della timbratura di uscita che
ho mostrato nella query per Access)
Per quanto riguarda il trattamento delle date ti rimando a questo articolo
del buon Lorenzo Benaglia.
http://italy.mvps.org/MVPs/lbenaglia/LorenzoBenaglia-Tip-FormatoDate.htm
e alla funzione Convert di Sql Server
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp
Ciao Giorgio

Aggiornamento:
La query suggerita da Giorgio va benissimo e stavo scrivendo la procedura
per utilizzarla.
SELECT IdBadge,
       Data=Convert(datetime,Data),
       OraIn,
       OraOut,
       Datediff(n,OraIn,OraOut) AS Totale
FROM ( SELECT IdBadge,
              Data=Convert(varchar,Dataora,112) ,
              OraIn=Convert(char(5),Dataora,108),
              OraOut=(SELECT TOP 1 Convert(char(5),Dataora,108)
                      FROM Movimenti M2
                      WHERE M2.TipoMovimento=1
                        AND M2.IdBadge=M1.IdBadge
                        AND Convert(varchar,M2.Dataora,112)
=Convert(varchar,M1.Dataora,112)
                        AND M2.Dataora>M1.DataOra
                      ORDER BY M2.DataOra)
         FROM   Movimenti M1
         WHERE  TipoMovimento=0) Tx
ORDER BY IdBadge,Data,OraIn
Ho aggiunto la condizione AND (MONTH(Data) = @Mese)
e mi estrae correttamente i record relativi al mese (es. gennaio):
1 01/01/2000 08:55 13:30 275
1 11/01/2005 08:55 13:30 275
Se però voglio aggiungerci l'altra condizione: AND (ANNO(Data) = @Anno)
non mi estrae nulla!
La cosa strana è che se nella query visualizzo il campo ANNO(Data) me lo
riporta correttamente nel formato YYYY!
Sbaglio io ad inserire il parametro anno o cosa?
Ma non c'p un modo per inserire un parametro solo ANNO/MESE? Ho spulciato la
CONVERT sui BOL ma non l'ho trovato
Grazie
Marco

 CONVERT sui BOL ma non l'ho trovato
in questo caso il campo data č di tipo testo quindi potresti semplicemente
passare l'anno e il mese in un varchar(6)
--------------
....  ) Tx
WHERE Left(Data,6)='200501'
ORDER BY IdBadge,Data,OraIn
----------------
in altro caso č meglio passare una data e confrontarne il mese e l'anno
-------------
....  ) Tx
WHERE MONTH(Data)=MONTH(@DataRif)
  AND YEAR(Data)=YEAR(@DataRif)
ORDER BY IdBadge,Data,OraIn
-------------
Ciao Giorgio

 news:421b8aac$0$6317$5fc...@news.tiscali.it...
in ogni caso è meglio che il filtro sulla data venga fatto nella tabella
derivata
--------------
...........
         FROM   Movimenti M1
         WHERE  TipoMovimento=0
           AND  MONTH(DataOra)=MONTH(@DataRif)
           AND YEAR(DataOra)=YEAR(@DataRif)
         ) Tx
ORDER BY IdBadge,Data,OraIn
--------------
in questo caso filtriamo subito le righe alla fonte con notevole guadagno di
prestazioni.
Ciao Giorgio