[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 :muro:

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

:help:

[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.... :help:

grazie mille.

RaouL.

[RaouL_BennetH]

:help:

[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] (http://img221.imageshack.us/i/immaginexfn.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]

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] (http://img221.imageshack.us/i/immaginexfn.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]

Sisi, ho scritto manutenzioni, ma voleva essere assicurazioni

Ok :p

provo subito !!

grazie.

[RaouL_BennetH]

Ho provato e riprovato quasi tutta la notte :muro:

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 :cry:

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

[gugoXX]

Ma i numeri sono corretti?

[RaouL_BennetH]

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]

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 :muro:

gugoXX ancora una volta non so come ringraziarti :ave:

...sta diventando imbarazzante :(