SUM(T.HOURS_WORKED) AS WHOURS, Sum(L.HOURS_ESTIMATED) -
SUM(T.HOURS_WORKED) as HoursLeft
FROM LOG L inner join Track T on L.Log_ID = T.Log_ID
Where L.FINISH_DT Is Null AND L.APPROVAL='A'
GROUP BY L.Log_ID, L.TECH_ASSIGNED
-----Original Message-----
From: NANCY GRAHAM [mailto:[EMAIL PROTECTED]
Sent: Monday, January 19, 2004 3:49 PM
To: SQL
Subject: Mathematics
I have two tables LOG, TRACK. The LOG contains LOG_ID, TECH_ASSIGNED and
the number of HOURS_ESTIMATED to complete the log . The TRACK table
keeps HOURS_WORKED, LOG_ID and TECH_ASSIGNED.
I need the hours assigned to each tech and the hours actually worked by
each tech. Then I need to find the hours remaining to be worked by each
tech by subtracting HOURS_WORKED FROM HOURS_ESTIMATED.
I have this working in ACCESS with a query of a query. How do I get it
to work in SQL in CF5.0?
SELECT TECH_ASSIGNED, LOG_ID SUM(HOURS_ESTIMATED) as SHOURS
FROM LOG
WHERE LOG_ID IN
(
SELECT LOG_ID, SUM(HOURS_WORKED) AS WHOURS
FROM TRACK
WHERE LOG_ID = LOG_ID
)
AND FINISH_DT Is Null AND APPROVAL='A'
GROUP BY TECH_ASSIGNED
Thanks for any assistance.
________________________________
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
