Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
roger00713
Starting Member
11 Posts |
 Posted - 2011-06-16 : 23:03:10
|
| Hi.Im a bit stuck at the moment trying to get a report to do what i want it to.The report is for attendance records at a school.The table that stores these records has 8 records for each student for each school day.The relevant fields are Studentkey, date, period and attended.Most of these are self explanatory but the period field is numbered 1 - 8 and the attended field marks whether the student attended that period. It is either an A for Absent, P for Present or Null for not recorded.I am requiring a report to group all 8 periods together and tell me if a student was absent for an entire day (either an A or Null in each attended field). If someone could help me with this that would be greatly appreciated.Thanks in advance. |
|
|
Lewie
Starting Member
42 Posts |
Posted - 2011-06-17 : 10:31:29
|
| SELECT Studentkey,date, Case when SUM(Case when isnull(attended,'') = 'P' then 1 else 0 end) > 0 then 'Was there during the day' else 'Absent all Day' end as StudentResultFROM <Table name>GROUP BY Studentkey,date |
 |
|
|
roger00713
Starting Member
11 Posts |
Posted - 2011-06-19 : 22:20:35
|
| Thanks for the advice.Worked great! |
|
|
|
|
|
|
Grouping