Cross Join Problem
-
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)
-- INNER JOIN SalesReporting.dbo.[2005MonthlySales] TH
-- ON CAST(RTRIM(DA.AcctCode)AS varchar(8)) = RTRIM(TH.AcctCode)
WHERE
RTRIM(da.acctcode) = 'am940'
ORDER
BY
cy
.janRev, py.JanRev
--************************************************
The results look 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.
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!
CSDunn
-
What results do you hope to achieve? The names of your tables seem to indicate that you are summing apples and oranges.
Does this psuedo code help in giving us an example of some test data? I am guessing there may be an additional field that can be used to distinguish just which of these values you want to SUM.
DECLARE @DailyAccountsDownload TABLE( AcctCode varchar(5))
INSERT @DailyAccountsDownload SELECT 'AM940'
DECLARE @PriorYearSales TABLE( AcctCode varchar(5),
JanRev money ))
INSERT @PriorYearSales SELECT 'AM940', 2.25
INSERT @PriorYearSales SELECT 'AM940', 511.00
INSERT @PriorYearSales SELECT 'AM940', 21256.50
DECLARE @CurrentYearSales TABLE( AcctCode varchar(5),
JanRev money ))
INSERT @CurrentYearSales SELECT 'AM940', 0.00
INSERT @CurrentYearSales SELECT 'AM940', 12.75
INSERT @CurrentYearSales SELECT 'AM940', 745.00
INSERT @CurrentYearSales SELECT 'AM940', 1620.50
I wasn't born stupid - I had to study.
-
Thank you for your input. I want to get an accurate total for both of the Revenue fields in one result set. The code you have posted correctly represents the data I get back from each of the three tables individually.
CSDunn
-
Is this what you are looking for? I have removed DISTINCT, (generally that indicates a problem with table relationships and should be avoided...). Also, the need for an ORDER BY in this scenario was unnessary...
--SELECT DISTINCT RTRIM( DA.AcctCode) AS DaAcctCode, SUM( CY.JanRev) AS CYJanRev, SUM( PY.JanRev) AS PYJanRev
SELECT RTRIM( DA.AcctCode) AS DaAcctCode, SUM( CY.JanRev) AS CYJanRev, SUM( PY.JanRev) AS PYJanRev
FROM @DailyAccountsDownload DA
INNER JOIN @PriorYearSales PY ON CAST( RTRIM( DA.AcctCode) AS varchar(8)) = RTRIM( PY.AcctCode)
INNER JOIN @CurrentYearSales CY ON CAST( RTRIM( DA.AcctCode) AS varchar(8)) = RTRIM( CY.AcctCode)
WHERE RTRIM( DA.AcctCode) = 'AM940'
GROUP BY DA.AcctCode
--ORDER BY CY.JanRev, PY.JanRev
DaAcctCode CYJanRev PYJanRev
---------- --------------------- ---------------------
AM940 $7,134.75 $87,079.00
I wasn't born stupid - I had to study.
-
Thanks again. No, those are not the correct results.
The following is the result set from the inital posting;
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
SUM(CY.JanRev) should be equivalent to;
2.25+511+1256.50; 1769.75
SUM(PYJanRev) should be equivalent to;
12.75+745+1620; 2378.25
Does this help?
CSDunn
-
Yes - it helped me recognize I am a knucklehead and did not do the simple addition before I posted...
Try this:
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'
I wasn't born stupid - I had to study.
-
Bingo! Thanks a million!
CSDunn
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply