[SQL]Ottenere intervalli mensili

[RaouL_BennetH]

Ciao a tutti

Scusate per il titolo poco chiaro ma stamattina mi sento leggermente rincitrullito...

allora, ho questa query:

SELECT
(a.Importo / a.Durata) as importoMese,
b.RagioneSociale, b.targa
FROM assicurazioni a
JOIN
VeicoliNoleggiati B
ON
a.id_Veicolo = b.id_Veicolo

Questa query funziona bene, nel senso che mi restituisce i dati che mi aspetto.

Considerando che la tabella assicurazioni ha anche due campi di tipo 'Date', che sono:

startData
endData

e che si riferiscono al periodo di copertura dell'assicurazione, vorrei capire se
fosse possibile adeguare la query di sopra in modo che l'espressione:

importo / durata

mi venga riportata per mese, per esempio:

GENNAIO-----FEBBRAIO
190,00______190,00

etc...

grazie mille

RaouL.

[RaouL_BennetH]

mmm... se anche la trovassi non andrebbe bene..

Il mio problema è quindi più complicato

Avendo questa situazione:

Azienda->AssicuraVeicolo->Data = 03/06/2009
Durata polizza mesi = 12
importo = 1.000,00

Azienda->NoleggiaVeicolo->Data = 22/07/2009
supponendo che il termine del noleggio coincida con la data di scadenza dell'assicurazione:
importo per l'assicurazione = (importo / totaleGiorniNoleggio)
-> 1000 / 316 = 3,16

Fin qui.. tutto ok (diciamo)

ora, come faccio tramite sql a fare in modo che la somma di questi giorni mi venga imputata al mese di riferimento?

Cioè, considerando la data di inizio noleggio dell'esempio, dal 22/07 al 31/07 sono 9 giorni, quindi in 'LUGLIO' dovrei ottenere 3,16 * 9 = 28,44

e alla scadenza dovrei ottenere: 'GENNAIO' (2010) 3,16 * 3 = 9,48

[RaouL_BennetH]

sono giunto a questo:

select a.targa,
sum(case month (a.inizioNoleggio) when '1' then (b.importo / datediff(last_day(a.inizioNoleggio), a.inizioNoleggio))  else 0 end )as 'GENNAIO',
sum(case month (a.inizioNoleggio) when '2' then (b.importo / datediff(last_day(a.inizioNoleggio), a.inizioNoleggio))  else 0 end )as 'FEBBRAIO',
sum(case month (a.inizioNoleggio) when '3' then (b.importo / datediff(last_day(a.inizioNoleggio), a.inizioNoleggio))  else 0 end )as 'MARZO',
sum(case month (a.inizioNoleggio) when '4' then (b.importo / datediff(last_day(a.inizioNoleggio), a.inizioNoleggio))  else 0 end )as 'APRILE'
from veicolinoleggiati a
join assicurazione b on a.id = b.id_veicolo
where a.inizioNoleggio between b.startData and b.endData
group by a.targa

Ma ho aperti tanti di quei tab con ste query che non mi ci raccapezzo più

[RaouL_BennetH]

uppettino....

[RaouL_BennetH]

sono ancora in alto mare ma qualche progresso l'ho fatto.

Considerando due tabelle:

Manutenzioni, Assicurazioni

la tabella manutenzioni:

id_veicolo
tipoManutenzione
dataManutenzione
importo
descrizione

//ottengo il risultato desiderato in questo modo:

SELECT targa,
SUM(IF(MONTH(dataManutenzione) = 1), importo, 0)) As 'GENNAIO',
SUM(IF(MONTH(dataManutenzione) = 2), importo, 0)) As 'FEBBRAIO',
//fino a dicembre
SUM(importo) as 'TOTALE'

FROM manutenzioni 
JOIN
autoveicoli 
ON manutenzioni.id_veicolo = autoveicoli.id
GROUP BY targa

mi da la visualizzazione che desidero.

Per la tabella assicurazioni invece ho un bel problema:

id_veicolo
startData
endData
durata
importo

se faccio la stessa query, giustamente ottengo l'importo soltanto in un mese.

E' come se dovessi ciclare all'interno della query ma non ho idea di come fare.

Plz....

grazie mille.

RaouL.

[RaouL_BennetH]

[gugoXX]

Costruisciti la classica dimensione temporale
Ovvero una tabella con PK pari alla data, e con varie colonne relative agli attributi della dimensione temporale.
Che deve essere prepopolata.

