February 12, 2007 at 8:30 am
I've been working on a query all weekend, it's almost working, but I have one issue with a LEFT OUTER JOIN.
I need to join two tables to get the results I need for one column(totExpenses), but the join is causing the results to be multiplied in another column(totMileage) if the left outer join returns more than one result.
For example, if the left outer join returns 3 rows of data, then my other column(totMileage) will be multipled by 3.
But I need the join to return those results so the totExpenses column is accurate, but it is throwing off my totMileage column.
Am I missing something here? -Thanks
Here is the query:
select users.Uname, users.UnameFirst, users.UnameLast,
round(sum(workcompleted.WCmileage), 2) as totMileage,
round(sum(workexpense.WXamount), 2) as totExpenses
from workrequest, worktask, users,
workcompleted LEFT OUTER JOIN workexpense on workexpense.WXlinkItemID = workcompleted.WCid
where workcompleted.WCcompletedDate between '1/2/2007' and '2/12/2007'
and workcompleted.WCcompletedDate is not null
and workcompleted.WCemployeeID = users.Uname
and workcompleted.WClinkItemID = worktask.WTid
and worktask.WTlinkItemID = workrequest.WRid
group by users.Uname, users.UnameLast, users.UnameFirst
ORDER BY users.UnameLast, users.UnameFirst
February 12, 2007 at 10:39 am
The lack of test data and results, along with the mix of old and new style join syntax, makes it difficult to guess what is going on here. I suspect the main problem is multiple rows in worktask for each user. Using an EXISTS subquery will get around this problem. I can see no point in having workrequest in the query.
The following is probably wrong, but may be of help:
SELECT U.Uname, U.UnameFirst, U.UnameLast
,ROUND(SUM(C.WCmileage), 2) AS totMileage
,ROUND(SUM(E.WXamount), 2) AS totExpenses
FROM users U
JOIN workcompleted C
ON U.Uname = C.WCemployeeID
LEFT JOIN workexpense E
ON C.WCid = E.WXlinkItemID
WHERE EXISTS (
SELECT *
FROM worktask T
-- JOIN workrequest R
-- ON T.WTlinkItemID = R.WRid
WHERE T.WTid = C.WClinkItemID
AND T.WCcompletedDate >= '20070201'
AND T.WCcompletedDate < '20070212')
GROUP BY U.Uname, U.UnameFirst, U.UnameLast
ORDER BY U.UnameFirst, U.UnameLast
February 12, 2007 at 10:43 am
Hello,
I divided up in 2 and left out worktask,workrequest (not used?)
select Mileage.users.Uname
, Mileage.UnameFirst
, Mileage.UnameLast
, Mileage.totMileage
, expenses.totExpenses
FROM --calculate total mileage per user,per workcompleted
(
SELECT users.Uname, users.UnameFirst, users.UnameLast
,round(sum(workcompleted.WCmileage), 2) as totMileage
FROM users /*all users*/
inner join workcompleted /*have not completed*/
on workcompleted.WCemployeeID = users.Uname
and workcompleted.WCcompletedDate is not null
group by users.Uname, users.UnameLast, users.UnameFirst
)Mileage
inner join --calculate total expenses per user,per workcompleted
(
SELECT users.Uname, users.UnameFirst, users.UnameLast
,round(sum(workexpense.WXamount), 2) as totExpenses
FROM users
inner join workcompleted
on workcompleted.WClinkItemID = worktask.WTid
AND workcompleted.WCcompletedDate is not null
left join workexpense
on workexpense.WXlinkItemID = workcompleted.WCid
group by users.Uname, users.UnameFirst, users.UnameLast
)expenses
on mileage.Uname=expenses.uname
and mileage.UnameFirst=expenses.UnameFirst
and mileage.UnameLast=expenses.UnameLast
order by mileage.UnameLast,mileage.UnameFirst
February 12, 2007 at 11:17 am
I have not tested this and please ignore if doesnot yeild the desired results.
SELECT users.Uname,
users.UnameFirst,
users.UnameLast,
ROUND(SUM(workcompleted.WCmileage), 2)/(COUNT( WXlinkItemID)) AS totMileage,
ROUND(SUM(workexpense.WXamount), 2) as totExpenses
FROM
workrequest,
worktask,
users,
workcompleted
LEFT OUTER JOIN
workexpense ON workexpense.WXlinkItemID = workcompleted.WCid
WHERE
workcompleted.WCcompletedDate BETWEEN '1/2/2007' AND '2/12/2007'
AND
workcompleted.WCcompletedDate IS NOT NULL
AND
workcompleted.WCemployeeID = users.Uname
AND
workcompleted.WClinkItemID = worktask.WTid
AND
worktask.WTlinkItemID = workrequest.WRid
GROUP BY
users.Uname,
users.UnameLast,
users.UnameFirst
ORDER BY
users.UnameLast,
users.UnameFirst
Prasad Bhogadi
www.inforaise.com
February 12, 2007 at 11:23 am
Someone on usenet suggest this...I think it may work?
One way would be to turn your "workexpense" table into a derived
table:
select users.Uname, users.UnameFirst, users.UnameLast,
round(sum(workcompleted.WCmileage), 2) as totMileage,
round(sum(workexpense.WXamount), 2) as totExpenses
from workrequest, worktask, users,
workcompleted
LEFT JOIN
(
SELECT WXlinkItemID, SUM(WXamount) AS WXamount
FROM workexpense
GROUP BY WXlinkItemID
) AS workexpense
ON workexpense.WXlinkItemID = workcompleted.WCid
where workcompleted.WCcompletedDate between '1/2/2007' and '2/12/2007'
and workcompleted.WCcompletedDate is not null
and workcompleted.WCemployeeID = users.Uname
and workcompleted.WClinkItemID = worktask.WTid
and worktask.WTlinkItemID = workrequest.WRid
group by users.Uname, users.UnameLast, users.UnameFirst
ORDER BY users.UnameLast, users.UnameFirst
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply