PLEASE HELP!!!!
Needing help with developing a stored proc for the following:
Production crews work at the following times:
CREW A = 7:30AM to 3:30PM
CREW B = 3:30PM to 11:30PM
CREW C = 11:30PM to 7:30AM
- There are Record_Begin_Da te and Record_End_Date time/date fields in the database.
- There is logic in place that figures out which shift WAS Working at each time
The logic is briefly described below:
Record_Begin_Da te
- Determine which shift for the time
- If Record_Begin_Da te is Between 7:30AM to 3:30PM then it's Shift A
- If Record_Begin_Da te is Between 3:30PM to 11:30PM then it's Shift B
- If Record_Begin_Da te is Between 11:30PM to 7:30AM then it's Shift C
Record_End_Date
- Determine which shift for the time
- If Record_End_Date is Between 7:30AM to 3:30PM then it's Shift A
- If Record_End_Date is Between 3:30PM to 11:30PM then it's Shift B
- If Record_End_Date is Between 11:30PM to 7:30AM then it's Shift C
-------------------------------------------------------------------------------------------------------
Some of these records CROSS OVER shifts. Let's say the Record_Begin_Da te is at 2/01/2007 7:30AM and the Record_End_Date is 3:45PM. That means that the record crosses over into another shift.
We need to CHARGE each shift with their time WITHIN the Record_Begin_Da te and Record_End_Date .
So it will go something like this:
Record_Begin_Da te 2/1/07 7:50AM
Record_End_Date 2/1/07 3:45PM
1. Logic will evaluate to see if the Record_Begin_Da te or Record_End_Date crosses over shifts.
2. Figure out how much time of that goes to each shift.
Something like: Shift A had 20 minutes and Shift B had 15 minutes
3. Re-calculate a few things
4. insert new records in the database.
Your help is greatly greatly appreciated with this problem!!!
Needing help with developing a stored proc for the following:
Production crews work at the following times:
CREW A = 7:30AM to 3:30PM
CREW B = 3:30PM to 11:30PM
CREW C = 11:30PM to 7:30AM
- There are Record_Begin_Da te and Record_End_Date time/date fields in the database.
- There is logic in place that figures out which shift WAS Working at each time
The logic is briefly described below:
Record_Begin_Da te
- Determine which shift for the time
- If Record_Begin_Da te is Between 7:30AM to 3:30PM then it's Shift A
- If Record_Begin_Da te is Between 3:30PM to 11:30PM then it's Shift B
- If Record_Begin_Da te is Between 11:30PM to 7:30AM then it's Shift C
Record_End_Date
- Determine which shift for the time
- If Record_End_Date is Between 7:30AM to 3:30PM then it's Shift A
- If Record_End_Date is Between 3:30PM to 11:30PM then it's Shift B
- If Record_End_Date is Between 11:30PM to 7:30AM then it's Shift C
-------------------------------------------------------------------------------------------------------
Some of these records CROSS OVER shifts. Let's say the Record_Begin_Da te is at 2/01/2007 7:30AM and the Record_End_Date is 3:45PM. That means that the record crosses over into another shift.
We need to CHARGE each shift with their time WITHIN the Record_Begin_Da te and Record_End_Date .
So it will go something like this:
Record_Begin_Da te 2/1/07 7:50AM
Record_End_Date 2/1/07 3:45PM
1. Logic will evaluate to see if the Record_Begin_Da te or Record_End_Date crosses over shifts.
2. Figure out how much time of that goes to each shift.
Something like: Shift A had 20 minutes and Shift B had 15 minutes
3. Re-calculate a few things
4. insert new records in the database.
Your help is greatly greatly appreciated with this problem!!!