15,947,411 members
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

## Solution 1

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.