sql - 有条件扣除员工工资

Question

我正在尝试根据员工迟到次数扣除工资。所以我的条件是:员工连续迟到3天(周五周六除外——周休)，则扣1天工资。示例如下，这就是我现在使用给定查询得到的内容:

Name  PunchDate             Attendance   PerDaySal          Status    
John  2017-05-12 00:00:00.000   W        461.538461538462   Weekly
John  2017-05-13 00:00:00.000   W        461.538461538462   Weekly
John  2017-05-14 09:00:00.000   P        461.538461538462   On Time
John  2017-05-15 09:16:00.000   P        461.538461538462   Late
John  2017-05-16 09:18:00.000   P        461.538461538462   Late
John  2017-05-17 09:20:00.000   P        0                  Late -- On 3rd consecutive day
John  2017-05-18 09:26:00.000   P        461.538461538462   Late
John  2017-05-19 00:00:00.000   W        461.538461538462   Weekly
John  2017-05-20 00:00:00.000   W        461.538461538462   Weekly
John  2017-05-21 09:18:00.000   P        461.538461538462   Late
John  2017-05-22 09:28:00.000   P        0                  Late -- On the next 3rd consecutive day (Possibly 6th)           
John  2017-05-23 09:28:00.000   P        461.538461538462   Late              

上述中，如果连续第三天迟到，则进行扣除。我再次提醒一下，周五和周六必须排除这一点。我可以显示延迟计数和每周假期，但不确定如何进行扣除。如果我没记错的话，我打算制作一个“扣除”表来存储天数，如下所示:

ID    Days  
1     3 ---- On 3rd late count, PerDaySal 0
2     6 ---- On 6th late count, PerDaySal 0 and so on 

但是对于特定月份(甚至每三天)，如何验证逻辑？我尝试了一个愚蠢的方法(但从未找到解决方案):

SELECT k.NAME AS Employee, m.PunchDate,

(CASE WHEN o.WeekName = 'Friday' OR o.WeekName = 'Saturday' THEN 'W' ELSE m.Status END) 
AS Attendance,

(CASE WHEN o.WeekName = 'Friday' OR o.WeekName = 'Saturday' THEN 0 ELSE (k.SALARY / 26) END) 
AS PerDaySal,

(CASE WHEN CONVERT(CHAR(8), m.PunchDate, 108) > '09:15:00' THEN 'Late'  
 WHEN CONVERT(CHAR(8), m.PunchDate, 108) >= '09:00:00' AND CONVERT(CHAR(8), m.PunchDate, 108) <= '09:15:00' THEN 'On Time'
 WHEN o.WeekName = 'Friday' OR o.WeekName = 'Saturday' AND CONVERT(CHAR(8), m.PunchDate, 108) <= '00:00:00' THEN 'Weekly' END) AS Status

--The silly one - (CASE WHEN COUNT(CONVERT(CHAR(8), m.PunchDate, 108) > '09:15:00') > 3 THEN 0 ELSE (k.SALARY / 26) END) AS PerDaySal

FROM @Attendances m INNER JOIN @Employee k ON k.ID = m.EmpId 
LEFT JOIN @Weekly o ON o.WeekDate = m.PunchDate
GROUP BY k.NAME, m.PunchDate, m.Status, k.Salary, o.WeekName 

以下是带有示例数据的表结构:

declare @Attendances table (Id int not null identity(1,1) primary key,EmpId int,PunchDate datetime,Status nvarchar(4));
insert into @Attendances([EmpId],[PunchDate],[Status]) values
 (2,cast(0x0000A77200000000 as datetime),N'A')
,(2,cast(0x0000A77100000000 as datetime),N'A')
,(2,cast(0x0000A776009A5BA0 as datetime),N'P')
,(2,cast(0x0000A775009450C0 as datetime),N'P')
,(2,cast(0x0000A77400982920 as datetime),N'P')
,(2,cast(0x0000A773009450C0 as datetime),N'P');

