Calculate total working hours in SQL Server

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

Hi,

I have table like below in SQL Server

233955-image.png

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

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
0 comments No comments
{count} votes

Accepted answer
  1. LiHongMSFT-4306 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


    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


0 additional answers

Sort by: Most helpful