# Calculate total working hours in SQL Server

21 Reputation points
2022-08-23T07:59:14.157+00:00

Hi,

I have table like below in SQL Server

I need to calculate production hours based on 'created' date. For every order for every product and for every operator, his/her effective hours will be calculated as below.

Step 1:

a) When Old_Status = 'New' and New_Status = 'WIP', then we note the 'Created' date
(we need to consider oldest created date amongst multiple entries)

b) When Old_Status = 'Wait'/'WIP' and New_Status = 'Completed',
then we note the 'Created' date
(we need to consider latest created date amongst multiple entries)

c)Now, we need to take the difference of above two dates and save it as : Total Hours

Step 2:

Similarly,

a) When Old_Status = 'WIP' and New_Status = 'Wait', then we note the 'Created' date
(we need to consider first created date amongst multiple entries)

b) When Old_Status = 'Wait' and New_Status = 'WIP', then we note the 'Created' date
(we need to consider first created date amongst multiple entries)

c) Take the difference of above dates and save as : Wait time

d) Repeat above step for multiple WIP -> Wait and Wait -> WIP.
e) Add all the Wait time and save as : Total Wait

Step 3:

Calculate, Production hours = Total Hours (from step 1) - Total Wait (from Step 2)

Regards, Jyoti

Azure SQL Database
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,582 questions

1. 24,926 Reputation points
2022-08-23T09:32:27.493+00:00

Hi @Jyoti Pawar
Try this query:

``````SELECT Product_Name,Operater_Name
,DATEDIFF(HH,MIN(CASE WHEN Old_Status='NEW' THEN Created END),MAX(CASE WHEN New_Status='Completed' THEN Created END))AS Total_Hours
,DATEDIFF(HH,MIN(CASE WHEN Old_Status='WIP' THEN Created END),MAX(CASE WHEN New_Status='WIP' THEN Created END))AS Wait_Hours
,DATEDIFF(HH,MIN(CASE WHEN Old_Status='NEW' THEN Created END),MAX(CASE WHEN New_Status='Completed' THEN Created END))
-DATEDIFF(HH,MIN(CASE WHEN Old_Status='WIP' THEN Created END),MAX(CASE WHEN New_Status='WIP' THEN Created END))AS Total_Wait
FROM Table_Name
GROUP BY Product_Name,Operater_Name
``````

Best regards,
LiHong