declare @Employee table (ID int not null primary key,NAME nvarchar(50),ADDRESS nvarchar(max),SALARY float);
insert @Employee([ID], [NAME], [ADDRESS], [SALARY]) values
 (1, N'John', N'Germany', 12000)
,(2, N'Jack', N'France', 14000);

declare @Weekly table (WeekID int not null primary key,WeekNAME nvarchar(20),WeekDate datetime,Status nvarchar(10));
insert @Weekly([WeekID], [WeekName], [WeekDate], [Status]) values
 (1, N'Friday', CAST(0x0000A77100000000 AS DateTime), N'W')
,(2, N'Saturday', CAST(0x0000A77200000000 AS DateTime), N'W');

Answer 1

使用示例表而不是多个表，并使用通用表表达式和带有 case 表达式的窗口函数，如果按照问题规范在周五或周六，则不会将一天算作迟到撰写本文的时间。

;with cte as (
select * 
  , rn = row_number() over (partition by Name order by punchDate)
  , DayName = datename(weekday,punchdate)
  , LateDays = (select sum(
    case when datename(weekday,punchdate) not in ('Friday','Saturday') and [Status]='Late'
        then 1 else 0 end
  )
  from t i
  where i.Name = t.Name and i.PunchDate <= t.PunchDate
  )
from t
)
select 
    cte.Name
  , cte.PunchDate
  , cte.DayName
  , cte.Attendance
  , PerDaysSal = case when z.rn is not null then 0 
      else cte.PerDaySal end
  , cte.Status
  , cte.rn
  , cte.LateDays
from cte
  left join (
    select i.Name, rn=min(i.rn)
    from cte i
    where i.LateDays > 0
      and i.LateDays % 3 = 0
    group by Name, LateDays
      ) z
  on cte.Name = z.Name
 and cte.rn = z.rn

rextester 演示:http://rextester.com/ETOI35350

返回:

+------+---------------------+-----------+------------+---------------+---------+----+----------+
| Name |      PunchDate      |  DayName  | Attendance |  PerDaysSal   | Status  | rn | LateDays |
+------+---------------------+-----------+------------+---------------+---------+----+----------+
| John | 12.05.2017 00:00:00 | Friday    | W          | 461,538461538 | Weekly  |  1 |        0 |
| John | 13.05.2017 00:00:00 | Saturday  | W          | 461,538461538 | Weekly  |  2 |        0 |
| John | 14.05.2017 00:00:00 | Sunday    | P          | 461,538461538 | On Time |  3 |        0 |
| John | 15.05.2017 00:00:00 | Monday    | P          | 461,538461538 | Late    |  4 |        1 |
| John | 16.05.2017 00:00:00 | Tuesday   | P          | 461,538461538 | Late    |  5 |        2 |
| John | 17.05.2017 00:00:00 | Wednesday | P          | 0,000000000   | Late    |  6 |        3 |
| John | 18.05.2017 00:00:00 | Thursday  | P          | 461,538461538 | Late    |  7 |        4 |
| John | 19.05.2017 00:00:00 | Friday    | W          | 461,538461538 | Weekly  |  8 |        4 |
| John | 20.05.2017 00:00:00 | Saturday  | P          | 461,538461538 | Late    |  9 |        4 |
| John | 21.05.2017 00:00:00 | Sunday    | P          | 461,538461538 | On Time | 10 |        4 |
| John | 22.05.2017 00:00:00 | Monday    | P          | 461,000000000 | Late    | 11 |        5 |
| John | 23.05.2017 00:00:00 | Tuesday   | P          | 0,000000000   | Late    | 12 |        6 |
+------+---------------------+-----------+------------+---------------+---------+----+----------+

<小时/>

在使用实际表时，不应按Name分区，而应按EmpId分区。您还可以使用出勤中的 id 而不是用于对行进行编号的 row_number()。