booking query 2

G

Ghena

Hi Everybody,

I have 2 tables called Listino1 and prenotazioni


my data in prenotazioni right now are:


ID_prenotazioni ID_hotel id_room tipo_room nome_room pax sell_q dal al
1 24 Roma: prova 2 1 dbl standard 2 1 20/06/2005 25/06/2005
2 24 Roma: prova 2 1 dbl standard 2 1 21/06/2005 25/06/2005
3 24 Roma: prova 2 1 dbl standard 2 1 24/06/2005 26/06/2005
5 24 Roma: prova 2 1 dbl standard 2 1 24/06/2005 26/06/2005


In the listino1 data are:

ID_listino1 ID_hotel id_room tipo_room dal al
1 22 Palermo: la pace 1 dbl 20/05/2005 01/06/2005
2 22 Palermo: la pace 1 dbl 02/06/2005 30/06/2005
5 22 Palermo: la pace 1 dbl 01/07/2005 30/07/2005
6 22 Palermo: la pace 1 dbl 31/07/2005 31/08/2005
7 23 Palermo: prova 1 3 sgl 20/05/2005 01/06/2005
8 23 Palermo: prova 1 3 sgl 02/06/2005 30/06/2005
9 23 Palermo: prova 1 3 sgl 01/07/2005 30/07/2005
10 23 Palermo: prova 1 3 sgl 31/07/2005 31/08/2005
11 24 Roma: prova 2 5 dbl 20/05/2005 01/06/2005
12 24 Roma: prova 2 5 dbl 02/06/2005 30/06/2005
13 24 Roma: prova 2 5 dbl 01/07/2005 30/07/2005
14 24 Roma: prova 2 5 dbl 31/07/2005 31/08/2005
15 25 Roma: prova 3 7 twin 25/05/2005 01/06/2005
16 25 Roma: prova 3 7 twin 02/06/2005 15/06/2005
17 25 Roma: prova 3 7 twin 16/06/2005 30/06/2005
18 25 Roma: prova 3 7 twin 01/07/2005 31/08/2005
19 26 Roma: prova 4 9 dbl 25/05/2005 01/06/2005
20 26 Roma: prova 4 9 dbl 02/06/2005 15/06/2005
21 26 Roma: prova 4 9 dbl 16/06/2005 30/06/2005
22 26 Roma: prova 4 9 dbl 01/07/2005 31/08/2005
23 27 Roma: prova 5 11 dbl 25/05/2005 01/06/2005
24 27 Roma: prova 5 11 dbl 02/06/2005 15/06/2005
25 27 Roma: prova 5 11 dbl 16/06/2005 30/06/2005
26 27 Roma: prova 5 11 dbl 01/07/2005 31/08/2005


I need to display the sum of prenotazioni.sell_q included in every
single period in listino1 limited by some date parameters

I'm trying with:

