March 13, 2019 at 9:23 am
I need to identify cases where a job runs over its estimated completion time. Code to recreate:
DECLARE @Table TABLE (
RowNum INT
, ExpectedJobTime DECIMAL(12,2)
, LoggedJobTime DECIMAL(12,2)
, RemainingJobTime VARCHAR(24)
)
INSERT INTO @Table (RowNum,ExpectedJobTime,LoggedJobTime)
VALUES
--(1,14.7,10.6) -- Positive value works
(1,5.4,6.73) -- Negative value doesn't work
UPDATE @Table
SET RemainingJobTime = CASE
WHEN LoggedJobTime > ExpectedJobTime
THEN 'Standard Exceeded'
ELSE ExpectedJobTime - LoggedJobTime
END
SELECT *
FROM @Table
If the value of LoggedJobTime is greater than that of ExpectedJobTime then RemainingJobTime should be set to 'Standard Exceeded' otherwise it should be the result of ExpectedJobTime - LoggedJobTime. It works in cases where the result of ExpectedJobTime - LoggedJobTime is positive but fails with 'Error converting data type varchar to numeric.' when that result would be negative. I'm clearly going about this wrong here but am not sure how to correct it. Can anyone show me the proper way to do this? Thanks in advance for looking at it.
March 13, 2019 at 9:34 am
The remainingJobtime field is varchar
So you have to convert number into string
Try this
UPDATE #table SET
RemainingJobTime = CASE THEN LoggedJobTime > ExpectedJobTime THEN 'Standard Exceeded'
ELSE
CAST(ExpectedJobTime - LoggedJobTime AS VARCHAR(10))
END
FROM #table AS t
March 13, 2019 at 10:50 am
That does the trick. Thanks!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply