SQLTeam.com | Weblogs | Forums

Attendance Calculation Based on IN Timing

Hi,
I have a attendance table which is given below.

CREATE TABLE [dbo].[Attendance](
	[Attendance_No] [numeric](18, 0) NULL,
	[Attendance_Date] [date] NULL,
	[Attendance_EmpNo] [numeric](18, 0) NULL,
	[Attendance_DailyWages] [numeric](18, 2) NULL,
	[Attendance_InTime] [datetime] NULL,
	[Attendance_OutTime] [datetime] NULL
) ON [PRIMARY]

GO

The Data inside table is given below.

	 INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime])
     VALUES (1,'2022-01-01',1,20.5,'2022-01-01 08:17 AM','2022-01-01 03:30 PM')
     
     INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime])
     VALUES (2,'2022-01-01',1,20.5,'2022-01-01 08:20 AM','2022-01-01 03:30 PM')
     
     INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime])
     VALUES (3,'2022-01-01',1,20.5,'2022-01-01 08:40 AM','2022-01-01 03:30 PM')
     
     INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime])
     VALUES (4,'2022-01-01',1,20.5,'2022-01-01 08:50 AM','2022-01-01 03:30 PM')
     
     INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime])
     VALUES (5,'2022-01-01',1,20.5,'2022-01-01 08:52 AM','2022-01-01 03:30 PM')
     
      INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime])
     VALUES (6,'2022-01-01',2,20.5,'2022-01-01 08:17 AM','2022-01-01 03:30 PM')
     
     INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime])
     VALUES (7,'2022-01-01',2,20.5,'2022-01-01 08:20 AM','2022-01-01 03:30 PM')
     
     INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime])
     VALUES (8,'2022-01-01',2,20.5,'2022-01-01 08:40 AM','2022-01-01 03:30 PM')
     
     INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime])
     VALUES (9,'2022-01-01',2,20.5,'2022-01-01 08:50 AM','2022-01-01 03:30 PM')
     
     INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime])
     VALUES (10,'2022-01-01',2,20.5,'2022-01-01 08:52 AM','2022-01-01 03:30 PM')
     
     
GO

Below in tabular format.

Attendance_No Attendance_Date Attendance_EmpNo Attendance_DailyWages Attendance_InTime Attendance_OutTime
1 2022-01-01 1 20.00 2022-01-01 08:17:00.000 2022-01-01 03:30:00.000
2 2022-01-02 1 20.00 2022-01-01 08:20:00.000 2022-01-01 03:30:00.000
3 2022-01-03 1 20.00 2022-01-01 08:40:00.000 2022-01-01 03:30:00.000
4 2022-01-04 1 20.00 2022-01-01 08:50:00.000 2022-01-01 03:30:00.000
5 2022-01-05 1 20.00 2022-01-01 08:52:00.000 2022-01-01 03:30:00.000
6 2022-01-01 2 20.00 2022-01-01 08:17:00.000 2022-01-01 03:30:00.000
7 2022-01-02 2 20.00 2022-01-01 08:20:00.000 2022-01-01 03:30:00.000
8 2022-01-03 2 20.00 2022-01-01 08:40:00.000 2022-01-01 03:30:00.000
9 2022-01-04 2 20.00 2022-01-01 08:50:00.000 2022-01-01 03:30:00.000
10 2022-01-05 2 20.00 2022-01-01 08:52:00.000 2022-01-01 03:30:00.000

How to check and apply the deduction based on Slabs.

Below is the Slabs.

Slab-A
1st Time 08:15 AM to 08:30 AM 5%
2nd Time 08:15 AM to 08:30 AM 10%
3rd Time 08:15 AM to 08:30 AM 15%
4th Time 08:15 AM to 08:30 AM 100%
Slab-B
1st Time 08:31 AM to 09:00 AM 15%
2nd Time 08:31 AM to 09:00 AM 25%
3rd Time 08:31 AM to 09:00 AM 50%
4th Time 08:31 AM to 09:00 AM 100%

Need to Get the below output. then what will be the query

