FQYZjiaozhigong考勤,排班完数据处理后的统计报表T-SQL生成脚本

         --FQYZjiaozhigong考勤,排班完数据处理后的统计报表T-SQL生成脚本

CREATE PROCEDURE p_tj
(@st datetime,
@et datetime)  AS
SELECT @st as 起始日期,  @et as 终止日期,dpt.dpname AS 部门, dbo.Customers.Name AS 姓名,
      dbo.Customers.OutID AS 学工号, grp_bc_name.bc_name AS 班次,
      grp_bc_name.StartPoint AS 起始刷卡统计,
      grp_bc_name.EndPoint AS 终止刷卡统计
FROM (SELECT CustID, bc_name, '正常:' + CONVERT(varchar,
              SUM(CASE WHEN startstatus = 0 THEN 1 ELSE 0 END))
              + '次,迟到:' + CONVERT(varchar,
              SUM(CASE WHEN startstatus = 1 THEN 1 ELSE 0 END))
              + '次,未刷:' + CONVERT(varchar,
              SUM(CASE WHEN startstatus = 3 THEN 1 ELSE 0 END)) + '次' AS StartPoint,
              '正常:' + CONVERT(varchar,
              SUM(CASE WHEN endstatus = 0 THEN 1 ELSE 0 END))
              + '次,早退:' + CONVERT(varchar,
              SUM(CASE WHEN endstatus = 2 THEN 1 ELSE 0 END))
              + '次,未刷:' + CONVERT(varchar,
              SUM(CASE WHEN endstatus = 3 THEN 1 ELSE 0 END))
              + '次' AS EndPoint
        FROM ProcRecords where procdt between @st and @et
        GROUP BY CustID, bc_name) grp_bc_name INNER JOIN
      dbo.Customers ON grp_bc_name.CustID = dbo.Customers.CustID INNER JOIN
          (SELECT DeptCode1 + DeptCode2 + DeptCode3 AS dpcode,
               CASE WHEN deptname2 = '' THEN deptname1 WHEN deptname2 <> '' AND
               deptname3 = '' THEN DeptName1 + '\' + DeptName2 WHEN deptname2 <> '' AND
                deptname3 <> '' THEN DeptName1 + '\' + DeptName2 + '\' + DeptName3 END AS
                dpname
         FROM Department) dpt ON
      dbo.Customers.DeptCode = dpt.dpcode COLLATE Chinese_PRC_CI_AS
GO