March 8, 2006 at 2:40 pm
I am farley new to SQL and having trouble with the falling query. The main resovle that I am trying to accomplish is that I need a piece of data from three tables to get the total cost of some parts for a year. How should I change the query to get the results that I need? I know it should be easy and Thanks in advance.
Declare @BeginningDate as datetime, @EndDate as datetime
set @EndDate = '12/31/2005'
set @BeginningDate = '01/01/2005'
select distinct inmast.fpartno, inmast.frev, Cast(Round(Sum((rcitem.fqtyrecv - rcinsp.fnqtyfail) * rcitem.fucost), 0)as money) as PartCostTot
from inmast inner Join rcitem
on inmast.fpartno + inmast.frev = rcitem.fpartno + rcitem.fpartrev
join rcmast on rcitem.freceiver = rcmast.freceiver
join rcinsp on rcitem.freceiver = rcinsp.fcreceiver
Where inmast.fprodcl = 'RM'
and fdaterecv between @BeginningDate and @EndDate
Group by inmast.fpartno, inmast.frev
Order by PartCostTot Desc
March 8, 2006 at 3:00 pm
Hello Jones,
Change these lines
from inmast inner Join rcitem
on inmast.fpartno + inmast.frev = rcitem.fpartno + rcitem.fpartrev
join rcmast on rcitem.freceiver = rcmast.freceiver
join rcinsp on rcitem.freceiver = rcinsp.fcreceiver
Where inmast.fprodcl = 'RM'
and fdaterecv between @BeginningDate and @EndDate
as follows
from inmast inner join rcitem on inmast.fpartno = rcitem.fpartno join rcmast on rcitem.freceiver = rcmast.freceiver join rcinsp on rcitem.freceiver = rcinsp.fcreceiver where inmast.fprodcl = 'RM' and inmast.frev = rcitem.fpartrev and <tablename>.fdaterecv between @BeginningDate and @EndDate
Note: replace <tablename> with the correct tablename.
Thanks and have a great day!!!
Lucky
March 9, 2006 at 6:42 am
Thanks for your reply.
I tried as you suggested and it still returned to many results and over inflated the total.
It would be so much simpler if I didn't need info from all three tables to make the calculation.
Any other thoughs?
March 9, 2006 at 9:19 am
Well, I'm fairly new to this myself, but one thing I'd look at would be the JOIN section:
from inmast inner Join rcitem
on inmast.fpartno + inmast.frev = rcitem.fpartno + rcitem.fpartrev
join rcmast on rcitem.freceiver = rcmast.freceiver
join rcinsp on rcitem.freceiver = rcinsp.fcreceiver
Are inmast.fpartno and inmast.frev numeric types? If not then you might be concatenating your data instead of summing those two values, and it might lead to odd results. Are you actually after inmast.fpartno=rcitem.fpartno AND inmast.frev=rcitem.fpartrev? You could also perform the calculations in a subquery to make debugging the multiple joins simpler.
Another thing to look at is you are using BETWEEN on your dates and so you're actually excluding 1-1-05 and 12-31-05. I'm not sure if that was your intention or not but I always try to use >=startdate and <=enddate to work around that. Hopefully some of that helps you out. Good luck.
Brian
March 9, 2006 at 9:29 am
Thanks for your reply!
How would I do the the calculation in a subquery?
March 9, 2006 at 10:07 am
Something along the lines of:
select distinct inmast.fpartno, inmast.frev, cast(round(sum(X.cost),0) as money) as PartCostTot
from inmast inner Join
(select rcitem.fpartno, rcitem.fpartrev, ((rcitem.fqtyrecv - rcinsp.fnqtyfail) * rcitem.fucost)) as Cost from rcitem inner join rcmast on rcitem.freceiver=rcmast.freceiver inner join rcinsp on rcmast.freceiver=rcinsp.fcreceiver) as X
on (inmast.fpartno + inmast.frev) = (x.fpartno + x.fpartrev) and inmast.fprodcl='RM'
Adding the fprodcl section in the join rather than the where clause will restrict your results when they're gathered, rather than gathering them all and then filtering with WHERE.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply