Newsgroup: it.comp.appl.access
Titolo: Query con date e prezzi frazionati

Salve a tutti!
 
Mi trovo da diversi giorni alle prese con un enigma per una query che vorrei realizzare per delle statistiche.
 
Ho un database con una tabella in cui elenco le prenotazioni di un albergo.
Tra i campi ho la data del check-in, la data del check-out e il prezzo totale del soggiorno.
 
Sto cercando di estrapolare delle statistiche mensili con totali parziali degli incassi.
 
In pratica il risultato dei totali divisi per mese deve includere:
 
   prezzo_soggiorno / (data_check_in - data_check_out) * giorni_del_mese_selezionato
 
Il problema è appunto che le prenotazioni sono spesso a cavallo di due o più mesi.
 
Grazie fin d'ora a qualsiasi aiuto vogliate darmi!
 
Danielle

 Danielle
 
spiega meglio
 
-- 
------------
Pablitomf
Napoli sempre nel "Q"uore!
"mammasantissima" si nasce, non si diventa.
 
TheSpaceCowboy : 
Britos deve recuperare per arrivare in forma al prossimo
infortunio - 24/10/12
 

> spiega meglio
 
Pablito,
 
Ogni record di prenotazione ha una campo con data di inizio (check-in) e uno con data di fine (check-out), inoltre ha un campo con il prezzo totale.
 
L'idea è creare una query che mi dia un risultato del genere
 
Gennaio 2011  TOT:1000
Febbraio 2011 TOT:1200
Marzo 2011    TOT:800
...           ...
Dicembre 2012 TOT:2300
Gennaio 2013  TOT:1900
 
Il problema è che alcuni record (prenotazioni) hanno una data di inizio in un mese e la data di fine in un altro (ES: check-in 21/11/2012; check-out 2/1/2013). In questi casi, il prezzo totale va diviso tra i giorni dei vari mesi (ES: prezzo totale €1200, Giorni di Dicembre 2012: 10; Giorni di Gennaio 2013: 2; Ergo ---> asssegnare a Dicembre 2012: €1000 e a Gennaio 2013: €200; questi risultati si devono sommare raggruppandoli per mese e anno)
 
Spero di essere stata più chiara.
 
Grazie!
 
Danielle

> Spero di essere stata piï¿œ chiara.
 
mmmmm... un po' caotica la cosa... fatta a braccio (credo si possa migliorare)
 
Mi sono immaginato una tabella che ho chiamato "tbl_check_in_out"
con i campi:
1) id (chiave contatore)
2) check-in (data)
3) check-out (data)
4) prezzo (valuta)
e su questa ho basato tutto il resto.
 
N.B.
Ho dato per scontato che le prenotazioni non superassero il mese tra entrata
e uscita (per prenotazioni superiori al mese questo sistema non funziona) -
chi e' che fa vacanze in albergo di oltre 1 mese? :D - 
 
--------------- 
 
Crea una query e chiamala "Query1" e incolla quanto sotto:
 
SELECT 
tbl_check_in_out.id
, tbl_check_in_out.[check-in],
IIf(Format([check-out],"yymm")<=Format([check-in],"yymm"),"",DateAdd("d",-1,CVDate("01/"
& Format(DateAdd("m",1,[check-in]),"mm/yy")))) AS [check-out_parz],
IIf([check-out_parz]<>"",DateDiff("d",[check-in],[check-out_parz])+1,"") AS
gg1, IIf([check-out_parz]<>"",[PrezzoGg]*[gg1],"") AS pr1,
IIf([check-out_parz]<>"",IIf([check-out_parz],DateAdd("d",1,[check-out_parz]),""),"")
AS [check-in_parz], tbl_check_in_out.[check-out],
IIf([check-out_parz]<>"",DateDiff("d",[check-in_parz],[check-out])+1,"") AS
gg2, IIf([check-out_parz]<>"",[PrezzoGg]*[gg2],"") AS pr2,
DateDiff("d",[check-in],[check-out])+1 AS gg, tbl_check_in_out.prezzo,
Round([prezzo]/[gg],2) AS PrezzoGg
FROM tbl_check_in_out;
 
--------------- 
 
Crea una query di unione per separare i risultati e chiamala "Query2" e
incolla questo:
 
SELECT Query1.id, Query1.[check-in], Query1.[check-out_parz], Query1.gg1,
Query1.pr1
FROM Query1
WHERE (((Query1.[check-out_parz])<>""))
UNION SELECT Query1.id, Query1.[check-in_parz], Query1.[check-out],
Query1.gg2, Query1.pr2
FROM Query1
WHERE (((Query1.[check-in_parz])<>""))
UNION SELECT Query1.id, Query1.[check-in], Query1.[check-out], Query1.gg,
Query1.prezzo
FROM Query1
WHERE (((Query1.[check-out_parz])=""));
 
--------------- 
 
Poi crei l'ultima query (che ï¿œ poi il risultato che ti aspetti), chiamala
"Query3" e incolla il codice sotto:
 
SELECT Format([check-in],"mmmm yy") AS Periodo, Query2.pr1
FROM Query2
ORDER BY Format([check-in],"yymm");
 
--------------- 
 
Fammi sapere.


> Fammi sapere.
 
Grazie Pablito, l'ho provato e funziona... ma ahimè alcune prenotazioni sono superori a un mese! Dannati turisti!!! ;-)
Il risultato è molto vicino a quello reale ma ancora non ci siamo... forse pensavo che si potrebbe provare un approccio diverso mediante una query di creazione tabella che per ogni giorno dell'anno (a partire dalla data del primo check-in fino ad oggi) dica quanto è l'incasso giornaliero.
 
In seguito sarebbe possibile tramite una seconda query raggruppare per mese i risultati e sommare i prezzi giornalieri.
 
Ti sembra logico e fattibile? Fammi sapere cosa ne pensi...
 
Grazie ancora,
 
Danielle
 
PS: la tabella si chiama reservations e i campi interessati sono ID_reservation (contatore), checkin, checkout, price

<Il problema č appunto che le prenotazioni sono spesso a cavallo <di due o
piů mesi.
 
 
qui c'č la soluzione (in inglese, per sql server,ma adattabile facilmente ad
access ),bisogna creare una tabella di appoggio con le date di fine mese.
 
http://www.sqlservercentral.com/Forums/Topic589668-338-1.aspx
 
 
 
 

 
http://www.sqlservercentral.com/Forums/Topic589668-338-1.aspx
 
 
 
Funziona alla grande,spezza mese per mese anche  periodi a cavallo d'anno 
oppure pi� lunghi di 12 mesi .
 
io ho usato il seguente codice generico,basta copiarlo su SQL studio ( usa 
la nuova comoda sintassi sql 2008,esempio l'istruzione SELECT CASE dentro 
una query,che dovresti poter sostituire con IIF in access):
 
if exists (select * from tempdb..sysobjects where type = 'U' and name like 
'#Tally%')
 
drop table #Tally
 
Create table #Tally
 
(Numbers tinyint)
 
-- fill the tally-table with numeric range
 
INSERT INTO #Tally
 
SELECT 0 UNION ALL
 
SELECT 1 UNION ALL
 
SELECT 2 UNION ALL
 
SELECT 3 UNION ALL
 
SELECT 4 UNION ALL
 
SELECT 5 UNION ALL
 
SELECT 6 UNION ALL
 
SELECT 7 UNION ALL
 
SELECT 8 UNION ALL
 
SELECT 9 UNION ALL
 
SELECT 10 UNION ALL
 
SELECT 11 UNION ALL
 
SELECT 12 UNION ALL
 
SELECT 13 UNION ALL
 
SELECT 14
 
 
 
 
 
if exists (select * from tempdb..sysobjects where type = 'U' and name like 
'#DatePeriod%')
 
drop table #DatePeriod
 
Create table #DatePeriod
 
(StartDate DateTime,
 
EndDate DateTime,
 
Units int)
 
-- create the sample data
 
INSERT INTO #DatePeriod
 
SELECT '20080308', '20090416', 3 UNION ALL
 
