I have the SQL query below working from the following tables:
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.