Click here to Skip to main content
15,947,411 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi All,

I want to calculate delay time for any work/process. I have shifts timing table just like office timing and it may be 1st, 2nd, or 3rd shift in a day. In between the timing few minute breaks are there for lunch or tea break etc.
One work/Process may be finished within 1st shift or it may go to 2nd, 3rd shift also or work may go to next day any shift also. And I have a standard time for the work/process to complete.
ShiftTime
ID  Shift   FromTime    ToTime  Descriptioin
1   1   05:55   07:55   10 mins tea break
2   1   08:05   09:55   30 min lunch break
3   1   10:25   12:30   10 min tea break
4   1   12:40   14:30   End of 1st Shift.
5   2   15:01   17:00   10 min tea break
6   2   17:10   19:30   30 min lunch break
7   2   20:00   21:50   10 min tea break
8   2   22:00   23:35   End of 2nd Shift.

StandaredTime
ID  Stage   StageName   StandardTime(MINUTE)
1   1   WELD    600
2   2   PAINT   800
3   1   ASSEMBLY    800
4   1   QC  200

So I have to calculate the delay time for one work/process.
Case 1 - Support Today there are only two shift. Process1 got started on 2nd shift at 18:30 and StandardTime is 10 Hours to complete the process and suppose it got finished next day 1st shift. So. Calculation would be something like this.
23:35(End of 2nd shift of first day) - 18:30 (Process starting time) -00:30 mins (Lunch break) -00:10 (mins tea break) = 4:25 mins. Job is done. Next 5:30 mins job will be done on next day 1st shift. Next day calculation would be the same like.
5:55 am + 5:35 (hr job remain) + 30 min (lunch break) + 10 min (tea break). So the work should be finished by 12:00 AM afternoon.
And Holiday also I have to calculate. And I need to add to this holiday to calculate delay.
ID  NoOfShift   Date
1   2       2009-01-01 00:00:00.000
2   1       2009-01-02 00:00:00.000
3   2       2009-01-03 00:00:00.000
4   0       2009-01-04 00:00:00.000
5   2       2009-01-05 00:00:00.000
6   1       2009-01-06 00:00:00.000

Note : NoOfShift 0 means holiday for that day.


I am not getting any idea how to calculate.

Thanks
Shambhu
Posted
Updated 16-Oct-10 12:27pm
v2

1 solution

You are likely to get more help if you learn to use a search engine.

You can use a search engine to find out what a search engine is. :)

Try searching for sql server calculate time difference and start reading till you find something that you understand. Then try it out. If you get a problem then ask here again with the specific problem.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900