Attendance_No Attendance_Date Attendance_EmpNo Attendance_DailyWages Attendance_InTime Attendance_OutTime Slab Slab Deduction Apply Deduction Amount On Daily Wages
1 01-01-2022 1 20.5 1-1-22 8:17 AM 1-1-22 3:30 AM Slab-A 1st Time-5% 1.025
2 02-01-2022 1 20.5 1-1-22 8:20 AM 1-1-22 3:30 AM Slab-A 2nd Time-10% 2.05
3 03-01-2022 1 20.5 1-1-22 8:40 AM 1-1-22 3:30 AM Slab-B 1st Time-15% 3.075
4 04-01-2022 1 20.5 1-1-22 8:50 AM 1-1-22 3:30 AM Slab-B 2nd Time-25% 5.125
5 05-01-2022 1 20.5 1-1-22 8:52 AM 1-1-22 3:30 AM Slab-B 3rd Time-50% 10.25
6 01-01-2022 2 20.5 1-1-22 8:17 AM 1-1-22 3:30 AM Slab-A 1st Time-5% 1.025
7 02-01-2022 2 20.5 1-1-22 8:20 AM 1-1-22 3:30 AM Slab-A 2nd Time-10% 2.05
8 03-01-2022 2 20.5 1-1-22 8:40 AM 1-1-22 3:30 AM Slab-B 1st Time-15% 3.075
9 04-01-2022 2 20.5 1-1-22 8:50 AM 1-1-22 3:30 AM Slab-B 2nd Time-25% 5.125
10 05-01-2022 2 20.5 1-1-22 8:52 AM 1-1-22 3:30 AM Slab-B 3rd Time-50% 10.25

can be output in Image also

please help on this
regards,
Basit.

I created your Slabs table as well to help. I added a SeqNum to identify which occurrence to pick from

Create table #Slabs (Slab char(1), Description varchar(10), SeqNum int, StartTime time, EndTime time, DeductionPct numeric(3,2))
insert into #Slabs values
('A','1st Time',1,'08:15','08:30', .05),
('A','2nd Time',2,'08:15','08:30', .10),
('A','3rd Time',3,'08:15','08:30', .15),
('A','4th Time',4,'08:15','08:30', 1.00),
('B','1st Time',1,'08:31','09:00', .15),
('B','2nd Time',2,'08:31','09:00', .25),
('B','3rd Time',3,'08:31','09:00', .50),
('B','4th Time',4,'08:31','09:00', 1.00)

select Attendance_No, Attendance_Date, Attendance_EmpNO, Attendance_DailyWages, Attendance_InTime, Attendance_OutTime,
		s.Slab, Concat(s.Description,'-', (DeductionPct * 100),'%') as SlabDeductionApply, Cast(Attendance_DailyWages * DeductionPct as money) as DeductionAmountOnDailyWages
  from (
Select *, Row_number() over (partition by slab, attendance_EmpNo order by attendance_no, slab) as RowNum
 from #Attendance a
	join (select distinct s.Slab, s.StartTime, s.EndTime from #Slabs s) s
		on Cast(a.Attendance_InTime as time) between s.StartTime and s.EndTime) x
join #Slabs s
	on x.slab = s.Slab
	and x.RowNum = s.SeqNum
order by attendance_no
1 Like

Many Many Thanks for your help.

Hi,
Many Many Thanks for your reply, I added one column AttendanceType, below is the Table query.

CREATE TABLE [dbo].[Attendance](
	[Attendance_No] [numeric](18, 0) NULL,
	[Attendance_Date] [date] NULL,
	[Attendance_EmpNo] [numeric](18, 0) NULL,
	[Attendance_DailyWages] [numeric](18, 2) NULL,
	[Attendance_InTime] [datetime] NULL,
	[Attendance_OutTime] [datetime] NULL,
	[AttendanceType] [nvarchar](50) NULL
) ON [PRIMARY]

GO

and New Data is Given below.

INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime],AttendanceType)
     VALUES (1,'2022-01-01',1,20.5,'2022-01-01 08:17 AM','2022-01-01 03:30 PM','OD')
     
     INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime],AttendanceType)
     VALUES (2,'2022-01-01',1,20.5,'2022-01-01 08:20 AM','2022-01-01 03:30 PM','OD')
     
     INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime],AttendanceType)
     VALUES (3,'2022-01-01',1,20.5,'2022-01-01 08:40 AM','2022-01-01 03:30 PM','OD')
     
     INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime],AttendanceType)
     VALUES (4,'2022-01-01',1,20.5,'2022-01-01 08:50 AM','2022-01-01 03:30 PM','OD')
     
     INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime],AttendanceType)
     VALUES (5,'2022-01-01',1,20.5,'2022-01-01 08:52 AM','2022-01-01 03:30 PM','OD')
     
      INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime],AttendanceType)
     VALUES (6,'2022-01-01',2,20.5,'2022-01-01 08:17 AM','2022-01-01 03:30 PM','OD')
     
     INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime],AttendanceType)
     VALUES (7,'2022-01-01',2,20.5,'2022-01-01 08:20 AM','2022-01-01 03:30 PM','OD')
     
     INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime],AttendanceType)
     VALUES (8,'2022-01-01',2,20.5,'2022-01-01 08:40 AM','2022-01-01 03:30 PM','OD')
     
     INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime],AttendanceType)
     VALUES (9,'2022-01-01',2,20.5,'2022-01-01 08:50 AM','2022-01-01 03:30 PM','OD')
     
     INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime],AttendanceType)
     VALUES (10,'2022-01-01',2,20.5,'2022-01-01 08:52 AM','2022-01-01 03:30 PM','OD')
     
     INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime],AttendanceType)
     VALUES (11,'2022-01-06',1,20.5,NULL,NULL,'AB')
     
          INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime],AttendanceType)
     VALUES (12,'2022-01-06',2,20.5,NULL,NULL,'AB')
     
GO

The Query is looking for

If AttendanceType is "AB" then deduct full day otherwise the deduction will be on IN Time.

below is the output looking for.

Attendance_No Attendance_Date Type Attendance_EmpNo Attendance_DailyWages Attendance_InTime Attendance_OutTime Slab Slab Deduction Apply Deduction Amount On Daily Wages
1 01-01-2022 OD 1 20.5 01-01-2022 08:17 01-01-2022 03:30 Slab-A 1st Time-5% 1.025
2 02-01-2022 OD 1 20.5 01-01-2022 08:20 01-01-2022 03:30 Slab-A 2nd Time-10% 2.05
3 03-01-2022 OD 1 20.5 01-01-2022 08:40 01-01-2022 03:30 Slab-B 1st Time-15% 3.075
4 04-01-2022 OD 1 20.5 01-01-2022 08:50 01-01-2022 03:30 Slab-B 2nd Time-25% 5.125
5 05-01-2022 OD 1 20.5 01-01-2022 08:52 01-01-2022 03:30 Slab-B 3rd Time-50% 10.25
6 01-01-2022 OD 2 20.5 01-01-2022 08:17 01-01-2022 03:30 Slab-A 1st Time-5% 1.025
7 02-01-2022 OD 2 20.5 01-01-2022 08:20 01-01-2022 03:30 Slab-A 2nd Time-10% 2.05
8 03-01-2022 OD 2 20.5 01-01-2022 08:40 01-01-2022 03:30 Slab-B 1st Time-15% 3.075
9 04-01-2022 OD 2 20.5 01-01-2022 08:50 01-01-2022 03:30 Slab-B 2nd Time-25% 5.125
10 05-01-2022 OD 2 20.5 01-01-2022 08:52 01-01-2022 03:30 Slab-B 3rd Time-50% 10.25
11 06-01-2022 AB 1 20.5 20.5
12 06-01-2022 AB 2 20.5 20.5

The Image output is

Many Many Thanks once again.

regards,
Basit.

add some conditional statements

