August 25, 2020 at 3:55 pm
Hello:
I think I might be close here, but I need to return 0 if there is division by zero.
SELECT JobInfo.JobNo, JobInfo.Product, JobInfo.ItemType, JobInfo.Detail, JobInfo.Started, JobInfo.Completed, JobInfo.Engineer, JobInfo.Designer, JobInfo.EstHrs, JobInfo.ShipTo, JobHrs.ActualHrs,
CASE WHEN JobInfo.EstHrs > 0 THEN ((JobInfo.EstHrs / JobHrs.ActualHrs) * 100) AS Efficiency ELSE 0 AS Efficiency
FROM JobInfo INNER JOIN
JobHrs ON JobInfo.JobNo = JobHrs.JobNo
WHERE (JobInfo.Completed BETWEEN @SDate AND @EDate) AND (JobInfo.Completed <> '1979-01-01')
ORDER BY JobInfo.Designer
Current logic produces:
Error in SELECT clause: expression near 'AS'.
Error in SELECT clause: expression near 'FROM'.
Missing FROM clause.
Unable to parse query text.
Thanks..
Steve Anderson
August 25, 2020 at 4:11 pm
your CASE statement needs an END on it before the AS.
That should fix it for you!
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 25, 2020 at 4:13 pm
Try this
SELECT JobInfo.JobNo
,JobInfo.Product
,JobInfo.ItemType
,JobInfo.Detail
,JobInfo.Started
,JobInfo.Completed
,JobInfo.Engineer
,JobInfo.Designer
,JobInfo.EstHrs
,JobInfo.ShipTo
,JobHrs.ActualHrs
,Efficiency = CASE
WHEN JobInfo.EstHrs > 0 THEN
(JobInfo.EstHrs * 100 / JobHrs.ActualHrs)
ELSE
0
END
FROM JobInfo
JOIN JobHrs
ON JobInfo.JobNo = JobHrs.JobNo
WHERE (JobInfo.Completed
BETWEEN @SDate AND @EDate
)
AND (JobInfo.Completed <> '1979-01-01')
ORDER BY JobInfo.Designer;
Not sure why your >0 test is on EstHrs, when you are dividing by ActualHrs, but I'm assuming this logic makes sense to you.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 25, 2020 at 5:50 pm
Typically people use a NULLIF like below, to avoid a CASE statement -- whether you prefer that or not is, of course, up to you:
ISNULL(((JobInfo.EstHrs / NULLIF(JobHrs.ActualHrs, 0)) * 100), 0)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 25, 2020 at 6:30 pm
Hello:
Just one more thing, how to I get a display of fewer decimals? It seems to want to show 8 plus decimals, even if I apply round, just converting them to zero.
I'm about tutorialed out on this.
SELECT JobInfo.JobNo, JobInfo.Product, JobInfo.ItemType, JobInfo.Detail, JobInfo.Started, JobInfo.Completed, JobInfo.Engineer, JobInfo.Designer, JobInfo.EstHrs, JobInfo.ShipTo, JobHrs.ActualHrs, Efficiency = CASE
WHEN JobInfo.EstHrs > 0 THEN
(JobHrs.ActualHrs / JobInfo.EstHrs) * 100 AS NUMERIC(5, 0)
ELSE
0 AS NUMERIC(5, 0)
END
FROM JobInfo INNER JOIN
JobHrs ON JobInfo.JobNo = JobHrs.JobNo
WHERE (JobInfo.Completed BETWEEN @SDate AND @EDate) AND (JobInfo.Completed <> '1979-01-01')
ORDER BY JobInfo.Designer
Thanks
Steve Anderson
August 25, 2020 at 6:36 pm
,Efficiency = CASE
WHEN JobInfo.EstHrs > 0 THEN
CAST((JobInfo.EstHrs * 100 / JobHrs.ActualHrs) AS Decimal(19,2))
ELSE
0
END
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 25, 2020 at 6:48 pm
Thank you!
Steve Anderson
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply