• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

What is wrong with the following code to get Ambiguous column name? or "The multi-part Identifier cannot be bound."

I am trying to group all the records by SIN, YEAR. I am getting the error Ambiguous column name 'SIN' when I try to run the code below. I understand the reason for the error but when I try tblResult.SIN, tblResult.Year I get the errors:
"The multi-part Identifier "tblResults.SIN" could not be bound."
"The multi-part Identifier "tblResults.YEAR" could not be bound."

Any help would be greatly appreciated.
SELECT     
SIN, 
YEAR, 
COALESCE (EXP_REF, 0.00) AS EXP_REF, 
COALESCE (REVENUE, 0.00) AS REVENUE, 
COALESCE ([CLIENT.REC], 0.00) AS [CLIENT.REC], 
COALESCE ([CLIENT.SHORT], 0.00) AS [CLIENT.SHORT], 
COALESCE ([SPOUSE.SHORT], 0.00) AS [SPOUSE.SHORT], 
COALESCE (ADJUSTMENT, 0.00) AS ADJUSTMENT, 
COALESCE (EXP_REF, 0) - COALESCE (REVENUE, 0) - COALESCE ([CLIENT.REC], 0) - COALESCE ([CLIENT.SHORT], 0)  - COALESCE ([SPOUSE.SHORT], 0) + COALESCE (ADJUSTMENT, 0) AS BF
 
FROM         
(SELECT     
COALESCE (Disb.SIN, Rcpts.SIN, Adjst.strSIN) AS SIN, 
COALESCE (Disb.YEAR, Rcpts.[Receipt.Year], Adjst.[Adj.Year]) AS YEAR, 
Disb.[T1 REFUND], 
Disb.OWES_REV, 
Disb.EXP_REF, 
Rcpts.REVENUE, 
Rcpts.[CLIENT.REC], 
Rcpts.[CLIENT.SHORT], 
Rcpts.[SPOUSE.SHORT], 
Adjst.ADJUSTMENT
FROM          
 
(SELECT     
YEAR, 
SIN, 
C_T1REFUND AS [T1 REFUND], 
C_OWES_REV AS OWES_REV, 
C_SCH1_REFUND AS EXP_REF
FROM          
Disbursements
WHERE      (CKDATE <= '10/31/2007')
 
GROUP BY SIN, YEAR, C_T1REFUND,C_OWES_REV,C_SCH1_REFUND ) AS Disb FULL OUTER JOIN
 
(SELECT     
SIN, 
YEAR AS [Receipt.Year], 
SUM(CASE WHEN Receipts.[Type] = 'RV' THEN COALESCE (Receipts.AMT_REC, 0) ELSE 0 END) AS REVENUE, 
SUM(CASE WHEN Receipts.[Type] = 'CL' THEN COALESCE (Receipts.AMT_REC, 0) ELSE 0 END) AS [CLIENT.REC], 
SUM(CASE WHEN Receipts.[Type] = 'CS' THEN COALESCE (Receipts.AMT_REC, 0) ELSE 0 END) AS [CLIENT.SHORT], 
SUM(CASE WHEN Receipts.[Type] = 'SS' THEN COALESCE (Receipts.AMT_REC, 0) ELSE 0 END) AS [SPOUSE.SHORT]
FROM          Receipts
WHERE      (DATE_REC <= '10/31/2007') 
GROUP BY SIN, YEAR) AS Rcpts ON Rcpts.SIN = Disb.SIN AND Rcpts.[Receipt.Year] = Disb.YEAR FULL OUTER JOIN
 
(SELECT     
strSIN, 
SUM(decAMT) AS ADJUSTMENT, 
strYear AS [Adj.Year]
FROM          tblAdjustments
WHERE      (Date <= '10/31/2007')
GROUP BY strSIN, strYear) AS Adjst ON Adjst.strSIN IN (Disb.SIN, Rcpts.SIN) Group By SIN, YEAR) AS tblResult
ORDER BY SIN

Open in new window

0
JohnnyBCJ
Asked:
JohnnyBCJ
  • 10
  • 6
1 Solution
 
i2mentalCommented:
If you're using tblResults.SIN and tblResults.Year  it should be tblResult.SIN and tblResult.Year according to your code.

No "s"
0
 
BrandonGalderisiCommented:
Are you sure you are referencing the correct column names?
tblResult has strSin
rcpts has SIN
disb has SIN
0
 
