0

I am working on a query to get monthly attendance totals by department. i.e it will show present employees in each department based on attendance logs table where check_type = 'C-IN'

  • Table 1: checkin_out_log
    • Columns: emp_id, check_time, check_type (C-IN, C-OUT)
  • Table 2: department
  • Table 3: employee

Expected output is 

    Department | Total Staff | Date 1 | Date 2 | Date 3|......
    finance    | 60          | 50     | 55     | 48    |.....

Here is my query which is currently returning me total staff in each department, 

SELECT 
    *
FROM 
    (SELECT 
         d.id, d.name AS Department, T.DateToCheck,
         COUNT(e.id) AS staff
     FROM 
         employee e
     CROSS JOIN 
         (SELECT 
              CAST(DATEADD(DAY, number, @DateFrom) AS DATE) 
          FROM 
              master..spt_values 
          WHERE 
              type = 'P' 
              AND DATEADD(DAY, number, @DateFrom) <= @DateTo ) T (DateToCheck)
    LEFT JOIN 
        department d ON  d.id = e.department 
    WHERE
        d.id = 7
    GROUP BY 
        d.id, d.name, T.DateToCheck) T
PIVOT 
     (MAX(staff) 
         FOR DateToCheck IN ([2018-09-01],[2018-09-02],[2018-09-03],[2018-09-04],[2018-09-05],[2018-09-06],[2018-09-07],[2018-09-08],[2018-09-09],[2018-09-10],
[2018-09-11],[2018-09-12],[2018-09-13],[2018-09-14],[2018-09-15],[2018-09-16],[2018-09-17],[2018-09-18],[2018-09-19],[2018-09-20],[2018-09-21],[2018-09-22],[2018-09-23],
[2018-09-24],[2018-09-25],[2018-09-26],[2018-09-27],[2018-09-28],[2018-09-29],[2018-09-30])) P
;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Nouman
  • 65
  • 1
  • 1
  • 6
  • Can't help but think you'd be better off putting your departments across the top, because they don't change often. Either that or making your calendar just the day number across the top 1..31 and not exact dates. I'd find changing this query every month to have the updated dates for this month, a massive pain in the ass.. – Caius Jard Oct 19 '18 at 05:25
  • 1
    What is your question, by the way? – Caius Jard Oct 19 '18 at 05:27
  • I need to show present employees in each department based on attendance logs table – Nouman Oct 19 '18 at 05:52
  • @CaiusJard no its not the case, this query is basically a result of a dynamic query which generates dynamic dates based on the range supplied, so issue at all on date columns – Nouman Oct 19 '18 at 05:54
  • Ugh, I had a feeling the word "dynamic" was going to creep in.. – Caius Jard Oct 19 '18 at 06:39
  • Is the 'biometrics' tag applicable to this question? I cannot discern that. – Tano Fotang Oct 20 '18 at 16:16

0 Answers0