Attendance Calculation Based on IN Timing

basit0079 · January 4, 2022, 3:17pm

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.

mike01 · January 4, 2022, 9:11pm

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

basit0079 · January 5, 2022, 10:00am

Many Many Thanks for your help.

basit0079 · January 5, 2022, 10:32am

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.

mike01 · January 5, 2022, 4:01pm

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

basit0079 · January 5, 2022, 4:39pm

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.

mike01 · January 5, 2022, 8:50pm

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?

basit0079 · January 6, 2022, 5:35am

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.

mike01 · January 6, 2022, 4:30pm

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

basit0079 · January 8, 2022, 6:29am

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

basit0079 · January 11, 2022, 3:58am

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