Differenza tra valori sulla stessa colonna

[michele.b26]

Ciao a tutti, qualcuno sa darmi il codice SQL per calcolare la differenza tra due valori che si trovano sulla stessa colonna?

Mi spiego meglio: ho una tabella con i campi `cliente`, `data_fattura` e voglio sapere quanto tempo è trascorso tra una fattura e l'altra dello stesso cliente, ad esempio:

ID_CLIENTE,  DATA_FATTURA
1            01-11-2009
1            07-11-2009
1            15-11-2009
2            15-10-2009
2            02-11-2009

e cerco il risultato:

ID_CLIENTE,  DATA_FATTURA, GIORNI
1            01-11-2009     NULL
1            07-11-2009     6
1            15-11-2009     8
2            15-10-2009     NULL
2            02-11-2009     18

Ho provato anche a cercare in internet, ma non trovo la soluzione.

Grazie per ogni eventuale risposta.

 

[borgo italia]

ciao
se si inserissero i valori di data fattura già in timestamp sarebbe tutto molto piu semplice
in time stamp l'ordinamento per data diventa sempre giusto
io ti do la soluzione per time stamp

$query1=mysql_query("SELECT* FROM clienti ORDER BY id_cliente, data_fattura")
while($riga1=misql_fetch_array($query1)){
$id_c1=$riga['id_cliente'];
$data_f1=$riga['data_fattura'];
echo "$id_cl1 $data_f1<br>";
$query2=mysql_query("SELECT* FROM clienti WHERE data_fattura > $data_f1 ORDER BY id_cliente, data_fattura")
while($riga2=mysql_fetch_array($query2)){
$id_c2=$riga['id_cliente'];
$data_f2=$riga['data_fattura'];
/*qui calcoli da differnza
$gg_dif=($data_f2-$data_f1)/(24*60*60);
con gli opportuni aggiornamenti
*/
echo "$id_cl $data_f $gg_diff<br>";
}//fine while interno
}//fine while esterno

dovrebbe risultare così

ID_CLIENTE, DATA_FATTURA
1 01-11-2009
1 07-11-2009 6
1 15-11-2009 8
2 15-10-2009
2 02-11-2009 17

comunque USA il timestamp per le date le trasformi poi a video come ti pare, anze se fai due fatture
allo stesso cliente nello stesso giorno le fatture hanno tempi diversi in quanto il timestamp conta i secondi

 

[alessandro1997]

borgo italia puoi sempre fare un explode del carattere - nel campo della data e poi passare i valori come parametri alla funzione mktime: http://www.php.net/manual/en/function.mktime.php

 

[borgo italia]

ciao
il problema non è quello, ma se nella tabella le date sono inserite gg-mm-aaaa succede che la data 01-12-2009 viene prima di 12-10-2009 dando orderby, mentre se inserisco la data in timestamp anche sel ho due date dellestesso giorno, possono essere della stessa ora e minuto, ma è estremamete difficile che siano dello stesso secondo (casomai uso microtime) e cosi sono sicuro che order by funzi esattamente come dovrebbe

 

[michele.b26]

Ehm, intendevo una soluzioni in SQL, scusate l'imprecisione, io ho scritto questo codice:

select t1.id_cliente, t1.data_fattura, DATEDIFF(t2.data_fattura, t1.data_fattura) as differenza_giorni
from tabella as t1, tabella as t2
where t2.data_fattura > t1.data_fattura
and t1.id_cliente = t2.id_cliente
group by t1.data_fattura
order by t1.id_cliente, t1.data_fattura

Però non ne sono convinto al 100% e non mi restituisce i 'null'.

 

[borgo italia]

ciao
non sono molto esperto in mysql, per quello ti ho suggerito la via "software".
forse non sfrutterò al massimo le potenzialità di mysql, però è la strada che preferisco tenendo le query le più semplici e lineari possibile.
quindi non so dirti perche la tua quary non funzi. spera che qualcuno più esperto ti sappia rispondere

 

[michele.b26]