ES:
PK_Data Data (oppure varchar con descrittivo YYYY-MM-DD)
DescGiorno varchar(10)
DescMese varchar(6)
DescMeseLungo varchar(20)
DescAnno varchar(4)
DescSettimana varchar(6)

'2009-06-21', '2009-06-21', '2009-06','2009 Giugno', '2009', '2009-28'
(il 28 della 28esima settimana e' sparato a caso, occorre costruire bene e popolare)
Fatta bene una volta, fatta per sempre (o quasi)

Dopodiche', se durata vale esattamente il numero di giorni tra startData ed EndData, farai una cosa simile a

SELECT targa, DescMese, Sum(Importo/durata) ValPerMese
FROM  manutenzioni 
JOIN autoveicoli ON manutenzioni.id_veicolo = autoveicoli.id
JOIN DimTempo ON PK_Data BETWEEN startData AND EndData
GROUP BY targa, DescMese

E otterrai un record per ciascuna targa-mese, con l'importo mediato sui giorni del mese di riferiemento.

[RaouL_BennetH]

Ciao gugoXX e grazie per aver risposto

Il problema sulla tabella delle manutenzioni l'ho risolto con la query:

SELECT targa,
SUM(IF(MONTH(dataManutenzione) = 1), importo, 0)) As 'GENNAIO',
SUM(IF(MONTH(dataManutenzione) = 2), importo, 0)) As 'FEBBRAIO',
//fino a dicembre
SUM(importo) as 'TOTALE'

FROM manutenzioni 
JOIN
autoveicoli 
ON manutenzioni.id_veicolo = autoveicoli.id
GROUP BY targa

E mi funziona bene perchè per le manutenzioni non ho bisogno di dividere l'importo della manutenzione nei mesi, dato che è un importo o più importi relativi ad un mese solo o a più mesi.

Infatti la visualizzazione che ottengo è questa:

[img=http://img221.imageshack.us/img221/1329/immaginexfn.th.jpg]

Il problema lo ho per le assicurazioni, perchè devo dividere l'importo per la durata della polizza e visualizzarlo così come lo visualizzo per le manutenzioni.

grazie mille

RaouL.

[gugoXX]

Originariamente inviato da RaouL_BennetH

Ciao gugoXX e grazie per aver risposto

Il problema sulla tabella delle manutenzioni l'ho risolto con la query:

SELECT targa,
SUM(IF(MONTH(dataManutenzione) = 1), importo, 0)) As 'GENNAIO',
SUM(IF(MONTH(dataManutenzione) = 2), importo, 0)) As 'FEBBRAIO',
//fino a dicembre
SUM(importo) as 'TOTALE'

FROM manutenzioni 
JOIN
autoveicoli 
ON manutenzioni.id_veicolo = autoveicoli.id
GROUP BY targa

E mi funziona bene perchè per le manutenzioni non ho bisogno di dividere l'importo della manutenzione nei mesi, dato che è un importo o più importi relativi ad un mese solo o a più mesi.

Infatti la visualizzazione che ottengo è questa:

[img=http://img221.imageshack.us/img221/1329/immaginexfn.th.jpg]

Il problema lo ho per le assicurazioni, perchè devo dividere l'importo per la durata della polizza e visualizzarlo così come lo visualizzo per le manutenzioni.

grazie mille

RaouL.

Sisi, ho scritto manutenzioni, ma voleva essere assicurazioni

[RaouL_BennetH]

Originariamente inviato da gugoXX

Sisi, ho scritto manutenzioni, ma voleva essere assicurazioni

Ok

provo subito !!

grazie.

[RaouL_BennetH]

Ho provato e riprovato quasi tutta la notte

quello che ottengo è:

1, 'April', 169.000000
1, 'August', 169.000000
1, 'December', 160.666667
1, 'February', 169.000000
1, 'January', 8.333333
1, 'July', 169.000000
1, 'June', 169.000000
1, 'March', 169.000000
1, 'May', 169.000000
1, 'November', 169.000000
1, 'October', 169.000000
1, 'September', 169.000000

Mi rendo conto di essere un idiota, ma non rieso a visualizzarla come per le altre

PS: il numero si riferisce all'id, dalla query ho omesso la targa

[gugoXX]

Ma i numeri sono corretti?

[RaouL_BennetH]

Originariamente inviato da gugoXX

Ma i numeri sono corretti?

Si, sono corretti. Questo veicolo come altri, ha due polizze distinte. La divisione dell'importo totale nei mesi è corretta.

[gugoXX]

Bene.
Allora devi solo fare la trasposta.

SELECT id_veicolo,SUM(CASE WHEN Mese=1 THEN valore ELSE 0 END) Gennaio,
                        ,SUM(CASE WHEN Mese=2 THEN valore  ELSE 0 END) Febbraio,
                        ...
                        ,SUM(CASE WHEN Mese=12 THEN valore  ELSE 0 END) Dicembre
FROM (La query di prima)
GROUP BY id_veicolo

Inutile dire che stai evidentemente piegando l'SQL per avere in output un report.
L'SQL dovrebbe essere usato per estrarre i dati, che dovrebbero essere nel formato che avevi prima, senza le trasposizioni (che peraltro hai gia' fatto anche tu nella prima parte della soluzione)
Il tool per la reportistica dovrebbe essere in grado di fare queste operazioni da solo, dati in ingresso i dati puri, mediante funzionalita' di pivoting o altro.
Questo "piegare l'SQL" ti costera' in seguito tanti mal di testa.