i2mentalCommented:
All of the sub selects appear to line up ok. He has SIN several times, but what it comes down to this basically. If you cut and pasted those error messages, then it's just a typo. tblResults instead of tblResult

SELECT    
SIN,
YEAR,
COALESCE (EXP_REF, 0.00) AS EXP_REF,
COALESCE (REVENUE, 0.00) AS REVENUE,
COALESCE ([CLIENT.REC], 0.00) AS [CLIENT.REC],
COALESCE ([CLIENT.SHORT], 0.00) AS [CLIENT.SHORT],
COALESCE ([SPOUSE.SHORT], 0.00) AS [SPOUSE.SHORT],
COALESCE (ADJUSTMENT, 0.00) AS ADJUSTMENT,
COALESCE (EXP_REF, 0) - COALESCE (REVENUE, 0) - COALESCE ([CLIENT.REC], 0) - COALESCE ([CLIENT.SHORT], 0)  - COALESCE ([SPOUSE.SHORT], 0) + COALESCE (ADJUSTMENT, 0) AS BF
 
FROM        
(SELECT    
COALESCE (Disb.SIN, Rcpts.SIN, Adjst.strSIN) AS SIN,
COALESCE (Disb.YEAR, Rcpts.[Receipt.Year], Adjst.[Adj.Year]) AS YEAR,
Disb.[T1 REFUND],
Disb.OWES_REV,
Disb.EXP_REF,
Rcpts.REVENUE,
Rcpts.[CLIENT.REC],
Rcpts.[CLIENT.SHORT],
Rcpts.[SPOUSE.SHORT],
Adjst.ADJUSTMENT
FROM Disb
 FULL OUTER JOIN Rcpts ON Rcpts.SIN = Disb.SIN AND Rcpts.[Receipt.Year] = Disb.YEAR
 FULL OUTER JOIN Adjst ON Adjst.strSIN IN (Disb.SIN, Rcpts.SIN) Group By SIN, YEAR) AS tblResult
ORDER BY SIN
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
JohnnyBCJAuthor Commented:
Sorry, the typo is unfortunately in the description and not in the actual code. No I did not copy and pasted (although I should have). The results I am getting using the above code  without the group is

WWWWWW      1989      0.00      0.00      0.00      0.00      0.00      -209.90      -209.90
TTTTTTTTT      1991      342.27      309.99      0.00      0.00      0.00      -1.69      30.59
XXXXXXXXX      1992      0.00      0.00      0.00      0.00      0.00      -44.50      -44.50
XXXXXXXXX      1992      0.00      0.00      0.00      0.00      0.00      -12.10      -12.10
XXXXXXXXX      1992      81.00      0.00      0.00      0.00      0.00      -44.50      36.40
XXXXXXXXX      1992      81.00      0.00      0.00      0.00      0.00      -12.10      68.90

Where XXXXXXXXX is the same sin.  What I want is:

WWWWWW      1989      0.00      0.00      0.00      0.00      0.00      -209.90      -209.90
TTTTTTTTT      1991      342.27      309.99      0.00      0.00      0.00      -1.69      30.59
XXXXXXXXX      1992      0.00      0.00      0.00      0.00      0.00      -113.2      48.70

note: the last 2 numbers are just a sum of the values for that column grouped by sin/year.
-113.2 = -44.50 -12.10 -44.50 -12.10
48.70 = -44.50 -12.10 + 36.40 + 68.90
0
 
i2mentalCommented:
Are you past your original syntax problem?

As far as the results you want. You'll need to use aggregate functions on the columns you're not grouping by. I'm not sure what you want to do with the 81.00 in  your third column as it doesn't appear in  your wanted results, if you want them all summed, try this.

It seems your outermost select statement may be redundant in this case.
SELECT     
SIN, 
YEAR, 
COALESCE (EXP_REF, 0.00) AS EXP_REF, 
COALESCE (REVENUE, 0.00) AS REVENUE, 
COALESCE ([CLIENT.REC], 0.00) AS [CLIENT.REC], 
COALESCE ([CLIENT.SHORT], 0.00) AS [CLIENT.SHORT], 
COALESCE ([SPOUSE.SHORT], 0.00) AS [SPOUSE.SHORT], 
COALESCE (ADJUSTMENT, 0.00) AS ADJUSTMENT, 
COALESCE (EXP_REF, 0) - COALESCE (REVENUE, 0) - COALESCE ([CLIENT.REC], 0) - COALESCE ([CLIENT.SHORT], 0)  - COALESCE ([SPOUSE.SHORT], 0) + COALESCE (ADJUSTMENT, 0) AS BF
 