SELECT '20080417', '20080430', 5 UNION ALL
 
SELECT '20080501', '20080629', 1 UNION ALL
 
SELECT '20080630', '20080714', 4 UNION ALL
 
SELECT '20080715', '20080902', 1 UNION ALL
 
SELECT '20080903', '20080930', 8 UNION ALL
 
SELECT '20081001', '20081022', 3
 
 
-- select and calculate the periods
 
SELECT
 
StartDate AS OriginalStartDate
 
, EndDate AS OriginalEndDate
 
, CASE WHEN (DateAdd(Month, DateDiff(month, 0, StartDate) + Numbers ,0)) <= 
StartDate
 
THEN StartDate
 
ELSE DateAdd(Month, DateDiff(month, 0, StartDate) + Numbers ,0)
 
END AS PeriodStart
 
, CASE WHEN (DateAdd(Day, -1, DateAdd(Month, DateDiff(month, 0, StartDate) + 
Numbers + 1 ,0))) > EndDate
 
THEN EndDate
 
ELSE DateAdd(Day, -1, DateAdd(Month, DateDiff(month, 0, StartDate) + Numbers 
+ 1 ,0))
 
END AS PeriodEnd
 
, Units
 
FROM
 
#DatePeriod, #Tally
 
WHERE
 
datediff(m, StartDate, EndDate) >= Numbers
 
ORDER BY StartDate, Numbers
 
 
DROP TABLE #DatePeriod
 
DROP TABLE #Tally
 
 

Grazie mille Vittorio!
Sembra promettente! Lo proverò e ti farò sapere!
Danielle
 

 PS: la tabella si chiama reservations e i campi interessati sono
ID_reservation (contatore), checkin, checkout, price
 
certo, si puo' fare...
 
la tabella di appoggio l'ho chiamata "reservation_day" e si compone di:
1) tbl_id (chiave contatore)
2) id (numerico)
3) gg (date)
4) price (valuta 10 cifre decimali)
 
 
questo e' il codice:
 
    Dim SQLstring As String
 
    DoCmd.SetWarnings False
        SQLstring = "Delete * from reservation_day"
        DoCmd.RunSQL SQLstring
    DoCmd.SetWarnings True
 
 
    Dim DateTemp As Date
 
    Dim db As Database
    Dim rs As DAO.Recordset
 
    Set db = CurrentDb
    Set rs = db.OpenRecordset("reservations")
 
        With rs
            .MoveFirst
 
            While Not rs.EOF
                 Dim GiorniPrenotazione As Integer
                 GiorniPrenotazione = DateDiff("d", !checkin, !checkout) + 1
 
                 Dim PrezzoGiornaliero As Double
                 PrezzoGiornaliero = !price / GiorniPrenotazione
 
                 For DateTemp = !checkin To !checkout
                    DoCmd.SetWarnings False
                        SQLstring = ""
                        SQLstring = SQLstring & "INSERT INTO reservation_day (
id, gg, price ) "
                        SQLstring = SQLstring & "SELECT " & !ID_reservation & "
AS Espr1, "
                        SQLstring = SQLstring & "'" & Format(CVDate(DateTemp),
"dd/mm/yy") & "' AS Espr2, "
                        SQLstring = SQLstring & PrezzoGiornaliero & " AS Espr3
FROM reservations "
                        SQLstring = SQLstring & "GROUP BY " & !ID_reservation &
", '" & Format(CVDate(DateTemp), "dd/mm/yy") & "', " & PrezzoGiornaliero & ";"
 
                        DoCmd.RunSQL SQLstring
                    DoCmd.SetWarnings True
 
                 Next DateTemp
 
                .MoveNext
            Wend
 
        End With
 
    Set rs = Nothing
    Set db = Nothing
 
Questo l'SQL di risultato:
SELECT Format([gg],"mmmm yyyy") AS Data, Sum(reservation_day.price) AS Importo
FROM reservation_day
GROUP BY Format([gg],"mmmm yyyy"), Format([gg],"yymm")
ORDER BY Format([gg],"yymm") DESC;
 
prova e fammi sapere
