Calculate total working hours in SQL Server

Question

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)

Thank you in advance!

Regards, Jyoti

Answer 1

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

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
email-notifications