FROM         
(SELECT     
COALESCE (Disb.SIN, Rcpts.SIN, Adjst.strSIN) AS SIN, 
COALESCE (Disb.YEAR, Rcpts.[Receipt.Year], Adjst.[Adj.Year]) AS YEAR, 
SUM(Disb.[T1 REFUND]), 
SUM(Disb.OWES_REV), 
SUM(Disb.EXP_REF), 
SUM(Rcpts.REVENUE), 
SUM(Rcpts.[CLIENT.REC]), 
SUM(Rcpts.[CLIENT.SHORT]), 
SUM(Rcpts.[SPOUSE.SHORT]), 
SUM(Adjst.ADJUSTMENT)
FROM          
 
(SELECT     
YEAR, 
SIN, 
C_T1REFUND AS [T1 REFUND], 
C_OWES_REV AS OWES_REV, 
C_SCH1_REFUND AS EXP_REF
FROM          
Disbursements
WHERE      (CKDATE <= '10/31/2007')
 
GROUP BY SIN, YEAR, C_T1REFUND,C_OWES_REV,C_SCH1_REFUND ) AS Disb FULL OUTER JOIN
 
(SELECT     
SIN, 
YEAR AS [Receipt.Year], 
SUM(CASE WHEN Receipts.[Type] = 'RV' THEN COALESCE (Receipts.AMT_REC, 0) ELSE 0 END) AS REVENUE, 
SUM(CASE WHEN Receipts.[Type] = 'CL' THEN COALESCE (Receipts.AMT_REC, 0) ELSE 0 END) AS [CLIENT.REC], 
SUM(CASE WHEN Receipts.[Type] = 'CS' THEN COALESCE (Receipts.AMT_REC, 0) ELSE 0 END) AS [CLIENT.SHORT], 
SUM(CASE WHEN Receipts.[Type] = 'SS' THEN COALESCE (Receipts.AMT_REC, 0) ELSE 0 END) AS [SPOUSE.SHORT]
FROM          Receipts
WHERE      (DATE_REC <= '10/31/2007') 
GROUP BY SIN, YEAR) AS Rcpts ON Rcpts.SIN = Disb.SIN AND Rcpts.[Receipt.Year] = Disb.YEAR FULL OUTER JOIN
 
(SELECT     
strSIN, 
SUM(decAMT) AS ADJUSTMENT, 
strYear AS [Adj.Year]
FROM          tblAdjustments
WHERE      (Date <= '10/31/2007')
GROUP BY strSIN, strYear) AS Adjst ON Adjst.strSIN IN (Disb.SIN, Rcpts.SIN) Group By SIN, YEAR) AS tblResult
ORDER BY SIN

Open in new window

0
 
JohnnyBCJAuthor Commented:
SELECT    
SIN,
YEAR,
COALESCE (EXP_REF, 0.00) AS EXP_REF,
COALESCE (REVENUE, 0.00) AS REVENUE,
COALESCE ([CLIENT.REC], 0.00) AS [CLIENT.REC],
COALESCE ([CLIENT.SHORT], 0.00) AS [CLIENT.SHORT],
COALESCE ([SPOUSE.SHORT], 0.00) AS [SPOUSE.SHORT],
COALESCE (ADJUSTMENT, 0.00) AS ADJUSTMENT,
COALESCE (EXP_REF, 0) - COALESCE (REVENUE, 0) - COALESCE ([CLIENT.REC], 0) - COALESCE ([CLIENT.SHORT], 0)  - COALESCE ([SPOUSE.SHORT], 0) + COALESCE (ADJUSTMENT, 0) AS BF
 
FROM        
(SELECT    
COALESCE (Disb.SIN, Rcpts.SIN, Adjst.strSIN) AS SIN,
COALESCE (Disb.YEAR, Rcpts.[Receipt.Year], Adjst.[Adj.Year]) AS YEAR,
SUM(Disb.[T1 REFUND]) as [T1 REFUND],
SUM(Disb.OWES_REV) as [OWES_REV],
SUM(Disb.EXP_REF)  as [EXP_REF],
SUM(Rcpts.REVENUE) as [REVENUE],
SUM(Rcpts.[CLIENT.REC]) as [CLIENT.REC],
SUM(Rcpts.[CLIENT.SHORT]) as [CLIENT.SHORT],
SUM(Rcpts.[SPOUSE.SHORT]) as [SPOUSE.SHORT],
SUM(Adjst.ADJUSTMENT) as [ADJUSTMENT]
FROM          
 
(SELECT    
YEAR,
SIN,
C_T1REFUND AS [T1 REFUND],
C_OWES_REV AS OWES_REV,
C_SCH1_REFUND AS EXP_REF
FROM          
Disbursements
WHERE      (CKDATE <= '10/31/2007')
 
GROUP BY SIN, YEAR, C_T1REFUND,C_OWES_REV,C_SCH1_REFUND ) AS Disb FULL OUTER JOIN
 
(SELECT    
SIN,
YEAR AS [Receipt.Year],
SUM(CASE WHEN Receipts.[Type] = 'RV' THEN COALESCE (Receipts.AMT_REC, 0) ELSE 0 END) AS REVENUE,
SUM(CASE WHEN Receipts.[Type] = 'CL' THEN COALESCE (Receipts.AMT_REC, 0) ELSE 0 END) AS [CLIENT.REC],
SUM(CASE WHEN Receipts.[Type] = 'CS' THEN COALESCE (Receipts.AMT_REC, 0) ELSE 0 END) AS [CLIENT.SHORT],
SUM(CASE WHEN Receipts.[Type] = 'SS' THEN COALESCE (Receipts.AMT_REC, 0) ELSE 0 END) AS [SPOUSE.SHORT]
FROM          Receipts
WHERE      (DATE_REC <= '10/31/2007')
GROUP BY SIN, YEAR) AS Rcpts ON Rcpts.SIN = Disb.SIN AND Rcpts.[Receipt.Year] = Disb.YEAR FULL OUTER JOIN
 
(SELECT    
strSIN,
SUM(decAMT) AS ADJUSTMENT,
strYear AS [Adj.Year]
FROM          tblAdjustments
WHERE      (Date <= '10/31/2007')
GROUP BY strSIN, strYear) AS Adjst ON Adjst.strSIN IN (Disb.SIN, Rcpts.SIN) Group By SIN, YEAR) AS tblResult
order by SIN

The above code gives me the Ambiguous column name 'SIN'. I also get it if I remove the last line 'order by SIN'. It gives me  the ambiguous column because it doesn't know which one to pick but when I tell it to pick tblResult.strSIN it gives me The multi-part Identifier "tblResult.strSIN"cannot be bound.
0
 
JohnnyBCJAuthor Commented:
I'm not sure what you want to do with the 81.00 in  your third column as it doesn't appear in  your wanted results, if you want them all summed, try this.

Sorry. You are correct. I wanted them all summed.

WWWWWW      1989      0.00      0.00      0.00      0.00      0.00      -209.90      -209.90
TTTTTTTTT      1991      342.27      309.99      0.00      0.00      0.00      -1.69      30.59
XXXXXXXXX      1992      162.00      0.00      0.00      0.00      0.00      -113.2      48.70
0
 
i2mentalCommented:
Please post the version of the code you use that gives you the error "The multi-part Identifier "tblResult.strSIN"cannot be bound."
0
 
JohnnyBCJAuthor Commented:
SELECT    
SIN,
YEAR,
COALESCE (EXP_REF, 0.00) AS EXP_REF,
COALESCE (REVENUE, 0.00) AS REVENUE,
COALESCE ([CLIENT.REC], 0.00) AS [CLIENT.REC],
COALESCE ([CLIENT.SHORT], 0.00) AS [CLIENT.SHORT],
COALESCE ([SPOUSE.SHORT], 0.00) AS [SPOUSE.SHORT],
COALESCE (ADJUSTMENT, 0.00) AS ADJUSTMENT,
COALESCE (EXP_REF, 0) - COALESCE (REVENUE, 0) - COALESCE ([CLIENT.REC], 0) - COALESCE ([CLIENT.SHORT], 0)  - COALESCE ([SPOUSE.SHORT], 0) + COALESCE (ADJUSTMENT, 0) AS BF
 
