June 25, 2002 at 4:54 pm
I need to calculate results off of calcualted fields.
something like this
SELECT tSchedule.SDG, DateDiff("hh",SampleDate,GETDATE()) AS timein,(ExHT*24) AS ExHrs, (ExHT-timein) AS HT
FROM tSchedule
Works in Access but not in SQL
Ideally I would also want to add a contition on the HT alias
WHERE HT<24
but that's probably too much 🙂
Any ideas alre welcome.
Thanks,
Jakub
June 25, 2002 at 5:06 pm
Hello,
You could try this...
select sgd,
datediff(hour,sampledate,getdate()) as 'TimeIn',
(exht * 24) as 'ExHrs',
(exht - (datediff(hour,sampledate,getdate()))) AS 'HT'
from dbo.tSchedule
I'm not sure of the data types you are using but the above works for me.
Cheers,
Chris
June 25, 2002 at 5:07 pm
And then for the where clause you could use this...
where (exht - (datediff(hour,sampledate,getdate()))) < 24
It works over here.
Cheers,
Chris
June 25, 2002 at 5:48 pm
The basic problem here is that timein is not understood at this level. By replacing timein with the same calulation code it will work. Or you can change you query to a subquery and hanlde the last cal outside like so.
SELECT SDG, timein, ExHrs, (ExHT-timein) AS HT
FROM
(
SELECT tSchedule.SDG, DateDiff("hh",SampleDate,GETDATE()) AS timein,(ExHT*24) AS ExHrs, ExHT
FROM tSchedule
) AS subq1
Then you can easily add a where clause. The only problem is this method may produce the result set slower than if you write all the needed calcs in a non-subquery.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply