Receiving Cross Join Result in Error

Hello,

I'm getting a cross join result from the following query;

SELECT DISTINCT RTRIM(DA.AcctCode) AS DaAcctCode, cy.JanRev AS CYJanRev, PY.JanRev AS PYJanRev

FROM SalesCommissions.dbo.DailyAccountsDownload DA

INNER JOIN SalesReporting.dbo.PriorYearSales PY

ON CAST(RTRIM(DA.AcctCode)AS varchar(8)) = RTRIM(PY.AcctCode)

INNER JOIN SalesReporting.dbo.CurrentYearSales CY

ON CAST(RTRIM(DA.AcctCode)AS varchar(8)) = RTRIM(CY.AcctCode)

WHERE

RTRIM(da.acctcode) = 'am940'

ORDER BY

cy.janRev, py.JanRev

--************************************************
The result looks like this;

DaAcctCode CYJanRev PYJanRev
AM940 2.25 0.00
AM940 2.25 12.75
AM940 2.25 745.00
AM940 2.25 1620.50
AM940 511.00 0.00
AM940 511.00 12.75
AM940 511.00 745.00
AM940 511.00 1620.50
AM940 1256.50 0.00
AM940 1256.50 12.75
AM940 1256.50 745.00
AM940 1256.50 1620.50

When I try to SUM(CY.JanRev) and SUM(PY.JanRev), then GROUP BY DA.AcctCode, all of the numbers in this cross join result are summed, and result is incorrect.

The thing I am trying to do is get correct totals for CYJanRev and PYJanRev per each DAAcctCode. In this example, the totals should be 1769.75 and 2378.25 respectively.

Why might I be having this problem? I've tried a number of different aggregate functions and joins to get the correct results, but nothing has worked.

Thank you for your help!

cdun2

I think there is problem with your join condition. Hope this not retrieving the previous years data too? Could you double check pl...|||

Maybe something like:

select da.acctCode as daAcctCode,
( select sum(py.janRev) from dbo.priorYearSales py
where CAST(RTRIM(DA.AcctCode)AS varchar(8)) = RTRIM(PY.AcctCode)
) as pyJanRev,
( select sum(cy.janRev) from dbo.currentYearSales cy
where CAST(RTRIM(DA.AcctCode)AS varchar(8)) = RTRIM(cY.AcctCode)
) as cyJanRev
from ( select distinct rtrim(acctCode)
from salesCommissions.dbo.dailyAccountsDownload
) da

?

|||

I'm not sure I follow you. CY is the current year data (2007) and PY is the previous year data (2006).

|||

This got it;

SELECT RTRIM( DA.AcctCode) AS DaAcctCode, CY.JanRev AS CYJanRev, PY.JanRev PYJanRev
FROM @.DailyAccountsDownload DA
INNER JOIN( SELECT AcctCode, SUM( JanRev) AS JanRev FROM @.CurrentYearSales GROUP BY AcctCode) CY
ON CAST( RTRIM( DA.AcctCode) AS varchar(8)) = RTRIM( CY.AcctCode)
INNER JOIN( SELECT AcctCode, SUM( JanRev) AS JanRev FROM @.PriorYearSales GROUP BY AcctCode) PY
ON CAST( RTRIM( DA.AcctCode) AS varchar(8)) = RTRIM( PY.AcctCode)
WHERE RTRIM( DA.AcctCode) = 'AM940'

Thanks for the assistance!

cdun2