Checking my totals in the SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Checking my totals in the SQL

Author

Topic

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-08-12 : 16:49:04

the following SQL is producing in Report Manager some totals that are impossible according to the user. I checked that all dates are in the period selected.

Now I would like to effect totals of the Extdcost and xtndprce inthis SQL to compare with that which I am getting in Report manager. the totals must be for Itmgedsc as a group.

SELECT IV00101.ITMGEDSC, IV00101.ITEMDESC, RM00101.CUSTNAME, SOP10200.EXTDCOST, SOP10200.XTNDPRCE, SOP10200.ITEMNMBR, IV40600.UserCatLongDescr, CATS.UserCatLongDescr AS Expr1, SOP10100.SOPNUMBE,
sop10100.docdate, SOP10106.USRDEF05
FROM SOP10200 AS SOP10200 INNER JOIN
IV00101 AS IV00101 ON SOP10200.ITEMNMBR = IV00101.ITEMNMBR INNER JOIN
SOP10100 AS SOP10100 ON SOP10200.SOPTYPE = SOP10100.SOPTYPE AND SOP10200.SOPNUMBE = SOP10100.SOPNUMBE INNER JOIN
SOP10106 AS SOP10106 ON SOP10200.SOPTYPE = SOP10106.SOPTYPE AND SOP10200.SOPNUMBE = SOP10106.SOPNUMBE INNER JOIN
SOP10100 AS RM00101 ON SOP10100.CUSTNMBR = RM00101.CUSTNMBR INNER JOIN
IV40600 AS IV40600 ON IV00101.ITMGEDSC = IV40600.USCATVAL INNER JOIN
IV40600 AS CATS ON IV00101.USCATVLS_2 = CATS.USCATVAL
WHERE RM00101.CUSTNAME = 'Northern Center' AND
SOP10100.DOCDATE BETWEEN '03/01/09' AND '03/31/09'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-12 : 17:03:27

[code]SELECT SUM(extdcost) as sum_extdcost, SUM(xtndprce) as sum_xtndprce
FROM
(
SELECT iv00101.itmgedsc,
iv00101.itemdesc,
rm00101.custname,
sop10200.extdcost,
sop10200.xtndprce,
sop10200.itemnmbr,
iv40600.usercatlongdescr,
cats.usercatlongdescr AS expr1,
sop10100.sopnumbe,
sop10100.docdate,
sop10106.usrdef05
FROM sop10200 AS sop10200
INNER JOIN iv00101 AS iv00101
ON sop10200.itemnmbr = iv00101.itemnmbr
INNER JOIN sop10100 AS sop10100
ON sop10200.soptype = sop10100.soptype
AND sop10200.sopnumbe = sop10100.sopnumbe
INNER JOIN sop10106 AS sop10106
ON sop10200.soptype = sop10106.soptype
AND sop10200.sopnumbe = sop10106.sopnumbe
INNER JOIN sop10100 AS rm00101
ON sop10100.custnmbr = rm00101.custnmbr
INNER JOIN iv40600 AS iv40600
ON iv00101.itmgedsc = iv40600.uscatval
INNER JOIN iv40600 AS cats
ON iv00101.uscatvls_2 = cats.uscatval
WHERE rm00101.custname = 'Northern Center'
AND sop10100.docdate BETWEEN '03/01/09' AND '03/31/09'
)dt
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-12 : 17:20:00

or this...

SELECT itmgedsc,SUM(extdcost) as sum_extdcost, SUM(xtndprce) as sum_xtndprce
FROM
(
SELECT iv00101.itmgedsc, 
       iv00101.itemdesc, 
       rm00101.custname, 
       sop10200.extdcost, 
       sop10200.xtndprce, 
       sop10200.itemnmbr, 
       iv40600.usercatlongdescr, 
       cats.usercatlongdescr AS expr1, 
       sop10100.sopnumbe, 
       sop10100.docdate, 
       sop10106.usrdef05 
FROM   sop10200 AS sop10200 
       INNER JOIN iv00101 AS iv00101 
         ON sop10200.itemnmbr = iv00101.itemnmbr 
       INNER JOIN sop10100 AS sop10100 
         ON sop10200.soptype = sop10100.soptype 
            AND sop10200.sopnumbe = sop10100.sopnumbe 
       INNER JOIN sop10106 AS sop10106 
         ON sop10200.soptype = sop10106.soptype 
            AND sop10200.sopnumbe = sop10106.sopnumbe 
       INNER JOIN sop10100 AS rm00101 
         ON sop10100.custnmbr = rm00101.custnmbr 
       INNER JOIN iv40600 AS iv40600 
         ON iv00101.itmgedsc = iv40600.uscatval 
       INNER JOIN iv40600 AS cats 
         ON iv00101.uscatvls_2 = cats.uscatval 
WHERE  rm00101.custname = 'Northern Center' 
       AND sop10100.docdate BETWEEN '03/01/09' AND '03/31/09' 
)dt
group by itmgedsc