Sometimes it takes someone with no knowledge of the details to try a solution.
I have a query called qCambios that uses the query sent in the last post.
That query I named qFreedaysBeforeNextArrival
so the sql for query qCambios is
Code:
SELECT qFreedaysBeforeNextArrival.*
FROM qFreedaysBeforeNextArrival
WHERE (((qFreedaysBeforeNextArrival.FreeDaysBefore)=0));
Here is a revised qCambios that will identify all Cambios/changeover (same day arrive depart)
You enter a parameter TheDate in MM\DD\YY format and the calculation is for 1 day previous through 10 days forward.
Code:
Parameters TheDate date;
SELECT qFreedaysBeforeNextArrival.*
FROM qFreedaysBeforeNextArrival
WHERE (((qFreedaysBeforeNextArrival.FreeDaysBefore)=0))
and nextArrival between TheDate -1 and TheDate +10;
I also noticed that in the previous query, the StayLength has to be increased by +1 since it includes the arrive date.
Here is the revised code that accounts for arrive date in the StayLength.
Code:
SELECT Cuscode
,arrive
,depart
,datediff("d", arrive, depart) + 1 AS stayLength
,PAX
,limpcode
,Partyname
,datediff("d", depart, nextarrival) AS FreeDaysBefore
,(
SELECT TOP 1 Dupe.arrive
FROM tblbookings AS Dupe
WHERE Dupe.cuscode = tblbookings.cuscode
AND Dupe.arrive >= tblbookings.depart
ORDER BY Dupe.Depart ASC
,Dupe.cuscode
) AS nextArrival
FROM tblbookings
ORDER BY cuscode
,arrive;
Revised sample output"
Here is a sample using the revised qCambios with 4/17/18 as the parameter value.
Note: You must use US Date format MM/DD/YY
If you just want the number of arrive and departs in the next time period, and the number of free days between
you could remove this from qCambios
Code:
(((qFreedaysBeforeNextArrival.FreeDaysBefore)=0))
and
Good luck with the project.