select Attendance_No, Attendance_Date, Attendance_EmpNO, Attendance_DailyWages, Attendance_InTime, Attendance_OutTime,
		s.Slab, 
		iif(AttendanceType not in ('AB'),Concat(s.Description,'-', (DeductionPct * 100),'%'), null) as SlabDeductionApply, 
		iif(AttendanceType = 'AB', Attendance_DailyWages, Cast(Attendance_DailyWages * DeductionPct as money)) as DeductionAmountOnDailyWages
  from (
Select *, Row_number() over (partition by slab, attendance_EmpNo order by attendance_no, slab) as RowNum
 from #Attendance a
	left join (select distinct s.Slab, s.StartTime, s.EndTime from #Slabs s) s
		on Cast(a.Attendance_InTime as time) between s.StartTime and s.EndTime
		and a.AttendanceType not in ('AB')
		) x
  left join #Slabs s
	on x.slab = s.Slab
	and x.RowNum = s.SeqNum
order by attendance_no

Really Many Many Thanks really appreciate for your help, I used case statement, below is query.

select Attendance_No, Attendance_Date, Attendance_EmpNO, Attendance_DailyWages, Attendance_InTime, Attendance_OutTime,
		s.Slab, 
		 
		case  when AttendanceType = 'AB' then Attendance_DailyWages else Cast(Attendance_DailyWages * DeductionPct as money) end as DeductionAmountOnDailyWages
  from (
Select *, Row_number() over (partition by slab, attendance_EmpNo order by attendance_no, slab) as RowNum
 from Attendance a
	left join (select distinct s.Slab, s.StartTime, s.EndTime from Slabs s) s
		on Cast(a.Attendance_InTime as time) between s.StartTime and s.EndTime
		and a.AttendanceType not in ('AB')
		) x
  left join Slabs s
	on x.slab = s.Slab
	and x.RowNum = s.SeqNum
order by attendance_no

Now thinking what if employee Leave early.
OutTime Column is there Attendance_OutTime, for out time also need to create the slab.

example below slab.
e.g 1st Time leave early from 03:15 to 03:30 deduct 5%
e.g 2nd Time leave early from 03:15 to 03:30 deduct 15%

then how to combine in query please help on this.

Many Thanks once again.

regards
Basit.

if you supply some data with some rules around it, we can help. You didn't mention anything about the OutTime. Are there any other criteria you would be using?

Many Many Thanks for your reply.

Below is the Data

INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime],AttendanceType)
     VALUES (1,'2022-01-01',1,20.5,'2022-01-01 08:17 AM','2022-01-01 03:16 PM','OD')
     
     INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime],AttendanceType)
     VALUES (2,'2022-01-01',1,20.5,'2022-01-01 08:20 AM','2022-01-01 03:17 PM','OD')
     
     INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime],AttendanceType)
     VALUES (3,'2022-01-01',1,20.5,'2022-01-01 08:40 AM','2022-01-01 03:18 PM','OD')
     
     INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime],AttendanceType)
     VALUES (4,'2022-01-01',1,20.5,'2022-01-01 08:50 AM','2022-01-01 03:00 PM','OD')
     
     INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime],AttendanceType)
     VALUES (5,'2022-01-01',1,20.5,'2022-01-01 08:52 AM','2022-01-01 03:02 PM','OD')
     
      INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime],AttendanceType)
     VALUES (6,'2022-01-01',2,20.5,'2022-01-01 08:17 AM','2022-01-01 03:30 PM','OD')
     
     INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime],AttendanceType)
     VALUES (7,'2022-01-01',2,20.5,'2022-01-01 08:20 AM','2022-01-01 03:30 PM','OD')
     
     INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime],AttendanceType)
     VALUES (8,'2022-01-01',2,20.5,'2022-01-01 08:40 AM','2022-01-01 03:30 PM','OD')
     
     INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime],AttendanceType)
     VALUES (9,'2022-01-01',2,20.5,'2022-01-01 08:50 AM','2022-01-01 03:30 PM','OD')
     
     INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime],AttendanceType)
     VALUES (10,'2022-01-01',2,20.5,'2022-01-01 08:52 AM','2022-01-01 03:30 PM','OD')
     
     INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime],AttendanceType)
     VALUES (11,'2022-01-06',1,20.5,NULL,NULL,'AB')
     
          INSERT INTO [Attendance]([Attendance_No],[Attendance_Date],[Attendance_EmpNo],[Attendance_DailyWages],[Attendance_InTime],[Attendance_OutTime],AttendanceType)
     VALUES (12,'2022-01-06',2,20.5,NULL,NULL,'AB')
     
GO

slab query is below.

Create table Slabs (Slab char(1), Description varchar(10), SeqNum int, StartTime time, EndTime time, DeductionPct numeric(3,2))
insert into Slabs values
('A','1st Time',1,'08:15','08:30', .05),
('A','2nd Time',2,'08:15','08:30', .10),
('A','3rd Time',3,'08:15','08:30', .15),
('A','4th Time',4,'08:15','08:30', 1.00),
('B','1st Time',1,'08:31','09:00', .15),
('B','2nd Time',2,'08:31','09:00', .25),
('B','3rd Time',3,'08:31','09:00', .50),
('B','4th Time',4,'08:31','09:00', 1.00),

('C','1st Time',1,'03:00','03:15', .05),
('C','2nd Time',2,'03:00','03:15', .10),

('D','1st Time',1,'03:16','03:29', .05),
('D','2nd Time',2,'03:16','03:29', .10),
('D','3rd Time',3,'03:16','03:29', .15)