FROM        
(SELECT    
COALESCE (Disb.SIN, Rcpts.SIN, Adjst.strSIN) AS SIN,
COALESCE (Disb.YEAR, Rcpts.[Receipt.Year], Adjst.[Adj.Year]) AS YEAR,
SUM(Disb.[T1 REFUND]) as [T1 REFUND],
SUM(Disb.OWES_REV) as [OWES_REV],
SUM(Disb.EXP_REF)  as [EXP_REF],
SUM(Rcpts.REVENUE) as [REVENUE],
SUM(Rcpts.[CLIENT.REC]) as [CLIENT.REC],
SUM(Rcpts.[CLIENT.SHORT]) as [CLIENT.SHORT],
SUM(Rcpts.[SPOUSE.SHORT]) as [SPOUSE.SHORT],
SUM(Adjst.ADJUSTMENT) as [ADJUSTMENT]
FROM          
 
(SELECT    
YEAR,
SIN,
C_T1REFUND AS [T1 REFUND],
C_OWES_REV AS OWES_REV,
C_SCH1_REFUND AS EXP_REF
FROM          
Disbursements
WHERE      (CKDATE <= '10/31/2007')
 
GROUP BY SIN, YEAR, C_T1REFUND, C_OWES_REV, C_SCH1_REFUND ) AS Disb   FULL OUTER JOIN
 
(SELECT    
SIN,
YEAR AS [Receipt.Year],
SUM(CASE WHEN Receipts.[Type] = 'RV' THEN COALESCE (Receipts.AMT_REC, 0) - COALESCE (Receipts.INT_AMT, 0) ELSE 0 END) AS REVENUE,
SUM(CASE WHEN Receipts.[Type] = 'CL' THEN COALESCE (Receipts.AMT_REC, 0) ELSE 0 END) AS [CLIENT.REC],
SUM(CASE WHEN Receipts.[Type] = 'CS' THEN COALESCE (Receipts.AMT_REC, 0) ELSE 0 END) AS [CLIENT.SHORT],
SUM(CASE WHEN Receipts.[Type] = 'SS' THEN COALESCE (Receipts.AMT_REC, 0) ELSE 0 END) AS [SPOUSE.SHORT]
FROM          Receipts
WHERE      (DATE_REC <= '10/31/2007')
GROUP BY SIN, YEAR) AS Rcpts ON Rcpts.SIN = Disb.SIN AND Rcpts.[Receipt.Year] = Disb.YEAR FULL OUTER JOIN
 
(SELECT    
strSIN,
SUM(decAMT) AS ADJUSTMENT,
strYear AS [Adj.Year]
FROM          tblAdjustments
WHERE      (Date <= '10/31/2007')
GROUP BY strSIN, strYear) AS Adjst ON Adjst.strSIN IN (Disb.SIN, Rcpts.SIN) Group By tblResult.SIN, tblResult.YEAR) AS tblResult

ERRORS
 "The multi-part Identifier "tblResult.strSIN"cannot be bound."
 "The multi-part Identifier "tblResult.YEAR"cannot be bound."

If I leave the last couple of lines to be:
WHERE      (Date <= '10/31/2007')
GROUP BY strSIN, strYear) AS Adjst ON Adjst.strSIN IN (Disb.SIN, Rcpts.SIN) Group By SIN, YEAR) AS tblResult

ERROR: Ambiguous column name 'SIN'



0
 
i2mentalCommented:
Yeah, you're referring to tblresult.SIN before you define (within the parentheses of) tblResult.

Don't use tblresult.SIN and tblResult.YEAR where you are in the group by. Use just SIN and YEAR.

As far as the abigious column error it will leave with, it's most likely referring to the SIN right after your first SELECT.

Try This.

SELECT     
tblresult.SIN, 
tblresult.YEAR, 
COALESCE (EXP_REF, 0.00) AS EXP_REF, 
COALESCE (REVENUE, 0.00) AS REVENUE, 
COALESCE ([CLIENT.REC], 0.00) AS [CLIENT.REC], 
COALESCE ([CLIENT.SHORT], 0.00) AS [CLIENT.SHORT], 
COALESCE ([SPOUSE.SHORT], 0.00) AS [SPOUSE.SHORT], 
COALESCE (ADJUSTMENT, 0.00) AS ADJUSTMENT, 
COALESCE (EXP_REF, 0) - COALESCE (REVENUE, 0) - COALESCE ([CLIENT.REC], 0) - COALESCE ([CLIENT.SHORT], 0)  - COALESCE ([SPOUSE.SHORT], 0) + COALESCE (ADJUSTMENT, 0) AS BF
 
