MS SQL 2000 INSERT Record
Hi,
i have a strange scenario that means i have to createa duplicate record and then update both when a certain criteria is met.
The database is for the company i work for, and we create coils of wire.
We have Data Capture Database that tells us the Start Time and End Time of a Coil.
We also have A shift pattern which is 06:00 to 18:00 and 18:00 to 06:00.
The problem i have is when a Coil goes over a shift. so for instance starts at 05:00 and finishes at 07:00.
i need to build a Query in query analyser OR a Trigger which ever is the best way around, to look at the start time and end time and if the start time is below Shift end time but endtime is after shift end time, Duplicate the record and update the data accordingly.
The updates i need are to:
1: remove data from the coil start weight and coil finish weight so this is not calculated on this shift.
2: alter the Coil end time in the original record to shift end time
3: Alter the Coil Start Time to the Shift start time + 00:01
Hope this makes sense
Regards
Steve Dyson
[1133 byte] By [
snoopsterg] at [2007-11-20 11:25:39]
# 2 Re: MS SQL 2000 INSERT Record
Thanks for the reply,
The data in imported from an access database query, So maybe i can write something in there that does it first.
But anyway, The data capture system just inserts a row with the following fields:
Batch Name, Batch Start, Batch End, Coil Start Weight, Coil End Weight, Product Code, Shift colour, Shift Operator, Works Order No and a few other fields(which are insignificant but need duplicating).
The Fields that will need updating are:
Record 1 (original Post from Data Capture)
Batch Name (leave alone), Batch Start (Leave Alone), Batch End (update to Shift end time), Coil Start Weight (update To 0.00), Coil End Weight (update to 0.00), Shift colout (Update To Next Shift Colour), Shift Operator(Update To Next Operator).
This would then allow my reporting to accuratly use the data
Regards
Steve
# 3 Re: MS SQL 2000 INSERT Record
Here's how you do it with one query:
-- OK batches.
SELECT
[Batch Name], [Batch Start], [Batch End], [Coil Start Weight], [Coil End Weight],
[Product Code], [Shift Colour], [Shift Operator], [Works Order No]
FROM Batches
WHERE
(
DATEPART(hour, [Batch Start]) >= 6
AND
DATEPART(hour, [Batch End]) < 18
)
OR
(
(
DATEPART(hour, [Batch Start]) < 6
OR
DATEPART(hour, [Batch Start]) >= 18
)
AND
(
DATEPART(hour, [Batch End]) < 6
OR
DATEPART(hour, [Batch End]) >= 18
)
)
UNION ALL
-- Batches that exceed shift 6-18.
SELECT
[Batch Name], [Batch Start], DATEADD(hour, 17, DATEADD(minute, 59, CONVERT(char(10), [Batch End], 101))), 0, 0,
[Product Code], [Shift Colour], [Shift Operator], [Works Order No]
FROM Batches
WHERE
DATEPART(hour, [Batch Start]) >= 6
AND
DATEPART(hour, [Batch Start]) < 18
AND
(
DATEPART(hour, [Batch End]) < 6
OR
DATEPART(hour, [Batch End]) >= 18
)
UNION ALL
SELECT
[Batch Name], DATEADD(hour, 18, CONVERT(char(10), [Batch Start], 101)), [Batch End], [Coil Start Weight], [Coil End Weight],
[Product Code], [Shift Colour], [Shift Operator], [Works Order No]
FROM Batches
WHERE
DATEPART(hour, [Batch Start]) >= 6
AND
DATEPART(hour, [Batch Start]) < 18
AND
(
DATEPART(hour, [Batch End]) < 6
OR
DATEPART(hour, [Batch End]) >= 18
)
UNION ALL
-- Batches that exceed shift 18-6.
SELECT
[Batch Name], [Batch Start], DATEADD(hour, 5, DATEADD(minute, 59, CONVERT(char(10), [Batch End], 101))), 0, 0,
[Product Code], [Shift Colour], [Shift Operator], [Works Order No]
FROM Batches
WHERE
(
DATEPART(hour, [Batch Start]) < 6
OR
DATEPART(hour, [Batch Start]) >= 18
)
AND
DATEPART(hour, [Batch End]) >= 6
AND
DATEPART(hour, [Batch End]) < 18
UNION ALL
SELECT
[Batch Name], DATEADD(hour, 6, CONVERT(char(10), [Batch Start], 101)), [Batch End], [Coil Start Weight], [Coil End Weight],
[Product Code], [Shift Colour], [Shift Operator], [Works Order No]
FROM Batches
WHERE
(
DATEPART(hour, [Batch Start]) < 6
OR
DATEPART(hour, [Batch Start]) >= 18
)
AND
DATEPART(hour, [Batch End]) >= 6
AND
DATEPART(hour, [Batch End]) < 18
It's not quite as bad as it looks, because I've added a lot of space so that it's easier to follow what happens. What it does is that it first selects all batches that don't go into the next shift. Then it selects the batches that exceed shift 6-18, and cuts off the end time to 17:59. Then it selects the same rows again, but cuts the start time to 06:00 instead. Then it repeats the last two steps for shift 18-6.
The queries that cut the end time have coil weight set to 0, while the queries that cut the start time have the original weight values.
Note that if a batch can last 12 hours or longer, you will need a more complex query and at that point you should really consider going over to a stored procedure.
