Date question

[Naoise]

tblDate
FooID[int] | StartDate[datetime] | EndDate[datetime]

tblException
FooID[int] | ExceptionDate[datetime]

Looking for one query that completes these 3 things

How do I select
i] all dates between StartDate and EndDate from tblDate for a given FooID
ii] where that date is not in tblException for the FooID
iii] and that date is a Friday/Saturday/Sunday

 

[AlexCuse]

What database are you using?

ANd do you want dates from your table, or do you want to create a list of dates?

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[r937]

what does "select all dates between StartDate and EndDate" mean?

so you want this row --

42 '2007-02-06' '2008-04-09'

to generate 428 output rows?

what is the expected largest number of days between StartDate and EndDate?

r937.com | rudy.ca

 

[Naoise]

Sorry I should have explained myself more clearly. I will be given a date and I need to find FooID's from tblDate where the supplied date is between start and end date records in tblDate but is not in tblException for that FooID. I also have to ensure that the date given is a Friday/Saturday/Sunday

 

[r937]

select FooID
  from tblDate 
 where '2007-02-07' between StartDate and EndDate
   and datepart(dayofweek,'2007-02-07') not in (2,3,4,5)
   and not exists
       ( select *
           from tblException
          where FooID = tblDate.FooID )

of course, the datepart function is not an ANSI SQL function, you will need to find the equivalent function in whatever database system you're using

r937.com | rudy.ca

 

[Naoise]

Thanks for that, spot on