Withholding the employee's wages subject to
I am trying to subtract salaries based on the number of employees visiting the account. Thus, my condition: if an employee constantly arrives late 3 days (except for Friday and Saturday - weekly holidays), then one day the salary will be deducted. The sample is as follows, and this is what I have now with the given request:
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
In the example above, when it comes to late tracking for the third day in a row, a hold occurs. Again, let me remind you that this should be excluded on Friday and Saturday. I can show late counting and weekly holidays, but I don't know how I can proceed with the withdrawal. I am planning to make a Deduction table for the hold target to store days like this, if I'm not mistaken:
ID Days
1 3 ---- On 3rd late count, PerDaySal 0
2 6 ---- On 6th late count, PerDaySal 0 and so on
But for a specific month (even 3 days), how will the logic be verified? I tried a stupid (never solution):
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
Here are the table structures with sample data:
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');
source to share
Using your example table instead of multiple tables with a common table expression and expression box functions case
to avoid counting a day as late if it is Friday or Saturday as per the specification of the question at the time of this writing.
;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
Demo version of the rexter: http://rextester.com/ETOI35350
returns:
+------+---------------------+-----------+------------+---------------+---------+----+----------+
| 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 |
+------+---------------------+-----------+------------+---------------+---------+----+----------+
Instead of being partitioned into, Name
it should be partitioned EmpId
when using actual tables. You can also use id
from Attendance instead of row_number()
used for line number.
source to share