I am trying to deduct wage depending upon employee late attendance count. So my condition is: if an employee continuously comes late 3 days (Except friday and saturday - Weekly holidays), then one day salary will be deducted. The sample is as follows and this is what I have now with the given query:
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 above, when it comes to attendance late count on the third consecutive day, then the deduction takes place. Again, let me remind, this must be excluded friday and saturday. I am able to show the late count and weekly holidays but not sure how would I proceed with the deduction. I am planning to make a table 'Deduction' for the deduction purpose to store the days as follows if I am 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 specific month (Even per 3 days), how would the logic be validated? I tried a silly one (Never going a solution though):
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');