Sql query need to develop

Akhter HUssain

7y764SQL

I have table in which INTIme and OUTTIme are coming in a column now I want procedure in which

I want that to separate INTIME and OutTime into separate columns then hours difference will come from INTIME and OUTTIME ,

Duty hours define in Employee table will pick from employee master file table.

Day will be calculated if Hours will be equal to Duty Hours and greater then duty hours ,day will be = 1

OT Hours will come from Hours minus Duty Hours,

OT Amount will calculate, if Employee entitled for availing OT In employee master file then their amount will be calculated otherwise OT amount will not be calculated.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Piyush Pansuriya7y ago
Accepted answer
Hello Akhter,
Can you please share database table with your data for more clearity?
Piyush Pansuriya7y ago
Hello,
Try this :
SELECT EmpID,CAST(DateTime as date), MAX(CASE WHEN INOUT = 1 THEN Datetime END) AS INtime,
MAX(CASE WHEN INOUT = 2 THEN Datetime END) AS Outtime,
DATEDIFF(Hour, MIN(Datetime), MAX(Datetime)) AS Hours,
CASE WHEN DATEDIFF(Hour, MIN(Datetime),
MAX(Datetime)) >= 8 THEN 1 WHEN DATEDIFF(Hour, MIN(Datetime),
MAX(Datetime)) = 0 THEN 0 WHEN DATEDIFF(Hour, MIN(Datetime),
MAX(Datetime)) >= 6 THEN 0.5 END AS Day,
CASE WHEN DATEDIFF(Hour, MIN(Datetime),
MAX(Datetime)) >= 8 THEN DATEDIFF(Hour, MIN(Datetime),
MAX(Datetime)) - 8 ELSE 0 END AS OT,
CASE WHEN EmployeeDetails.OTAllow = 'Yes' THEN CASE WHEN DATEDIFF(Hour, MIN(Datetime),MAX(Datetime)) >= 8
THEN (DATEDIFF(Hour, MIN(Datetime) ,MAX(Datetime)) - 8) * 100 ELSE 0 END ELSE 0 END AS [ OT Amount]
FROM MachineAttendance
LEFT OUTER JOIN EmployeeDetails.EmpID = MachineAttendance.EmpID
LEFT OUTER JOIN EmployeeAttedance on EmployeeAttedance.EmpID = EmployeeDetails.EmpID
group by EmpID,CONVERT(date,DateTime)

Akhter HUssain7y ago

Employee Table

EmpCode	Name	OTAllow
001	Akhter	No
002	Hussain	Yes
003	Irfan	no

Attendance Machine Table

Empcode	Date	Status
001	1-1-2019 08:00	1
001	1-1-2019 20:00	2
002	2-1-2019 08:00	1
002	2-1-2019 20:00	2

Employee Attendane Table

Empcode	INTIME	OUTIME	Hours	Day	DutyHours	OT Rate	OT Hours	OT Amount
001	1-1-2019 08:00	1-1-2019 20:00	12	1	8	100	4	0
002	2-1-2019 08:00	2-1-2019 20:00	12	1	8	100	4	100

In Employee Attendance i want that OT amount will calculate if inemployee table,employee will allow to avail Overtime then Overtime will calculate other wise will be 0,,herei tried but not working

SELECT EmpID,CAST(DateTime as date), MAX(CASE WHEN INOUT = 1 THEN Datetime END) AS INtime,

MAX(CASE WHEN INOUT = 2 THEN Datetime END) AS Outtime,

DATEDIFF(Hour, MIN(Datetime), MAX(Datetime)) AS Hours,

CASE WHEN DATEDIFF(Hour, MIN(Datetime),

MAX(Datetime)) >= 8 THEN 1 WHEN DATEDIFF(Hour, MIN(Datetime),

MAX(Datetime)) = 0 THEN 0 WHEN DATEDIFF(Hour, MIN(Datetime),

MAX(Datetime)) >= 6 THEN 0.5 END AS Day,

CASE WHEN DATEDIFF(Hour, MIN(Datetime),

MAX(Datetime)) >= 8 THEN DATEDIFF(Hour, MIN(Datetime),

MAX(Datetime)) - 8 ELSE 0 END AS OT,

CASE WHEN EmployeeDetails.OTEntitled='Yes' THEN

DATEDIFF(Hour, MIN(Datetime),MAX(Datetime)) >= 8

THEN (DATEDIFF(Hour, MIN(Datetime) ,MAX(Datetime)) - 8) * 100 ELSE 0 END AS [ OT Amount]

FROM MachineAttendance

Join EmpID on EmployeeAttedance.EmpID = EmployeeDetails.EmpID

group by EmpID,CONVERT(date,DateTime)