September 14, 2013 at 10:53 am
I would like to pull a query with each student each day an attendance record.
Our database setup an AM and PM Period for all elementary students. I will pull if they absent both periods(AM, PM), then count that as one day absent.
The hard part is I need to put the AM absent code and PM absent code - which is basically to put two records for each student's AM and PM absent code into one row.
Any hint how to do this:
Below is the query I use, but it violates the key of database, for PK is studentid+ attendance date. My query result turns out for some students they have different attendance code in AM vs PM, there are two records returned.
SELECT DISTINCT TO_CHAR(S.Student_Number) ,
TO_CHAR(Am.Schoolid) ,
Sps_School_Year('C') ,
TO_CHAR(Am.Att_Date,'MM/DD/YYYY') ,
(
CASE
WHEN Am.Period_Abbreviation='EAM'
AND Am.Att_Code IN ('VU','UA','A')
THEN 'U'
WHEN Am.Period_Abbreviation='EAM'
AND Am.Att_Code NOT IN ('VU','UA','A')
THEN 'E'
ELSE NULL
END) AS Am_Excusedunexcused ,
(
CASE
WHEN Am.Period_Abbreviation='EPM'
AND Am.Att_Code IN ('VU','UA','A')
THEN 'U'
WHEN Am.Period_Abbreviation='EPM'
AND Am.Att_Code NOT IN ('VU','UA','A')
THEN 'E'
ELSE NULL
END) Pm_Excusedunexcused,
'Period' TO_CHAR(aM.Att_Date,'MM/DD/YYYY')
FROM Ps_Attendance_Meeting Am,
Students S,
(SELECT Studentid,
M.Schoolid,
Att_Date
FROM Ps_Attendance_Meeting M
WHERE M.Presence_Status_Cd ='Absent'
AND M.Period_Abbreviation IN ('EAM','EPM')
GROUP BY Studentid,
M.Schoolid,
Att_Date
HAVING COUNT(*)>1
) Co
WHERE Am.Studentid=S.Id
AND Am.Schoolid =S.Schoolid
AND Am.Schoolid =Co.Schoolid
AND Am.Studentid =Co.Studentid
AND Am.Att_Date =Co.Att_Date
for example: I'd like the result to be:
studenid|SchoolID|year|Att_date|AMAttCode|PMAttCode
7040744|289|2013|09/13/2013|E|U
Instead of
studenid|SchoolID|year|Att_date|AMAttCode|PMAttCode
7040744|289|2013|09/13/2013|E|null
7040744|289|2013|09/13/2013|null|U
September 15, 2013 at 4:28 pm
It's difficult to understand your question, since you seem to say that you get the result
studenid|SchoolID|year|Att_date|AMAttCode|PMAttCode
7040744|289|2013|09/13/2013|E|null
7040744|289|2013|09/13/2013|null|U
But the last column before the FROM clause appears to be a date.
In any case, I think you are in the wrong place. to_char() has a distinct flavour or Oracle, and this site is devoted to SQL Server.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 16, 2013 at 11:26 am
FROM Ps_Attendance_Meeting Am
Students S
WHERE M.Presence_Status_Cd ='Absent'
AND M.Period_Abbreviation IN ('EAM','EPM')
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
September 16, 2013 at 11:34 am
select ..., max(CASE
WHEN m.Period_Abbreviation='EAM'
AND m.Att_Code IN ('VU','UA','A')
THEN 'U'
WHEN m.Period_Abbreviation='EAM'
AND m.Att_Code NOT IN ('VU','UA','A')
THEN 'E'
ELSE NULL
END),
...
FROM Ps_Attendance_Meeting m
right join Students S on s.id = m.Studentid
WHERE M.Presence_Status_Cd ='Absent'
AND M.Period_Abbreviation IN ('EAM','EPM')
GROUP BY s.Student_Number,
M.Schoolid,
Sps_School_Year,--I'm assuming this is a column, not some function.
m.Att_Date
HAVING COUNT(*)>1
If you're not on oracle, you can use the windowed aggregates to make this perform a little better.
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply