selecting dates that dont exist in a table?

• ben potts

  Old Hand

  Points: 384

  More actions

  March 4, 2006 at 7:29 pm

  #111999

  Hi,

  I did some tsql a year or so ago, it wasnt much but it allows me to do most things i need to do in my job.

  I have created a new database for bookings, each booking has all the relevant details and also the date that it will occur. im trying to create a script that will look for the 10 soonest dates that there is not a booking for. at first i thought it would be easy, but now once i have got round to creating it i cant seem to think of a way to make it work!

  TABLE: BOOKINGS

  FIELD: DATE_BOOKED

  has anybody advice on how i can do this?

  many thanks,

  Ben

• paramind

  SSCrazy

  Points: 2130

  More actions

  March 5, 2006 at 2:00 am

  #624779

  It somewhat depends on your performance needs; one way to achieve it:

  CREATE TABLE IDEnumerator (ID TINYINT PRIMARY KEY)

  INSERT IDEnumerator (ID)

  SELECT 1

  UNION SELECT 2

  UNION SELECT 3

  UNION SELECT 4

  UNION SELECT 5

  UNION SELECT 6

  UNION SELECT 7

  UNION SELECT 8

  UNION SELECT 9

  UNION SELECT 10

  CREATE VIEW NextTenDates

  AS

  SELECT

  DATEADD(dd,ID,DATEDIFF(dd,0,GETDATE())) AS DateX

  FROM IDEnumerator

  --Version 1

  SELECT

        DateX

  FROM NextTenDates

  WHERE

         DateX NOT IN (Select BookingDate FROM Booking)

  --Version 2: Use a join

  --You don't have to create a static table though.

  --A temptable or a table valued function will do of course

  --but may not deliver the performance you want.

  
  _/_/_/ paramind _/_/_/

• ben potts

  Old Hand

  Points: 384

  More actions

  March 5, 2006 at 9:07 am

  #624787

  thanks a lot for the help. got it working now