[RaouL_BennetH]

Originariamente inviato da gugoXX

Bene.
Allora devi solo fare la trasposta.

SELECT id_veicolo,SUM(CASE WHEN Mese=1 THEN valore ELSE 0 END) Gennaio,
                        ,SUM(CASE WHEN Mese=2 THEN valore  ELSE 0 END) Febbraio,
                        ...
                        ,SUM(CASE WHEN Mese=12 THEN valore  ELSE 0 END) Dicembre
FROM (La query di prima)
GROUP BY id_veicolo

Inutile dire che stai evidentemente piegando l'SQL per avere in output un report.
L'SQL dovrebbe essere usato per estrarre i dati, che dovrebbero essere nel formato che avevi prima, senza le trasposizioni (che peraltro hai gia' fatto anche tu nella prima parte della soluzione)
Il tool per la reportistica dovrebbe essere in grado di fare queste operazioni da solo, dati in ingresso i dati puri, mediante funzionalita' di pivoting o altro.
Questo "piegare l'SQL" ti costera' in seguito tanti mal di testa.

Noooo!! manco a farlo apposta l'avevo appena trovata :

select targa,
sum(case month(date_hour) when '1' then (importo / durata)  else 0 end )as 'GENNAIO',
sum(case month(date_hour) when '2' then (importo / durata)  else 0 end )as 'FEBBRAIO',
sum(case month(date_hour) when '3' then (importo / durata)  else 0 end )as 'MARZO',
sum(case month(date_hour) when '4' then (importo / durata)  else 0 end )as 'APRILE',
sum(case month(date_hour) when '5' then (importo / durata)  else 0 end )as 'MAGGIO',
sum(case month(date_hour) when '6' then (importo / durata)  else 0 end )as 'GIUGNO',
sum(case month(date_hour) when '7' then (importo / durata)  else 0 end )as 'LUGLIO',
sum(case month(date_hour) when '8' then (importo / durata)  else 0 end )as 'AGOSTO',
sum(case month(date_hour) when '9' then (importo / durata)  else 0 end )as 'SETTEMBRE',
sum(case month(date_hour) when '10' then (importo / durata)  else 0 end )as 'OTTOBRE',
sum(case month(date_hour) when '11' then (importo / durata)  else 0 end )as 'NOVEMBRE',
sum(case month(date_hour) when '12' then (importo / durata)  else 0 end )as 'DICEMBRE',
sum(distinct(importo)) as TOTALE
from assicurazione
join autoveicolo on assicurazione.id_veicolo = autoveicolo.id
join calendar on date_hour between startData and endData
group by targa

Per quanto riguarda il discorso 'report' :

E' proprio quello che devo fare. Queste 'viste' saranno lanciate direttamente dal query browser per poi essere esportate o su csv o altri formati o per essere stampate direttamente.

Aggiungo: nella realtà aziendale in cui mi trovo stai sicuro che se devono trovare quattrini per fare il leasing di una bmw ci riescono.. ma se si tratta di comprare strumenti software o
pagare un professionista .... lasciamo perdere va.. io mi devo arrangiare come posso

gugoXX ancora una volta non so come ringraziarti

...sta diventando imbarazzante