dwknight
asked on
SQL Accommodation query
Hello,
I have an accommodation table that lists date into a room and date out of a room plus a client id that has details populated in another table.
What I am looking for is a query to compare the accommodation dates between the accommodation records for a client where there is a duration between visits to the motel of less than 12 months.
Thanks for the assistance.
I have an accommodation table that lists date into a room and date out of a room plus a client id that has details populated in another table.
What I am looking for is a query to compare the accommodation dates between the accommodation records for a client where there is a duration between visits to the motel of less than 12 months.
Thanks for the assistance.
A slightly different alternative:
SELECT *
FROM (SELECT last.client_id,
last.date_in,
last.date.out,
max(prior.date_in) as prior_in,
max(prior.date_out) as prior_out
FROM accomodation last
JOIN accomodation prior ON client.client_id = prior.client_id
AND prior.date_out < last.date_in
GROUP BY last.client_id,
last.date_in,
last.date.out) v
JOIN client on client.client_id = v.client_id
WHERE datediff(m, prior_out, date_in) >= 12
ASKER
Thanks for both of your comments lwadwell.
The first has some real promise - am still working through the query at the moment.
The second looks very interesting, but I am having trouble with the below line (line 8 of the above query)
JOIN accomodation prior ON client.client_id = prior.client_id
The client.client.id will not bind. The error line is 'The multi-part identifier "client.client_id" could not be bound'
Thanks again for your help with my issue!
The first has some real promise - am still working through the query at the moment.
The second looks very interesting, but I am having trouble with the below line (line 8 of the above query)
JOIN accomodation prior ON client.client_id = prior.client_id
The client.client.id will not bind. The error line is 'The multi-part identifier "client.client_id" could not be bound'
Thanks again for your help with my issue!
Oh bother! Forgot to change an alias:
SELECT *
FROM (SELECT last.client_id,
last.date_in,
last.date.out,
max(prior.date_in) as prior_in,
max(prior.date_out) as prior_out
FROM accomodation last
JOIN accomodation prior ON last.client_id = prior.client_id
AND prior.date_out < last.date_in
GROUP BY last.client_id,
last.date_in,
last.date.out) v
JOIN client on client.client_id = v.client_id
WHERE datediff(m, prior_out, date_in) >= 12
They are essentially the same ... except that the second one joins to the client table last as I thought you might want other details from it that were not included in the sub-query.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the reply. Worked a treat!
Open in new window