计算表中存在和不存在的天数 [英] Calculate Days Present and absent from the table

问题描述

我有一个表名 Emp_mon_day，其中包含员工 Present 和 Absent details.

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

我想要的是

我需要这 9 名员工，将 Emp_mon_day 表中每个员工的在职天数和缺勤天数信息合并到以下查询中

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

查询

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

[![图像数据][1]][1]

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

Emp_mon_day 的列详细信息如下

[![在此处输入图片描述][2]][2]

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

推荐答案

你可以试试下面的查询:

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;

说明:

我们在现有查询中添加了另一个 INNER JOIN 以获取 DaysPresent 和 DaysAbsent

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

为了进一步优化，我建议您直接将以下 WHERE 子句应用于 source set

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;

这篇关于计算表中存在和不存在的天数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！