FROM         
(SELECT     
COALESCE (Disb.SIN, Rcpts.SIN, Adjst.strSIN) AS SIN, 
COALESCE (Disb.YEAR, Rcpts.[Receipt.Year], Adjst.[Adj.Year]) AS YEAR, 
SUM(Disb.[T1 REFUND]) as [T1 REFUND],
SUM(Disb.OWES_REV) as [OWES_REV], 
SUM(Disb.EXP_REF)  as [EXP_REF], 
SUM(Rcpts.REVENUE) as [REVENUE],
SUM(Rcpts.[CLIENT.REC]) as [CLIENT.REC],
SUM(Rcpts.[CLIENT.SHORT]) as [CLIENT.SHORT],
SUM(Rcpts.[SPOUSE.SHORT]) as [SPOUSE.SHORT],
SUM(Adjst.ADJUSTMENT) as [ADJUSTMENT]
FROM          
 
(SELECT     
YEAR, 
SIN, 
C_T1REFUND AS [T1 REFUND], 
C_OWES_REV AS OWES_REV, 
C_SCH1_REFUND AS EXP_REF
FROM          
Disbursements
WHERE      (CKDATE <= '10/31/2007')
 
GROUP BY SIN, YEAR, C_T1REFUND, C_OWES_REV, C_SCH1_REFUND ) AS Disb   FULL OUTER JOIN
 
(SELECT     
SIN, 
YEAR AS [Receipt.Year], 
SUM(CASE WHEN Receipts.[Type] = 'RV' THEN COALESCE (Receipts.AMT_REC, 0) - COALESCE (Receipts.INT_AMT, 0) ELSE 0 END) AS REVENUE, 
SUM(CASE WHEN Receipts.[Type] = 'CL' THEN COALESCE (Receipts.AMT_REC, 0) ELSE 0 END) AS [CLIENT.REC], 
SUM(CASE WHEN Receipts.[Type] = 'CS' THEN COALESCE (Receipts.AMT_REC, 0) ELSE 0 END) AS [CLIENT.SHORT], 
SUM(CASE WHEN Receipts.[Type] = 'SS' THEN COALESCE (Receipts.AMT_REC, 0) ELSE 0 END) AS [SPOUSE.SHORT]
FROM          Receipts
WHERE      (DATE_REC <= '10/31/2007') 
GROUP BY SIN, YEAR) AS Rcpts ON Rcpts.SIN = Disb.SIN AND Rcpts.[Receipt.Year] = Disb.YEAR FULL OUTER JOIN
 
(SELECT     
strSIN, 
SUM(decAMT) AS ADJUSTMENT, 
strYear AS [Adj.Year]
FROM          tblAdjustments
WHERE      (Date <= '10/31/2007')
GROUP BY strSIN, strYear) AS Adjst ON Adjst.strSIN IN (Disb.SIN, Rcpts.SIN) Group By SIN, YEAR) AS tblResult

Open in new window

0
 
JohnnyBCJAuthor Commented:
When I copy and paste your code I still end up the same error when I try to verify SQL syntax. When I try to run it, the "tblresult." in tblresult.SIN and tblresult.Year both disappear and I get the error message: Column Rcpts.SIN is invalid in the select list because it is not contained in either an aggregated function or the group by clause. The same goes with Rcpst.SIN and Adjst.strSIN.

This is by far the most complex SQL error I've ever had. It does not help the fact that 3 of the tables have sin/strSIN in it.
0
 
i2mentalCommented:
Believe it or not, you're getting closer. it's only going to report one type of error to you at a time as it bumps into them. You're past the first. The next one is because you're doing a coalesce on several fields as part of your select list, but only grouping by one of them. You need to group by the aggregations as well.

It's hard to validate the code without the tables to reference, but try this.
SELECT     
tblresult.SIN, 
tblresult.YEAR, 
COALESCE (EXP_REF, 0.00) AS EXP_REF, 
COALESCE (REVENUE, 0.00) AS REVENUE, 
COALESCE ([CLIENT.REC], 0.00) AS [CLIENT.REC], 
COALESCE ([CLIENT.SHORT], 0.00) AS [CLIENT.SHORT], 
COALESCE ([SPOUSE.SHORT], 0.00) AS [SPOUSE.SHORT], 
COALESCE (ADJUSTMENT, 0.00) AS ADJUSTMENT, 
COALESCE (EXP_REF, 0) - COALESCE (REVENUE, 0) - COALESCE ([CLIENT.REC], 0) - COALESCE ([CLIENT.SHORT], 0)  - COALESCE ([SPOUSE.SHORT], 0) + COALESCE (ADJUSTMENT, 0) AS BF
 
FROM         
(SELECT     
COALESCE (Disb.SIN, Rcpts.SIN, Adjst.strSIN) AS SIN, 
COALESCE (Disb.YEAR, Rcpts.[Receipt.Year], Adjst.[Adj.Year]) AS YEAR, 
SUM(Disb.[T1 REFUND]) as [T1 REFUND],
SUM(Disb.OWES_REV) as [OWES_REV], 
SUM(Disb.EXP_REF)  as [EXP_REF], 
SUM(Rcpts.REVENUE) as [REVENUE],
SUM(Rcpts.[CLIENT.REC]) as [CLIENT.REC],
SUM(Rcpts.[CLIENT.SHORT]) as [CLIENT.SHORT],
SUM(Rcpts.[SPOUSE.SHORT]) as [SPOUSE.SHORT],
SUM(Adjst.ADJUSTMENT) as [ADJUSTMENT]
FROM          
 
(SELECT     
YEAR, 
SIN, 
C_T1REFUND AS [T1 REFUND], 
C_OWES_REV AS OWES_REV, 
C_SCH1_REFUND AS EXP_REF
FROM          
Disbursements
WHERE      (CKDATE <= '10/31/2007')
 
GROUP BY SIN, YEAR, C_T1REFUND, C_OWES_REV, C_SCH1_REFUND ) AS Disb   FULL OUTER JOIN
 
(SELECT     
SIN, 
YEAR AS [Receipt.Year], 
SUM(CASE WHEN Receipts.[Type] = 'RV' THEN COALESCE (Receipts.AMT_REC, 0) - COALESCE (Receipts.INT_AMT, 0) ELSE 0 END) AS REVENUE, 
SUM(CASE WHEN Receipts.[Type] = 'CL' THEN COALESCE (Receipts.AMT_REC, 0) ELSE 0 END) AS [CLIENT.REC], 
SUM(CASE WHEN Receipts.[Type] = 'CS' THEN COALESCE (Receipts.AMT_REC, 0) ELSE 0 END) AS [CLIENT.SHORT], 
SUM(CASE WHEN Receipts.[Type] = 'SS' THEN COALESCE (Receipts.AMT_REC, 0) ELSE 0 END) AS [SPOUSE.SHORT]
FROM          Receipts
WHERE      (DATE_REC <= '10/31/2007') 
GROUP BY SIN, YEAR) AS Rcpts ON Rcpts.SIN = Disb.SIN AND Rcpts.[Receipt.Year] = Disb.YEAR FULL OUTER JOIN
 
(SELECT     
strSIN, 
SUM(decAMT) AS ADJUSTMENT, 
strYear AS [Adj.Year]
FROM          tblAdjustments
WHERE      (Date <= '10/31/2007')
GROUP BY strSIN, strYear) AS Adjst ON Adjst.strSIN IN (Disb.SIN, Rcpts.SIN) Group By COALESCE (Disb.SIN, Rcpts.SIN, Adjst.strSIN), COALESCE (Disb.YEAR, Rcpts.[Receipt.Year], Adjst.[Adj.Year]) ) AS tblResult

Open in new window

0
 
JohnnyBCJAuthor Commented:
I will try this on Monday morning and will get back to you. I haven't forgotten about this. I greatly appreciate your help!
0
 
JohnnyBCJAuthor Commented:
Almost There!!
Last thing I need done is to have it order by sin by year so that if there are records with the same SIN but multiple years it would all show together. For example

111111111     1990...
111111111     1991...
111111111     1992...
222222222     1990...
222222222     1991...
333333333     1990...    

etc.
0
 
JohnnyBCJAuthor Commented:
I got it!!!
I'm amazed at how much you helped me! Thank you very much!!!

Here is my final code:

