Calculate Days Present and absent from the table

Question

I have a table name Emp_mon_day which consists Employees Present and Absent details.

What I want is

I need for these 9 employees, information about days present and days absent for each employees from Emp_mon_day table merged in to below query

QUERY

SELECT e.comp_mkey,
   e.status,
   e.resig_date,
   dt_of_leave,
   e.emp_name,
   e.date_of_joining,
   e.emp_card_no,
   a.pl_days,
   pl_days_opening,
   a.month1,
   a.month2,
   a.month3,
   a.month4,
   a.month5,
   a.month6,
   a.month7,
   a.month8,
   a.month9,
   a.month10,
   a.month11,
   a.month12,       
   a.month1 + a.month2 + a.month3 + a.month4 + a.month5 + a.month6 +   a.month7 + a.month8 + a.month9 + +a.month10 + a.month11 + a.month12 AS pl_sum
  FROM p_leave_allocation AS a
   INNER JOIN
   emp_mst AS e
   ON a.emp_card_no = e.emp_card_no
  WHERE  a.year = 2016
   AND (datediff(MONTH, e.date_of_joining, CONVERT (DATETIME, getdate(), 103)) >= 6
        AND datediff(MONTH, e.date_of_joining, CONVERT (DATETIME, getdate(), 103)) <= 36)
   AND (e.resig_date IS NULL
        OR (e.dt_of_leave IS NOT NULL
            AND e.dt_of_leave >= CONVERT (DATETIME, getdate(), 103)))
   AND e.status IN ('A', 'S')
   AND e.comp_mkey IN (7, 110)
   AND a.Year = 2016;

The above query gives me data as below

[![Image data][1]][1]

Column details for Emp_mon_day is below

[![enter image description here][2]][2]

Answer 1

You can try the below query:

SELECT e.comp_mkey, e.status,   e.resig_date,   dt_of_leave,    e.emp_name,
   e.date_of_joining,  e.emp_card_no,   a.pl_days,    pl_days_opening,    a.month1,
   a.month2,   a.month3,   a.month4,   a.month5,   a.month6,   a.month7,   a.month8,
   a.month9,   a.month10,   a.month11,   a.month12,       
   a.month1 + a.month2 + a.month3 + a.month4 + a.month5 + a.month6 +   a.month7 + a.month8 + a.month9 + +a.month10 + a.month11 + a.month12 AS pl_sum,
   m.[DaysAbsent],m.[DaysPresent]
  FROM p_leave_allocation AS a
   INNER JOIN
   emp_mst AS e
   ON a.emp_card_no = e.emp_card_no
   INNER JOIN
   (
   SELECT 
        comp_mkey,emp_mkey,[month],[year], 
        SUM(CASE WHEN data ='AB' THEN 1 ELSE 0 END) AS [DaysAbsent],
        SUM(CASE WHEN data ='P' THEN 1 ELSE 0 END) AS [DaysPresent]
    FROM
        (
        SELECT comp_mkey,emp_mkey,[month],[year],[Day1],[Day2],[Day3],[Day4],[Day5]
        --,...  
        FROM Emp_mon_day
        ) source
        UNPIVOT
        (
        data FOR day IN ([Day1],[Day2],[Day3],[Day4],[Day5]) -- dynamic query can generate all days data
        )up
        GROUP BY comp_mkey, emp_mkey,[month],[year]
   ) AS m
   ON m.comp_mkey=e.Comp_mkey and m.emp_mkey=e.mkey
   --- ABOVE CRITERIA NEEDS TO BE CHECKED
  WHERE  a.year = 2016
   AND (datediff(MONTH, e.date_of_joining, CONVERT (DATETIME, getdate(), 103)) >= 6
        AND datediff(MONTH, e.date_of_joining, CONVERT (DATETIME, getdate(), 103)) <= 36)
   AND (e.resig_date IS NULL
        OR (e.dt_of_leave IS NOT NULL
            AND e.dt_of_leave >= CONVERT (DATETIME, getdate(), 103)))
   AND e.status IN ('A', 'S')
   AND e.comp_mkey IN (7, 110)
   AND a.Year = 2016;

Explanation:

We've added another INNER JOIN to the existing query to get collated data of DaysPresent and DaysAbsent

To optimize this further, I'd suggest you directly apply following WHERE clause to source set

WHERE  comp_mkey IN (7, 110) AND Year = 2016;