June 21st, 2012, 10:34 AM
SQL Query Help
I have 2 tables. Attendant and AttendanceRecord. I want to log attendance in the attendance record table (obviously!).
I want to present the user with a dataset which has the name of the attendant and a flag as to whether they have attended on a particular date or not. So the user will set a date in control and the query will return a set of all attendees and whether they attended or not.
Have tried a number of different queries and getting close but nothing definitive.
June 21st, 2012, 11:20 AM
Change column names as apropriate.
case when AttendanceRecord.AttendanceDate = <given date> then 'Present' else 'Not present' end
on attendant.pk = AttendanceRecord.fk
and AttendanceRecord.AttendanceDate = <given date>
June 21st, 2012, 04:26 PM
Thanks a million - this does the trick.
Only thing is that in order to fit it into the tool I'm using the join isn't the easiest thing to use. Just wondering if there's any other way of doing this other than using a join on the date - is it possible to do using a where clause?
Why is the join necessary?
June 25th, 2012, 10:47 AM
case when exists(select *
where AttendanceDate = <given date>
and attendant.pk = AttendanceRecord.fk) then
'Present' else 'Not present' end
June 25th, 2012, 11:31 AM
That's perfect. Much appreciated.