August 1, 2006 at 5:32 am
I'm using a stored procedure to insert equipment fault records into a table that I would like to modify so that depending on the time the record was created, a "shift" field is populated.
Here is the stored procedure I'm currently using:
ALTER PROCEDURE [dbo].[p_dtu_Store_Line_Fault_Data]
-- Add the parameters for the stored procedure here
@AssetID int,
@Timestamp datetime,
@FaultCode int,
@State int
AS
BEGIN
SET NOCOUNT ON;
IF @State = 3
BEGIN
INSERT LineFaultData (FaultCode, AssetID, StartTime, Duration)
VALUES (@FaultCode, @AssetID, @Timestamp, 0)
END
IF @State <> 3
BEGIN
DECLARE @Count int
SET @Count = (SELECT Count(*) FROM LineFaultData WHERE AssetID = @AssetID AND Duration = 0)
IF @Count <> 0
BEGIN
DECLARE @StartTime datetime
SET @StartTime = (SELECT Top 1 StartTime FROM LineFaultData WHERE AssetID = @AssetID and Duration = 0)
UPDATE LineFaultData
SET Duration = DateDiff(s,@StartTime, @Timestamp)
WHERE AssetID = @AssetID and Duration = 0 and StartTime = @StartTime
END
END
END
If the start time of the record is between 7am - 3pm, I'd like to add "Days" to a shift column. If between 3pm - 11pm, it would be "Afternoons" and 11pm - 7am would be "Nights".
Is modifying the SP the correct method ? Any advice on how to accomplish this would be appreciated.
Thank-you
Jeff
August 2, 2006 at 1:34 am
Is this what you are looking for?
INSERT LineFaultData (FaultCode, AssetID, StartTime, Duration, Shift)
VALUES (@FaultCode, @AssetID, @Timestamp, 0
, CASE WHEN DATEPART(hh,@Timestamp) BETWEEN 7 AND 15 THEN 'Days'
WHEN DATEPART(hh,@Timestamp) BETWEEN 16 AND 23 THEN 'Afternoons'
ELSE 'Nights' END)
Andy
August 2, 2006 at 1:52 am
You mean something like this? I took the liberty to rewrite and optimize your procedure a little...
ALTER PROCEDURE dbo.p_dtu_Store_Line_Fault_Data
(
@AssetID int,
@Timestamp datetime,
@FaultCode int,
@State int
)
AS
SET NOCOUNT ON
IF @State = 3
INSERT LineFaultData
(
FaultCode,
AssetID,
StartTime,
Duration
)
VALUES (
@FaultCode,
@AssetID,
@Timestamp,
0
)
ELSE
BEGIN
DECLARE @StartTime datetime
SELECT @StartTime = (SELECT Top 1 StartTime FROM LineFaultData WHERE AssetID = @AssetID and Duration = 0)
IF @StartTime IS NOT NULL
UPDATE LineFaultData
SET Duration = DateDiff(s, @StartTime, @Timestamp),
Shift = CASE
WHEN DATEPART(hour, @StartTime) BETWEEN 7 AND 14 THEN 'Days'
WHEN DATEPART(hour, @StartTime) BETWEEN 15 AND 22 THEN 'Afternoons'
ELSE 'Nights'
END
WHERE AssetID = @AssetID
and Duration = 0
and StartTime = @StartTime
END
Good luck!
N 56°04'39.16"
E 12°55'05.25"
August 2, 2006 at 1:58 am
David, you are aware the BETWEEN operator is INCLUSIVE with limits?
In your case, BETWEEN 7 AND 15 is true for hours 07, 08, 09, 10, 11, 12, 13, 14 and 15.
This is from 07:00 to 15:59.
Also, BETWEEN 16 AND 23 is true for hours 16, 17, 18, 19, 20, 21, 22 and 23.
This is from 16:00 to 23:59.
Change to 15 to 14, and 23 to 22.
N 56°04'39.16"
E 12°55'05.25"
August 3, 2006 at 12:42 pm
Thank-you very much ! It worked perfect.
Regards,
Jeff
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply