Hospitality Book

• GlenParker

  Ten Centuries

  Points: 1051

  More actions

  January 9, 2009 at 8:06 am

  #77172

  Hi Troops

  I have an on-line Hospitality Book which is audited 6 monthly ie audit column set to 1 (default = 0)

  I am attempting to select only records with dates on/after most recent audit (pertinent columns; ID, DateEntered, audit)

  Any suggestions?

  Glen

  Glen Parker 🙂

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  January 9, 2009 at 8:28 am

  #924236

  Glen Parker (1/9/2009)

  ---

  Hi Troops

  I have an on-line Hospitality Book which is audited 6 monthly ie audit column set to 1 (default = 0)

  I am attempting to select only records with dates on/after most recent audit (pertinent columns; ID, DateEntered, audit)

  Any suggestions?

  Glen

  select

  hp.ID,

  hp.DateEntered,

  hp.audit

  from

  dbo.HospitalityBook hp

  where

  hp.DateEntered > (

  select

  max(hp1.DateEntered)

  from

  dbo.HospitalityBook hp1

  where

  hp1.audit = 1)

  Give that a try. No promises as I have nothing to test this against.

  For better answers to your questions, please read the first article linked below in my signature block.

• GlenParker

  Ten Centuries

  Points: 1051

  More actions

  January 12, 2009 at 3:13 am

  #925152

  Morning Lynn

  Thank you for the script, worked first time:)

  I like the simplicity and effectiveness of the 'nested loop' and wonder how I never realised the solution for myself. I do recall covering the subject during my SQL course at Learning Tree but couldn't find it in me somehow.

  Greatly appreciated your help on this one!

  Thanks again:)

  Glen

  Glen Parker 🙂

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  January 12, 2009 at 7:12 am

  #925241

  I'm glad it worked for you, and thank you for the feedback.

  I can probably tell you why you didn't think of it yourself, and it is something we all run into, and I call the Forest and the Trees syndrome. You are too close to the problem to see the solution. Sort of like being to close to the trees to see the forest. Happens to me more times than I want to admit. That's why co-workers can be quite helpful and, barring that, sites like SSC.