SELECT prenotazioni.ID_hotel, nz((SELECT Sum(sell_q) FROM prenotazioni
WHERE (listino1.dal>=#20/06/2005# And listino1.dal<=#01/07/2005#) or
(listino1.al >=#20/06/2005# And listino1.al<=#20/06/2005# and
[prenotazioni]![ID_hotel] = [listino1]![ID_hotel] ) ),0) AS totroom
listino1.dal, listino1.al, listino1.ID_hotel
FROM prenotazioni INNER JOIN listino1 ON prenotazioni.ID_hotel =
listino1.ID_hotel
GROUP BY prenotazioni.ID_hotel, listino1.dal, listino1.al,
listino1.ID_hotel;


I get:


prenotazioni.ID_hotel totroom dal al
24 Roma: prova 2 0 20/05/2005 01/06/2005
24 Roma: prova 2 0 02/06/2005 30/06/2005
24 Roma: prova 2 0 01/07/2005 30/07/2005
24 Roma: prova 2 0 31/07/2005 31/08/2005


But It is wrong becouse the right data should be:

prenotazioni.ID_hotel totroom dal al
24 Roma: prova 2 0 20/05/2005 01/06/2005
24 Roma: prova 2 4 02/06/2005 30/06/2005
24 Roma: prova 2 0 01/07/2005 30/07/2005
24 Roma: prova 2 0 31/07/2005 31/08/2005
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

1. You can't use the # date delimiter on European date formats
(day/month/year), only USA date formats (month/day/year).

2. You have put a WHERE clause in the subquery that should be in the
main query.

3. Use the BETWEEN predicate, it's easier than the >= & <= comparisons.

4. This

(listino1.al >=#20/06/2005# And listino1.al<=#20/06/2005#

is the same as this

listino1.a1 = #20/06/2005#

5. You don't need a Nz() around the subquery because you're using an
INNER JOIN on the main query tables (on records that have the same
ID_hotel value in both tables) and the subquery will only "look" at
records that the main query is examining.


Not sure about this query - untested:

SELECT P.ID_hotel,

(SELECT Sum(sell_q) FROM prenotazioni
WHERE ID_hotel = P.ID_hotel) AS totroom

L.dal, L.al

FROM prenotazioni As P INNER JOIN listino1 As L
ON P.ID_hotel = L.ID_hotel

WHERE L.dal BETWEEN #06/20/2005# And #07/01/2005#
OR L.al = #06/20/2005#

GROUP BY P.ID_hotel, L.dal, L.al

If you didn't want specific dates, the WHERE could also be:

WHERE L.dal BETWEEN P.dal AND P.al
OR L.al BETWEEN P.dal AND P.al

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRDsN+oechKqOuFEgEQJ8DwCgrLi+WAKO7qrjKisHaavGcClo1g8An0ua
kXxidn8IxfdDOqDd13uWimav
=PCj+
-----END PGP SIGNATURE-----

Hi Everybody,

I have 2 tables called Listino1 and prenotazioni


my data in prenotazioni right now are:


ID_prenotazioni ID_hotel id_room tipo_room nome_room pax sell_q dal al
1 24 Roma: prova 2 1 dbl standard 2 1 20/06/2005 25/06/2005
2 24 Roma: prova 2 1 dbl standard 2 1 21/06/2005 25/06/2005
3 24 Roma: prova 2 1 dbl standard 2 1 24/06/2005 26/06/2005
5 24 Roma: prova 2 1 dbl standard 2 1 24/06/2005 26/06/2005


In the listino1 data are:

ID_listino1 ID_hotel id_room tipo_room dal al
1 22 Palermo: la pace 1 dbl 20/05/2005 01/06/2005
2 22 Palermo: la pace 1 dbl 02/06/2005 30/06/2005
5 22 Palermo: la pace 1 dbl 01/07/2005 30/07/2005
6 22 Palermo: la pace 1 dbl 31/07/2005 31/08/2005
7 23 Palermo: prova 1 3 sgl 20/05/2005 01/06/2005
8 23 Palermo: prova 1 3 sgl 02/06/2005 30/06/2005
9 23 Palermo: prova 1 3 sgl 01/07/2005 30/07/2005
10 23 Palermo: prova 1 3 sgl 31/07/2005 31/08/2005
11 24 Roma: prova 2 5 dbl 20/05/2005 01/06/2005
12 24 Roma: prova 2 5 dbl 02/06/2005 30/06/2005
13 24 Roma: prova 2 5 dbl 01/07/2005 30/07/2005
14 24 Roma: prova 2 5 dbl 31/07/2005 31/08/2005
15 25 Roma: prova 3 7 twin 25/05/2005 01/06/2005
16 25 Roma: prova 3 7 twin 02/06/2005 15/06/2005
17 25 Roma: prova 3 7 twin 16/06/2005 30/06/2005
18 25 Roma: prova 3 7 twin 01/07/2005 31/08/2005
19 26 Roma: prova 4 9 dbl 25/05/2005 01/06/2005
20 26 Roma: prova 4 9 dbl 02/06/2005 15/06/2005
21 26 Roma: prova 4 9 dbl 16/06/2005 30/06/2005
22 26 Roma: prova 4 9 dbl 01/07/2005 31/08/2005
23 27 Roma: prova 5 11 dbl 25/05/2005 01/06/2005
24 27 Roma: prova 5 11 dbl 02/06/2005 15/06/2005
25 27 Roma: prova 5 11 dbl 16/06/2005 30/06/2005
26 27 Roma: prova 5 11 dbl 01/07/2005 31/08/2005


I need to display the sum of prenotazioni.sell_q included in every
single period in listino1 limited by some date parameters

I'm trying with:

SELECT prenotazioni.ID_hotel, nz((SELECT Sum(sell_q) FROM prenotazioni
WHERE (listino1.dal>=#20/06/2005# And listino1.dal<=#01/07/2005#) or
(listino1.al >=#20/06/2005# And listino1.al<=#20/06/2005# and
[prenotazioni]![ID_hotel] = [listino1]![ID_hotel] ) ),0) AS totroom
listino1.dal, listino1.al, listino1.ID_hotel
FROM prenotazioni INNER JOIN listino1 ON prenotazioni.ID_hotel =
listino1.ID_hotel
GROUP BY prenotazioni.ID_hotel, listino1.dal, listino1.al,
listino1.ID_hotel;


I get:


prenotazioni.ID_hotel totroom dal al
24 Roma: prova 2 0 20/05/2005 01/06/2005
24 Roma: prova 2 0 02/06/2005 30/06/2005
24 Roma: prova 2 0 01/07/2005 30/07/2005
24 Roma: prova 2 0 31/07/2005 31/08/2005


But It is wrong becouse the right data should be:

prenotazioni.ID_hotel totroom dal al
24 Roma: prova 2 0 20/05/2005 01/06/2005
24 Roma: prova 2 4 02/06/2005 30/06/2005
24 Roma: prova 2 0 01/07/2005 30/07/2005
24 Roma: prova 2 0 31/07/2005 31/08/2005
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

IIF and query booking 7
Advanced problem using auto-number and datediff to find readmissio 1
Between 2 dates 1
I need to help with Excel (beginner) -- year row and month column 2
Adding records to filtered query via linked table 3
Query to display the difference of period for an employee. 1
Taking the sum between two dates 2
append query for dates and hour 4

Top