Apprezzo anche la tua linea, ma per questo problema devo usare proprio il SQL, grazie lo stesso per l'aiuto. Altri suggerimenti?

 

[michele.b26]

Un'idea potrebbe essere quella di affiancare al campo data un'altra colonna con le date shiftate di uno, cioè creare una coppia con data e la data immediatamente successiva, per poi calcolarne la differenza, ma come si fa?

ID_CLIENTE,  DATA_FATTURA,  DATA_FATTURA_2
1            01-11-2009     NULL
1            07-11-2009     01-11-2009
1            15-11-2009     07-11-2009
2            15-10-2009     NULL
2            02-11-2009     15-10-2009

 

[borgo italia]

ciao
intanto non capisco perche tu voglia appesantire il db effetuando query complicate o aggiungendo colonne inutili,
ma ogniuno ha un suo stile.
comunque volevo segnalarti che il campo DATA_FATTURA, per fare quello che dici, deve essere ordinato in
ordine crescente di data.
ora se hai impostato il campo tipo DATE (estratto da manuale mysql)

Una colonna DATE può contenere date da '1000-01-01' (1° gennaio 1000)
a '9999-12-31' (31 dicembre 9999).
MySQL visualizza le date nel formato che vi abbiamo appena mostrato,
ma vi consente di inserirle sotto forma di stringhe o numeri.

quindi o le date sono inserite in tale formato aaaa mm gg ho il risultato è almeno assurdo
se inserisce le date nel formato europeo gg mm aaaa diffdate calcola la differenza tra le date
considerando quello che tu credi gg come anno, per il mese ci azzecca,
per giorno quello che credi essere l'anno

oppure se le hai inserite come stringa (varchar) ricordati che nell'ordinamento che fai per data
(im dodo da evere per prima la piu vecchia) ti può capitare che se la più vecchia è il 22-01-2009 ed una successiva
15-03-2009, nell'ordinamento mette per prima quella del 15-03 poi quella del 22-01 (errato sia come
ordinamento che come base del calcolo della differenza, almeno una data diviene negativa)

come già detto, sempre secondo me, il metodo migliore è inserire le date nel formato timestamp (impostando il camdo della data
come TIMESTAM o, secondo me meglio, BLOBINT(29). (timestamp/unix secondi trascorsi dal 1°gennaio 1970 ore 00)
in questo modo:
1. sei sicuro che l'ordinamento sia sempre e comunque esatto
2. la differenza è più facilmente calcolabile (anche indicando gg:h:min:sec e decimale di secondo)
3. con date(pattern, valore_timestamp) puoi dopo visualizzare la data come vuoi (puoi scegliere tra una ventina di formati
con i separatori che preferisci)

 

[alessandro1997]

Più che altro i formati che puoi utilizzare con la funzione date sono infiniti, a meno che tu non decida di utilizzare uno di quelli già integrati di PHP, che non sapevo neanche esistessero: http://www.php.net/manual/en/class.datetime.php#datetime.constants.types
Comunque concordo con borgo italia nel dire che usare la funzione time è molto più maneggevole e semplice.

 

[michele.b26]

Ho trovato la soluzione, non è mia, me l'ha passata un mio amico.

select t1.id_cliente, t1.data_fattura, t2.data_fattura, DATEDIFF(t2.data_fattura, t1.data_fattura) as giorni_differenza 
from tabella as t1, tabella as t2 
where t1.id_cliente = t2.id_cliente 
and t1.data_fattura < t2.data_fattura 
and not exists( 
	select 1 
	from tabella as t3 
	where t3.id_cliente = t1.id_cliente 
	and t3.data_fattura > t1.data_fattura 
	and t3.data_fattura < t2.data_fattura 
);

così funziona correttamente.

Concordo con voi sul formato della data, le ho scritte in quel modo solo per rendere l'esempio più leggibile.
Concordo anche sul fatto di usare il PHP per l'interrogazione o altri linguaggi, ma per delle indagini sul db, è meglio interrogarlo direttamente in SQL, soprattutto quando si usa Pentaho, così risulta più veloce.

Grazie per l'aiuto e per lo scambio di idee.