Looking for below output.

Attendance_No Attendance_Date Type Attendance_EmpNo Attendance_DailyWages Attendance_InTime Attendance_OutTime Slab Slab Deduction Apply Deduction Amount On Daily Wages Early Ded. Slab Deduction Amount Early Out
1 01-01-2022 OD 1 20.5 01-01-2022 08:17 01-01-2022 15:16 Slab-A 1st Time-5% 1.025 SlabD-5% 1st Time 1.025
2 02-01-2022 OD 1 20.5 01-01-2022 08:20 01-01-2022 15:17 Slab-A 2nd Time-10% 2.05 SlabD-10% 2nd Time 2.05
3 03-01-2022 OD 1 20.5 01-01-2022 08:40 01-01-2022 15:18 Slab-B 1st Time-15% 3.075 SlabD-15% 3rd time 3.075
4 04-01-2022 OD 1 20.5 01-01-2022 08:50 01-01-2022 15:00 Slab-B 2nd Time-25% 5.125 SlabC-5% 1st Time 1.025
5 05-01-2022 OD 1 20.5 01-01-2022 08:52 01-01-2022 15:02 Slab-B 3rd Time-50% 10.25 SlabC-10% 2nd Time 2.05
6 01-01-2022 OD 2 20.5 01-01-2022 08:17 01-01-2022 03:30 Slab-A 1st Time-5% 1.025 0
7 02-01-2022 OD 2 20.5 01-01-2022 08:20 01-01-2022 03:30 Slab-A 2nd Time-10% 2.05 0
8 03-01-2022 OD 2 20.5 01-01-2022 08:40 01-01-2022 03:30 Slab-B 1st Time-15% 3.075 0
9 04-01-2022 OD 2 20.5 01-01-2022 08:50 01-01-2022 03:30 Slab-B 2nd Time-25% 5.125 0
10 05-01-2022 OD 2 20.5 01-01-2022 08:52 01-01-2022 03:30 Slab-B 3rd Time-50% 10.25 0
11 06-01-2022 AB 1 20.5 20.5 0
12 06-01-2022 AB 2 20.5 20.5 0

Below result in image.

Many Thanks once again.

regards,
Basit.

The data in Slabs is incorrect, you need to use 24hr time to get this to work

Here's the change I made to that

Create table #Slabs (Slab char(1), Description varchar(10), SeqNum int, StartTime time, EndTime time, DeductionPct numeric(3,2))
insert into #Slabs values
('A','1st Time',1,'08:15','08:30', .05),
('A','2nd Time',2,'08:15','08:30', .10),
('A','3rd Time',3,'08:15','08:30', .15),
('A','4th Time',4,'08:15','08:30', 1.00),
('B','1st Time',1,'08:31','09:00', .15),
('B','2nd Time',2,'08:31','09:00', .25),
('B','3rd Time',3,'08:31','09:00', .50),
('B','4th Time',4,'08:31','09:00', 1.00),

('C','1st Time',1,'15:00','15:15', .05),
('C','2nd Time',2,'15:00','15:15', .10),
('D','1st Time',1,'15:16','15:29', .05),
('D','2nd Time',2,'15:16','15:29', .10),
('D','3rd Time',3,'15:16','15:29', .15)



