I hope someone can help, I am struggling to create a query to monitor the attendance of employees within a company. I have the below 3 tables;
1- People contains the name of the employee
2- [log] contains all the daily information for the employee. The Log_No relates to the absence code. E.g if aperson does not have a code that links to the absence table, they have worked a normal day. If the code links with the absence table, they have been absent for one of the below reasons. In the below case Robbie has had 2 days holiday
3- Absence this contain the reasons for absence.
I am trying to use the below 3 tables to create an output that ideally looks like the below, Any help would be greatly appreciated.
Ideal output e.g,
FirstName/ Surname/ Normal Working Day /Holiday/ Statutory Holiday/sickness/Training .
Ricky Marsh 3 2 0 0 0
select Person_No, Active, FirstName, Surname
where FirstName = 'Robbie'
and Surname = 'Marsh'
Person _No Active First Name Surname
1158 Y Robbie Marsh
select Person_No, Log_No, Arrived
where Person_No = '1158'
and Arrived between '2010-10-11 00:00:01.000' and '2010-10-16 23:00:01.000'
SUM(CASE WHEN A.Absence_Code_no IS NULL THEN 1 ELSE 0 END) AS [Normal Working Day],
SUM(CASE WHEN A.Absence_Code_no = 1 THEN 1 ELSE 0 END) AS [Holiday],
SUM(CASE WHEN A.Absence_Code_no = 2 THEN 1 ELSE 0 END) AS [Statutory Holiday],
SUM(CASE WHEN A.Absence_Code_no = 3 THEN 1 ELSE 0 END) AS [Sickness],
SUM(CASE WHEN A.Absence_Code_no = 4 THEN 1 ELSE 0 END) AS [Training],
SUM(CASE WHEN A.Absence_Code_no = 5 THEN 1 ELSE 0 END) AS [Unauthorised Absence],
SUM(CASE WHEN A.Absence_Code_no = 6 THEN 1 ELSE 0 END) AS [Compassionate Leave]
FROM #People AS P
INNER JOIN #log AS L ON
P.Person_No = L.Person_No
LEFT OUTER JOIN #Absence as A ON
L.Log_No = A.Log_No
where P.FirstName = 'Robbie'
and P.Surname = 'Marsh'
and L.Arrived between '2010-10-11 00:00:01.000' and '2010-10-16 23:00:01.000'
GROUP BY P.FirstName, P.Surname
It will fail however when there are more than one [Log] records per person per day (e g. 2010-10-11 09:00:00.000 at morning and 2010-10-11 13:00:00.000 at noon).
With kind regards . . . . . SQL Server 2000/2005/2012
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2. Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages