asked on

Testing if current date is between two dates

I have the SQL query below working from the following tables:

- Activities
- People
- UserList

I want to select another field 'status' - in which the following conditions are tested...

- If today's date is between Activities.Activity_Start and Activities.Activity_End, then return 'Current'
- If today's date is before Activity.Activity_Start then return 'Pending'
- If today's date is past Activities.Activity_End then return 'Archive'

This is my current SQL:

SELECT Activities.Activity_ID, Activities.Activity_Name, People.Person_nameFirst, People.Person_nameLast FROM Activities
INNER JOIN UserList ON Activities.Activity_Owner = UserList.User_Username
INNER JOIN People ON UserList.Person_ID = People.Person_ID
WHERE (Activities.Account_ID = @Account_ID)

Any suggestions on how I can fit this in, i'm getting a bit drowned in SQL!

Any help appreciated.

Nick

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

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

Start Free Trial

adathelad

SELECT Activities.Activity_ID, Activities.Activity_Name, People.Person_nameFirst, People.Person_nameLast,
CASE
WHEN GETDATE() BETWEEN Activities.Activity_Start AND Activities.Activity_End THEN 'Current'
WHEN GETDATE() < Activities.Activity_Start THEN 'Pending'
WHEN GETDATE() > Activities.Activity_End THEN 'Archive'
END AS Status
FROM Activities
INNER JOIN UserList ON Activities.Activity_Owner = UserList.User_Username
INNER JOIN People ON UserList.Person_ID = People.Person_ID
WHERE (Activities.Account_ID = @Account_ID)

nkewney

ASKER

Thanks to both of you. adathelad I'm sorry I didn't see your solution until I'd awarded the points. Thanks anyway... Nick