select Attendance_No, Attendance_Date, Attendance_EmpNO, Attendance_DailyWages, Attendance_InTime, Attendance_OutTime,
		iif(AttendanceType not in ('AB'),Concat(s.Description,'-', (s.DeductionPct * 100),'%'), null) as SlabDeductionApply, 
		iif(AttendanceType = 'AB', Attendance_DailyWages, Cast(Attendance_DailyWages * s.DeductionPct as money)) as DeductionAmountOnDailyWages,
		iif(SlabOut is null,'',Concat(s1.Description,'-', (s1.DeductionPct * 100),'%')) as EarlyDedSlab,
		iif(SlabOut is null, 0, Cast(Attendance_DailyWages * s1.DeductionPct as money)) as DeductionAmountEarlyOut

  from (
Select *, Row_number() over (partition by slab, attendance_EmpNo order by attendance_no, slab) as RowNum
 from #Attendance a
	left join (select distinct s.Slab, s.StartTime, s.EndTime from #Slabs s) s
		on Cast(a.Attendance_InTime as time) between s.StartTime and s.EndTime
		and a.AttendanceType not in ('AB')
	left join (select distinct s1.Slab as SlabOut, s1.StartTime as StartTimeOut, s1.EndTime as EndTimeOut from #Slabs s1) o
		on Cast(a.Attendance_outTime as time) between o.StartTimeOut and o.EndTimeOut
		and a.AttendanceType not in ('AB')
		) x
  left join #Slabs s
	on x.slab = s.Slab
	and x.RowNum = s.SeqNum
  left join #Slabs s1
	on x.slabOut = s1.Slab
	and x.RowNum = s.SeqNum
order by attendance_no

Many Thanks for reply, the early out is showing wrong.

select Attendance_No, Attendance_Date, Attendance_EmpNO, Attendance_DailyWages, Attendance_InTime, Attendance_OutTime,
		case when AttendanceType not in ('AB') then s.[Description] +'-'+ s.slab else null end as SlabDeductionApply, 
		case when  AttendanceType = 'AB'then Attendance_DailyWages else Cast(Attendance_DailyWages * s.DeductionPct as money) end as DeductionAmountOnDailyWages,
		case when SlabOut is null then ''else s1.Description +'-'+ s1.slab end as EarlyDedSlab,
		case when SlabOut is null then 0 else Cast(Attendance_DailyWages * s1.DeductionPct as money) end as DeductionAmountEarlyOut

  from (
Select *, Row_number() over (partition by slab, attendance_EmpNo order by attendance_no, slab) as RowNum
 from Attendance a
	left join (select distinct s.Slab, s.StartTime, s.EndTime from #Slabs s) s
		on Cast(a.Attendance_InTime as time) between s.StartTime and s.EndTime
		and a.AttendanceType not in ('AB')
	left join (select distinct s1.Slab as SlabOut, s1.StartTime as StartTimeOut, s1.EndTime as EndTimeOut from #Slabs s1) o
		on Cast(a.Attendance_outTime as time) between o.StartTimeOut and o.EndTimeOut
		and a.AttendanceType not in ('AB')
		) x
  left join #Slabs s
	on x.slab = s.Slab
	and x.RowNum = s.SeqNum
  left join #Slabs s1
	on x.slabOut = s1.Slab
	and x.RowNum = s1.SeqNum
order by attendance_no

below is the result.

Attendance_No Attendance_Date Attendance_EmpNO Attendance_DailyWages Attendance_InTime Attendance_OutTime SlabDeductionApply DeductionAmountOnDailyWages EarlyDedSlab DeductionAmountEarlyOut
1 2022-01-01 1 20.50 2022-01-01 08:17:00.000 2022-01-01 15:16:00.000 1st Time-A 1.0250 1st Time-D 1.025
2 2022-01-01 1 20.50 2022-01-01 08:20:00.000 2022-01-01 15:17:00.000 2nd Time-A 2.0500 2nd Time-D 2.05
3 2022-01-01 1 20.50 2022-01-01 08:40:00.000 2022-01-01 15:18:00.000 1st Time-B 3.0750 1st Time-D 1.025
4 2022-01-01 1 20.50 2022-01-01 08:50:00.000 2022-01-01 15:00:00.000 2nd Time-B 5.1250 2nd Time-C 2.05
5 2022-01-01 1 20.50 2022-01-01 08:52:00.000 2022-01-01 15:02:00.000 3rd Time-B 10.2500 NULL NULL
6 2022-01-01 2 20.50 2022-01-01 08:17:00.000 2022-01-01 15:30:00.000 1st Time-A 1.0250 0.00
7 2022-01-01 2 20.50 2022-01-01 08:20:00.000 2022-01-01 15:30:00.000 2nd Time-A 2.0500 0.00
8 2022-01-01 2 20.50 2022-01-01 08:40:00.000 2022-01-01 15:30:00.000 1st Time-B 3.0750 0.00
9 2022-01-01 2 20.50 2022-01-01 08:50:00.000 2022-01-01 15:30:00.000 2nd Time-B 5.1250 0.00
10 2022-01-01 2 20.50 2022-01-01 08:52:00.000 2022-01-01 15:30:00.000 3rd Time-B 10.2500 0.00
11 2022-01-06 1 20.50 NULL NULL NULL 20.5000 0.00
12 2022-01-06 2 20.50 NULL NULL NULL 20.5000 0.00

Image out is below.


Many Thanks once again.
regards,
Basit

Many Thanks,
Any update regarding above, the result is not correct because of Row_Number()
regards,
Basit.