[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 504
  • Last Modified:

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.
0
dwknight
Asked:
dwknight
  • 5
  • 2
1 Solution
 
lwadwellCommented:
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

0
 
lwadwellCommented:
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

0
 
dwknightAuthor Commented:
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!
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
lwadwellCommented:
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

0
 
lwadwellCommented:
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.
0
 
lwadwellCommented:
And it appears I have put in the second one:
  WHERE datediff(m, prior_out, date_in) >= 12
when it should be
  WHERE datediff(m, prior_out, date_in) <= 12
0
 
dwknightAuthor Commented:
Thanks for the reply. Worked a treat!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now