Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2010
    Posts
    6

    Unanswered: query help needed

    Hi,
    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

    .................................................. ................................................

    Below are the tables I am working with.

    Query 1
    select Person_No, Active, FirstName, Surname
    from People
    where FirstName = 'Robbie'
    and Surname = 'Marsh'

    output
    Person _No Active First Name Surname
    1158 Y Robbie Marsh
    ………………………………………………………………………………………………………………..
    Query 2
    select Person_No, Log_No, Arrived
    From [log]
    where Person_No = '1158'
    and Arrived between '2010-10-11 00:00:01.000' and '2010-10-16 23:00:01.000'

    Output
    Person_No Log_No Arrived
    1158 183951 2010-10-14 09:00:00.000
    1158 183952 2010-10-15 09:00:00.000
    1158 191787 2010-10-11 10:58:31.360
    1158 191881 2010-10-12 08:52:15.160
    1158 192108 2010-10-13 08:49:05.490
    ………………………………………………………………………………………………………………….



    Query 3
    Select Log_No, Absence_Log_No, Absence_Code_no
    from Absence
    where Log_No in ('183951','183952','191787','191881','192108')

    output 3
    Log_No Absence_Log_No Absence_Code_No
    183951 98770 1
    183952 98771 1
    …………………………………………………………………………………………………………..
    Absence_Code_no Description
    1 Holiday
    2 Statutory Holiday
    3 Sickness
    4 Training
    5 Unauthorised Absence
    6 Compassionate Leave

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This should work:
    Code:
    SELECT P.FirstName,
    	P.Surname,
    	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
    Wim

    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

  3. #3
    Join Date
    Oct 2010
    Posts
    6
    The query works perfectly and is exactly what I needed. Thank you very much for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •