mercoledì, febbraio 06, 2008

SUM datetime - Sommare date - SQL

- post<li> - Permalink

Tra le tutte le mansioni che un informatico deve saper fare c'è sicuramente la programmazione. Questa non l'ho mai dimenticata (ho fatto la tesi sui linguaggi di programmazione per il Web) anche se adesso non la pratico tanto quanto una volta.

Mi è capitato comunque di riprendere in mano un vecchio lavoro in ASP e MSSQL... e follia giorni e giorni di debug su di quelle minc...te .

Ma andiamo per ordine.

Problema: Sommare degli orari memorizzate in formato DATETIME relative a delle ore lavorate. Il formato DATETIME non può entrare in funzioni di aggregazione tipo SUM.

Il problema non è tra i più nuovi e comunque facendo una ricerca su Google per le parole SUM DATETIME appare tra i diversi risultati la strategia da usare. Molto semplice a dire la verità (una volta conosciuta - come sempre):

  1. Si trova la data 0 (zero);
  2. Si calcola la distanza dalla Data 0 alla Data rappresentate le ore lavorate in minuti;
  3. I minuti sono ora un numero 'puro' che può essere sommato;
  4. Il risultato della somma si divide per 60 (/) e si trova il totale in ore;
  5. Lo stesso risultato di prima si modula 60 ( % ) e si trova il resto in minuti;
In Transact-SQL:
  1. CAST('00:00' AS DATETIME);
  2. DATEDIFF(mi, OraZero, Ore);
  3. SUM(MinutiCalcolati);
  4. SommaMinuti / 60 AS OreTotali;
  5. SommaMinuti % 60 AS MinutiTotali;
Tutto insieme diventa:

SELECT SUM(DATEDIFF(n, CAST('00:00' AS DATETIME), Ore)) / 60 AS OreTotali, SUM(DATEDIFF(n, CAST('00:00' AS DATETIME), Ore)) % 60 AS MinutiTotali, idOrdine
FROM OreLavorate
GROUP BY idOrdine


facile no? NO!

Perchè cosi facendo ottenete un errore medio di circa 48 ore!!!! Non scherzo!
QUARANTOTTO ore non sono poche!
Sapete perchè? Perchè dopo diverse ore di debug e reverse engineering ho scoperto una cosa interessante ovvero che lo zero in casa MS è relativo!

Ovvero per alcune funzioni tipo CAST la data zero è il 01/01/1900 e fin qui niente di male, ma lo zero per le funzioni di memorizzazione è diversa!

Quale sarà questa data? Mah se è diversa dal precedente sarà una vicina tipo il 31/12/1899.... NO!

E' il 30 DICEMBRE 1899


Perchè il 30 sinceramente non lo so comunque se volete una query precisa dovete modificare la precedente query nel seguente modo:

SELECT SUM(DATEDIFF(n, CAST('Dicembre 30, 1899 00:00' AS DATETIME), Ore)) / 60 AS OreTotali, SUM(DATEDIFF(n, CAST('Dicembre 30, 1899 00:00' AS DATETIME), Ore)) % 60 AS MinutiTotali, idOrdine
FROM OreLavorate
GROUP BY idOrdine

Capite ora perchè preferisco programmare OPEN???

Byez

La libertà comprende il diritto di aver torto.

3 commenti:

Articoli correlati divisi per etichetta



Widget by Hoctro