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.

Lee Wadwell

something along the lines of (with knowing the full table and column names) perhaps?

SELECT *
FROM (SELECT client.client_id, 
             last.date_in, 
             last.date.out, 
             max(prior.date_in) as prior_in, 
             max(prior.date_out) as prior_out
      FROM client
           JOIN accomodation last  ON client.client_id = last.client_id
           JOIN accomodation prior ON client.client_id = prior.client_id
                                      AND prior.date_out < last.date_in
      GROUP BY client.client_id, 
             last.date_in, 
             last.date.out) v
WHERE datediff(m, prior_out, date_in) <= 12

Open in new window

Lee Wadwell

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

Open in new window

dwknight

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!

Lee Wadwell

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

Open in new window

Lee Wadwell

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

Lee Wadwell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dwknight

ASKER

Thanks for the reply. Worked a treat!