SELECT     SIN, YEAR, COALESCE (EXP_REF, 0.00) AS EXP_REF, COALESCE (REVENUE, 0.00) AS REVENUE, COALESCE ([CLIENT.REC], 0.00) AS [CLIENT.REC],
                      COALESCE ([CLIENT.SHORT], 0.00) AS [CLIENT.SHORT], COALESCE ([SPOUSE.SHORT], 0.00) AS [SPOUSE.SHORT], COALESCE (ADJUSTMENT, 0.00)
                      AS ADJUSTMENT, COALESCE (EXP_REF, 0) - COALESCE (REVENUE, 0) - COALESCE ([CLIENT.REC], 0) - COALESCE ([CLIENT.SHORT], 0)
                      - COALESCE ([SPOUSE.SHORT], 0) + COALESCE (ADJUSTMENT, 0) AS BF
FROM         (SELECT     COALESCE (Disb.SIN, Rcpts.SIN, Adjst.strSIN) AS SIN, COALESCE (Disb.YEAR, Rcpts.[Receipt.Year], Adjst.[Adj.Year]) AS YEAR,
                                              SUM(Disb.[T1 REFUND]) AS [T1 REFUND], SUM(Disb.OWES_REV) AS OWES_REV, SUM(Disb.EXP_REF) AS EXP_REF,
                                              SUM(Rcpts.REVENUE) AS REVENUE, SUM(Rcpts.[CLIENT.REC]) AS [CLIENT.REC], SUM(Rcpts.[CLIENT.SHORT]) AS [CLIENT.SHORT],
                                              SUM(Rcpts.[SPOUSE.SHORT]) AS [SPOUSE.SHORT], SUM(Adjst.ADJUSTMENT) AS ADJUSTMENT
                       FROM          (SELECT     YEAR, SIN, C_T1REFUND AS [T1 REFUND], C_OWES_REV AS OWES_REV, C_SCH1_REFUND AS EXP_REF
                                               FROM          Disbursements
                                               WHERE      (CKDATE <= '10/31/2007')
                                               GROUP BY SIN, YEAR, C_T1REFUND, C_OWES_REV, C_SCH1_REFUND) AS Disb FULL OUTER JOIN
                                                  (SELECT     SIN, YEAR AS [Receipt.Year], SUM(CASE WHEN Receipts.[Type] = 'RV' THEN COALESCE (Receipts.AMT_REC, 0)
                                                                           - COALESCE (Receipts.INT_AMT, 0) ELSE 0 END) AS REVENUE,
                                                                           SUM(CASE WHEN Receipts.[Type] = 'CL' THEN COALESCE (Receipts.AMT_REC, 0) ELSE 0 END) AS [CLIENT.REC],
                                                                           SUM(CASE WHEN Receipts.[Type] = 'CS' THEN COALESCE (Receipts.AMT_REC, 0) ELSE 0 END) AS [CLIENT.SHORT],
                                                                           SUM(CASE WHEN Receipts.[Type] = 'SS' THEN COALESCE (Receipts.AMT_REC, 0) ELSE 0 END) AS [SPOUSE.SHORT]
                                                    FROM          Receipts
                                                    WHERE      (DATE_REC <= '10/31/2007')
                                                    GROUP BY SIN, YEAR) AS Rcpts ON Rcpts.SIN = Disb.SIN AND Rcpts.[Receipt.Year] = Disb.YEAR FULL OUTER JOIN
                                                  (SELECT     strSIN, SUM(decAMT) AS ADJUSTMENT, strYear AS [Adj.Year]
                                                    FROM          tblAdjustments
                                                    WHERE      (Date <= '10/31/2007')
                                                    GROUP BY strSIN, strYear) AS Adjst ON Adjst.strSIN IN (Disb.SIN, Rcpts.SIN)
                       GROUP BY COALESCE (Disb.SIN, Rcpts.SIN, Adjst.strSIN), COALESCE (Disb.YEAR, Rcpts.[Receipt.Year], Adjst.[Adj.Year])) AS tblResult
ORDER BY SIN, YEAR
0
 
JohnnyBCJAuthor Commented:
I'm amazed at how far you went to help me. I greatly appreciated it. I do not think I'd get nearly this far if it wasn't for you. Thank you for sharing your knowledge.
0
 
JohnnyBCJAuthor Commented:
I invite you to try to figure out my latest SQL problem. It is located at http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_23801090.html#a22976396
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now