?
Solved

How do you select values from 3 different tables in a single select statement?

Posted on 2008-11-13
18
Medium Priority
?
216 Views
Last Modified: 2012-06-27
What we need to focus on in the three table structures I have are:

Table Disbursements:
SIN
YEAR
T1_REFUND
OWES_REV
EXP_REF
CKDATE

Table Receipts:
DATE_REC
SIN
TYPE
AMT_REC


Table Adjustments:
SIN
DATE
AMT

The primary keys for all tables are sequence numbers. They are all related by SIN numbers.

What I need to do is below in the Code Snippet. What type of join statement should I use to solve my two problems.

1. I need the select statement to work if there is only 1 record in 1 table (doesn't matter which table).
For Example:
If there is an adjustment but no disbursements / receipts I want the adjustment value to show.
If there is a disbursements but no receipts/adjustments. I want disbursement values to show.
If there is a receipt but no disbursements/adjustments. I want the receipt values to show.

In other words, I do not want the select statement to be based on one particular table. Is this possible with just one select statement?

2. The amount I'm getting for " SUM(tblAdjustments.decAMT) AS ADJUSTMENT" is X times the true value of it where X is the record count for receipts. For example, if I have two receipt records with the same SIN, I will get 2X the value of what (SUM(tblAdjustments.decAMT) AS ADJUSTMENT) is in the database. I believe it has something to do with the joins but I do not know what join I should use.


SELECT     Disbursements.YEAR, Disbursements.SIN, Disbursements.C_T1REFUND AS [T1 REFUND], Disbursements.C_OWES_REV AS OWES_REV, 
                      Disbursements.C_SCH1_REFUND AS EXP_REF, Receipts.YEAR AS [Receipt.Year], 
                      SUM(CASE WHEN Receipts.[Type] = 'RV' THEN COALESCE (Receipts.AMT_REC, 0) ELSE NULL END) AS REVENUE, 
                      SUM(CASE WHEN Receipts.[Type] = 'CL' THEN COALESCE (Receipts.AMT_REC, 0) ELSE NULL END) AS [CLIENT.REC], 
                      SUM(CASE WHEN Receipts.[Type] = 'CS' THEN COALESCE (Receipts.AMT_REC, 0) ELSE NULL END) AS [CLIENT.SHORT], 
                      SUM(CASE WHEN Receipts.[Type] = 'SS' THEN COALESCE (Receipts.AMT_REC, 0) ELSE NULL END) AS [SPOUSE.SHORT], 
                      SUM(tblAdjustments.decAMT) AS ADJUSTMENT
FROM         Disbursements FULL OUTER JOIN
                      tblAdjustments ON Disbursements.SIN = tblAdjustments.strSIN FULL OUTER JOIN
                      Receipts ON tblAdjustments.strSIN = Receipts.SIN AND Disbursements.SIN = Receipts.SIN
WHERE     (Disbursements.CKDATE <= '10/31/2007')
GROUP BY Disbursements.YEAR, Disbursements.SIN, Disbursements.C_T1REFUND, Disbursements.C_OWES_REV, Disbursements.C_SCH1_REFUND, 
                      Receipts.YEAR

Open in new window

0
Comment
Question by:JohnnyBCJ
  • 7
  • 5
  • 2
  • +2
18 Comments
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 600 total points
ID: 22949491
Think you are correct.  It is most likely the FULL OUTER JOIN across 3 tables causing you issues.

I would try first to get the data by doing:

SELECT ...
FROM Disbursements
LEFT JOIN tblAdjustments ...
LEFT JOIN Receipts ...
UNION
SELECT ...
FROM Receipts
LEFT JOIN tblAdjustments ...
LEFT JOIN Disbursements ...
UNION
SELECT ...
FROM tblAdjustments
LEFT JOIN Disbursements ...
LEFT JOIN Receipts ...

Take each case and look at it with one table in focus and then use UNION which will filter out duplicates to get a resultset that is either join of data amongst tables or the one record from table with values and nulls for other two table columns.

You can save this as a view or just make it the derived table in your outer query to get your final resultset.
0
 

Author Comment

by:JohnnyBCJ
ID: 22949821
My problem with 'Union' is from my understanding it can only be used if the values from both tables are the same.
This works:

SELECT     SIN
FROM         Disbursements
UNION
SELECT     SIN
FROM         Receipts
UNION
SELECT     strSIN
FROM         tblAdjustments

This doesn't work:

SELECT SIN, YEAR,  C_T1REFUND AS [T1 REFUND], C_OWES_REV AS OWES_REV, C_SCH1_REFUND AS EXP_REF
FROM Disbursements
UNION

SELECT
SIN,
SUM(CASE WHEN [Type] = 'RV' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS REVENUE,
SUM(CASE WHEN [Type] = 'CL' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [CLIENT.REC],
SUM(CASE WHEN [Type] = 'CS' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [CLIENT.SHORT],
SUM(CASE WHEN [Type] = 'SS' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [SPOUSE.SHORT]
FROM Receipts
group by SIN
UNION

SELECT
strSIN,
SUM(tblAdjustments.decAMT) AS ADJUSTMENT
FROM tblAdjustments
group by strSIN

Problem: All Queries combined using a union, intersect or except operator must have an equal number of expressions in their target lists.

I understand what is causing the issue, it's because disbursements selects 5 objects, receipts selects 5, adjustments select 2. The only common value they all have is SIN/strSIN.



SELECT SIN, YEAR,  C_T1REFUND AS [T1 REFUND], C_OWES_REV AS OWES_REV, C_SCH1_REFUND AS EXP_REF
FROM Disbursements
LEFT JOIN tblAdjustments
LEFT JOIN Receipts
UNION

SELECT
SIN,
SUM(CASE WHEN [Type] = 'RV' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS REVENUE,
SUM(CASE WHEN [Type] = 'CL' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [CLIENT.REC],
SUM(CASE WHEN [Type] = 'CS' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [CLIENT.SHORT],
SUM(CASE WHEN [Type] = 'SS' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [SPOUSE.SHORT]
FROM Receipts
LEFT JOIN tblAdjustments
LEFT JOIN Disbursements
group by SIN
UNION

SELECT
strSIN,
SUM(tblAdjustments.decAMT) AS ADJUSTMENT
FROM tblAdjustments
LEFT JOIN Disbursements
LEFT JOIN Receipts
group by strSIN


Problem: Incorrect syntax near 'UNION', Incorrect syntax near 'GROUP', Incorrect syntax near 'GROUP'.
I'm confused on how you can have a left join and a union. Left Join combines everything that is on the left








SELECT     Disbursements.SIN, Disbursements.YEAR, Disbursements.C_T1REFUND AS [T1 REFUND], Disbursements.C_OWES_REV AS OWES_REV,
                      Disbursements.C_SCH1_REFUND AS EXP_REF
FROM         Disbursements LEFT OUTER JOIN
                      tblAdjustments ON Disbursements.SIN = tblAdjustments.strSIN LEFT OUTER JOIN
                      Receipts ON Disbursements.SIN = Receipts.SIN
UNION
SELECT     Receipts_2.SIN, SUM(CASE WHEN [Type] = 'RV' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS REVENUE,
                      SUM(CASE WHEN [Type] = 'CL' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [CLIENT.REC],
                      SUM(CASE WHEN [Type] = 'CS' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [CLIENT.SHORT],
                      SUM(CASE WHEN [Type] = 'SS' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [SPOUSE.SHORT]
FROM         Receipts AS Receipts_2 LEFT OUTER JOIN
                      tblAdjustments AS tblAdjustments_2 ON Receipts_2.SIN = tblAdjustments_2.strSIN LEFT OUTER JOIN
                      Disbursements AS Disbursements_2 ON Receipts_2.SIN = Disbursements_2.SIN
GROUP BY Receipts_2.SIN
UNION
SELECT     tblAdjustments_1.strSIN, SUM(tblAdjustments_1.decAMT) AS ADJUSTMENT
FROM         tblAdjustments AS tblAdjustments_1 LEFT OUTER JOIN
                      Disbursements AS Disbursements_1 ON tblAdjustments_1.strSIN = Disbursements_1.SIN LEFT OUTER JOIN
                      Receipts AS Receipts_1 ON tblAdjustments_1.strSIN = Receipts_1.SIN
GROUP BY tblAdjustments_1.strSIN


Problem: All Queries combined using a union, intersect or except operator must have an equal number of expressions in their target lists.

Am I getting close to what I should have done?
0
 
LVL 8

Accepted Solution

by:
eszaq earned 1000 total points
ID: 22950253
You have to sellect the same number of columns with the same datatype from all the tables in your UNION query. If you do not have corresponding column in one of the tables select NULL as a value for that column:
SELECT     a,b,c
FROM         Table1
UNION
SELECT     a, NULL as b, c
FROM         Table2
UNION
SELECT     a, b, NULL as c
FROM         Table3
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

 

Author Comment

by:JohnnyBCJ
ID: 22950397
My issue with that Eszaq is that I need to be able to select different columns/data types from different tables in order to get the select statement to work. I understand that if I needed all the SIN from two tables that I would be able to do a 'union all' by selecting sin on both tables and it would give me all the sin on both tables but it doesn't work when I need to be able to select SIN, Year, Amount from adjustment table and SIN, Type, Amount from receipts (or when I need to do a case statement on the receipt type).

Maybe Union is not the way to go. I am thinking that creating a temp table combining all 3 tables may be the way to go. I believe that will allow me to do what I need.
0
 
LVL 8

Expert Comment

by:eszaq
ID: 22950541
Of course, replacement value for missing column does not necessarily have to be NULL. You can populate field with some dummy value (of the same data type), or use CAST / CONVERT functions to stick in value from field with different datatype - whatever makes sense in terms of making result set easier to read and meaningful for your application.
BUT you have to list ALL the columns in the same order in each select in your query:
(BTW is it typo - strSIN? If not and it is text type not number, you'll have to convert numbers tochar)
SELECT SIN, YEAR,  C_T1REFUND AS [T1 REFUND], C_OWES_REV AS OWES_REV, C_SCH1_REFUND AS EXP_REF,
NULL AS REVENUE, NULL AS [CLIENT.REC], NULL AS [CLIENT.SHORT], NULL AS [SPOUSE.SHORT],
AS ADJUSTMENT
FROM Disbursements
LEFT JOIN tblAdjustments
LEFT JOIN Receipts
UNION
 
SELECT
SIN, NULL as YEAR, NULL AS [T1 REFUND], NULL AS OWES_REV, NULL AS EXP_REF
SUM(CASE WHEN [Type] = 'RV' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS REVENUE,
SUM(CASE WHEN [Type] = 'CL' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [CLIENT.REC],
SUM(CASE WHEN [Type] = 'CS' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [CLIENT.SHORT],
SUM(CASE WHEN [Type] = 'SS' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [SPOUSE.SHORT],
0 AS ADJUSTMENT
FROM Receipts
LEFT JOIN tblAdjustments
LEFT JOIN Disbursements
group by SIN
UNION
 
SELECT
strSIN,  NULL as YEAR, AS [T1 REFUND], NULL AS OWES_REV, NULL AS EXP_REF
NULL AS REVENUE, NULL AS [CLIENT.REC], NULL AS [CLIENT.SHORT], NULL AS [SPOUSE.SHORT]
SUM(tblAdjustments.decAMT) AS ADJUSTMENT
FROM tblAdjustments
LEFT JOIN Disbursements
LEFT JOIN Receipts
 
GROUP by 1

Open in new window

0
 
LVL 8

Expert Comment

by:eszaq
ID: 22950557
Not sure if I grabbed right query to alter, just wanted to demonstrate the point.
0
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 600 total points
ID: 22950808
That was my point with UNION too.  All three queries have the same tables, so you should be able to construct the same columns on all the select statements replacing NULL with whatever you want.  For example on money fields that you eventual sum, you could do:

ISNULL(C_T1REFUND, 0) AS [T1 REFUND]

OR

-- use the column you want in all the queries since non-existence will result in NULL if that is what you want
C_T1REFUND AS [T1 REFUND]
0
 
LVL 8

Expert Comment

by:eszaq
ID: 22950822
Oh, I completely overlooked that you have GROUP BY clauses. Try this:
SELECT SIN, 
YEAR,  C_T1REFUND AS [T1 REFUND], C_OWES_REV AS OWES_REV, C_SCH1_REFUND AS EXP_REF,
NULL AS REVENUE, NULL AS [CLIENT.REC], NULL AS [CLIENT.SHORT], NULL AS [SPOUSE.SHORT],
NULL AS ADJUSTMENT
FROM Disbursements
LEFT JOIN tblAdjustments
LEFT JOIN Receipts
UNION
 
SELECT SIN, 
NULL as YEAR, NULL AS [T1 REFUND], NULL AS OWES_REV, NULL AS EXP_REF,
SUM(CASE WHEN [Type] = 'RV' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS REVENUE,
SUM(CASE WHEN [Type] = 'CL' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [CLIENT.REC],
SUM(CASE WHEN [Type] = 'CS' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [CLIENT.SHORT],
SUM(CASE WHEN [Type] = 'SS' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [SPOUSE.SHORT],
SUM(NULL) AS ADJUSTMENT
FROM Receipts
LEFT JOIN tblAdjustments
LEFT JOIN Disbursements
GROUP by  1,2,3,4,5
 
UNION
 
SELECT strSIN,  
NULL as YEAR, NULL AS [T1 REFUND], NULL AS OWES_REV, NULL AS EXP_REF,
SUM(NULL) AS REVENUE, SUM(NULL) AS [CLIENT.REC], SUM(NULL) AS [CLIENT.SHORT], SUM(NULL) AS [SPOUSE.SHORT],
SUM(tblAdjustments.decAMT) AS ADJUSTMENT
FROM tblAdjustments
LEFT JOIN Disbursements
LEFT JOIN Receipts
GROUP by 1,2,3,4,5

Open in new window

0
 
LVL 8

Assisted Solution

by:eszaq
eszaq earned 1000 total points
ID: 22950869
And get rid of JOINS in all the queries. You do not need them at this point. One step at a time. First see if you are getting what you need and go with derived table solution proposed by mwvisa1.  Sorry for too many copy-paste bloopers. Have too many thing on my plate at the moment.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 22952683
The general overal query should end up something like this:


SELECT     Disb.YEAR, Disb.SIN, Disb.[T1 REFUND], Disb.OWES_REV, Disb.EXP_REF,
           Rcpts.[Receipt.Year], Rcpts.REVENUE, Rcpts.[CLIENT.REC], Rcpts.[CLIENT.SHORT], Rcpts.[SPOUSE.SHORT],
           Adjust.ADJUSTMENT
FROM (
    SELECT     Disbursements.YEAR, Disbursements.SIN, Disbursements.C_T1REFUND AS [T1 REFUND], Disbursements.C_OWES_REV AS OWES_REV,
                          Disbursements.C_SCH1_REFUND AS EXP_REF
    FROM         Disbursements
    WHERE     (Disbursements.CKDATE <= '10/31/2007')
) AS Disb
FULL OUTER JOIN (
    SELECT     Receipts.SIN, Receipts.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
    GROUP BY     Receipts.SIN, Receipts.YEAR
) AS Rcpts ON Rcpts.SIN = Disb.SIN AND Rcpts.[Receipt.Year] = Disb.YEAR
FULL OUTER JOIN (
    SELECT     tblAdjustments.SIN,  /*, tblAdjustments.YEAR AS [YEAR], */
                      SUM(tblAdjustments.decAMT) AS ADJUSTMENT
    FROM         tblAdjustments
    GROUP BY     tblAdjustments.SIN /*, tblAdjustments.YEAR*/
) AS Adjst ON Adjst.strSIN IN (Disb.SIN, Rcpts.SIN)
--ORDER BY ...
0
 

Author Comment

by:JohnnyBCJ
ID: 22952953
I currently have

SELECT     SIN, YEAR, C_T1REFUND AS [T1 REFUND], C_OWES_REV AS OWES_REV, C_SCH1_REFUND AS EXP_REF, NULL AS REVENUE, NULL
                      AS [CLIENT.REC], NULL AS [CLIENT.SHORT], NULL AS [SPOUSE.SHORT], NULL AS ADJUSTMENT
FROM         Disbursements
WHERE     (CKDATE <= '10/31/2007')
GROUP BY SIN, YEAR, C_T1REFUND, C_OWES_REV, C_SCH1_REFUND
UNION
SELECT     SIN, YEAR, NULL AS [T1 REFUND], NULL AS OWES_REV, NULL AS EXP_REF, SUM(CASE WHEN [Type] = 'RV' THEN COALESCE (AMT_REC, 0)
                      ELSE NULL END) AS REVENUE, SUM(CASE WHEN [Type] = 'CL' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [CLIENT.REC],
                      SUM(CASE WHEN [Type] = 'CS' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [CLIENT.SHORT],
                      SUM(CASE WHEN [Type] = 'SS' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [SPOUSE.SHORT], NULL AS ADJUSTMENT
FROM         Receipts
WHERE     (DATE_REC <= '10/31/2007')
GROUP BY SIN, YEAR
UNION
SELECT     strSIN, strYear AS YEAR, NULL AS [T1 REFUND], NULL AS OWES_REV, NULL AS EXP_REF, NULL AS REVENUE, NULL AS [CLIENT.REC], NULL
                      AS [CLIENT.SHORT], NULL AS [SPOUSE.SHORT], SUM(decAMT) AS ADJUSTMENT
FROM         tblAdjustments
WHERE     (Date <= '10/31/2007')
GROUP BY strSIN, strYear

Which is great but my issue now is that it gives me 3 seperate records (for one each select statement). Is it possible to combine all 3 rows into 1? Like one huge group by SIN/Year?


0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 22953220
Yes, by using a FULL OUTER JOIN :-) .
0
 

Author Comment

by:JohnnyBCJ
ID: 22953688
Syntax please?
Remember I want this to bring back results that are not table depended (which is what is happening). I'm afraid that full outer join will only bring back the results that have something in all 3 Disbursements/Adjustments/Receipts table. Maybe I'm wrong.

The above issue is also combined with the fact that of Disbursement.SIN.
If Disbursement.SIN returns a null I want it to be replaced by Receipt.SIN or Adjustment.SIN.


SELECT     Disb.SIN, Disb.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')) 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
                            FROM          tblAdjustments
                            WHERE      (Date <= '10/31/2007')
                            GROUP BY strSIN) AS Adjst ON Adjst.strSIN IN (Disb.SIN, Rcpts.SIN)

Works but it is finding receipt/adjustment records where there is no disbursements (Which is great!!!!) but in that case I need to fill in the SIN number from whatever record it is found in (tblAdjustment.strSIN or Receipt.SIN). How do I manage to do that?
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 400 total points
ID: 23040376
the right outer or full outer is arguably a better choice - so long as you can resolve the "common" elements needed to group by. And from your comment above, wouldn't you just use adjst.strSIN ? Does it really matter if it is disbursements or receipt - and if so, why not another join then you alias tbladjustments for either a join to disbursements and another as a join to receipt ?

with your union query, to bring it back to one, simply wrap a select around it using MAX(column) for anything not part of the group by (which really needs to be common element from each of the unioned selects). You might have to fix the aggregate types, but should give you what you need - oh, and in these situations, do not use NULL as a "filler" use the datatype such as 0 for int and 0.0 for decimals etc. also, going this way, you do not need the individual group by's - and if you included date (as a query item and in the group by) you could also move that to the outer query - but then the subquery will be looking at more data (fine if datasets are reasonable - not so good if they are already large).


select SIN, YEAR, max([T1 REFUND]) AS [T1 REFUND], max(OWES_REV) AS OWES_REV, max(exp_ref) AS EXP_REF, sum(isnull(revenue,0)) AS REVENUE, sum([CLIENT.REC]) as [CLIENT.REC], sum([CLIENT.SHORT]) AS [CLIENT.SHORT], sum([spouse.short]) AS [SPOUSE.SHORT], sum(isnull(adjustment,0)) AS ADJUSTMENT

from
(
SELECT     SIN, YEAR, C_T1REFUND AS [T1 REFUND], C_OWES_REV AS OWES_REV, C_SCH1_REFUND AS EXP_REF, NULL AS REVENUE, NULL
                      AS [CLIENT.REC], NULL AS [CLIENT.SHORT], NULL AS [SPOUSE.SHORT], NULL AS ADJUSTMENT
FROM         Disbursements
WHERE     (CKDATE <= '10/31/2007')

UNION
SELECT     SIN, YEAR, NULL AS [T1 REFUND], NULL AS OWES_REV, NULL AS EXP_REF, (CASE WHEN [Type] = 'RV' THEN COALESCE (AMT_REC, 0)
                      ELSE NULL END) AS REVENUE, (CASE WHEN [Type] = 'CL' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [CLIENT.REC],
                      (CASE WHEN [Type] = 'CS' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [CLIENT.SHORT],
                      (CASE WHEN [Type] = 'SS' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [SPOUSE.SHORT], NULL AS ADJUSTMENT
FROM         Receipts
WHERE     (DATE_REC <= '10/31/2007')

UNION
SELECT     strSIN, strYear AS YEAR, NULL AS [T1 REFUND], NULL AS OWES_REV, NULL AS EXP_REF, NULL AS REVENUE, NULL AS [CLIENT.REC], NULL
                      AS [CLIENT.SHORT], NULL AS [SPOUSE.SHORT], SUM(decAMT) AS ADJUSTMENT
FROM         tblAdjustments
WHERE     (Date <= '10/31/2007')

) as SUBQ

GROUP BY SIN, YEAR                  -- and maybe from the first sub query element, [T1 REFUND], OWES_REV, EXP_REF

0
 

Author Comment

by:JohnnyBCJ
ID: 23055625
Thank you all for helping me. I really appreciate the help! The final code snippet is from below.

    Dim da As New SqlDataAdapter(" SELECT     SIN, SUM(COALESCE (EXP_REF, 0) + COALESCE (PayBack, 0) - COALESCE (REVENUE, 0) - COALESCE ([CLIENT.REC], 0) - COALESCE ([CLIENT.SHORT],     " & _
                    "                      0) - COALESCE ([SPOUSE.SHORT], 0) + COALESCE (ADJUSTMENT, 0)) AS BF, SUM(CSCH1) AS CSCH1, SUM(CREVREC) + SUM(INT_AMT2) AS CREVREC,                   " & _
                    "                      SUM(INT_AMT2) AS CINT, SUM(PBack2) AS CPBACK, SUM([CLIENT.REC2]) AS CL, SUM([CLIENT.SHORT2]) AS CS, SUM([SPOUSE.SHORT2]) AS SS,                      " & _
                    "                      SUM(ADJUSTMENT2) AS ADJ2, SUM([CLIENT.REC2]) + SUM([CLIENT.SHORT2]) + SUM([SPOUSE.SHORT2]) + SUM(ADJUSTMENT2 * - 1) AS CSCOL,                        " & _
                    "                      SUM(COALESCE (EXP_REF, 0)) + SUM(COALESCE (PayBack, 0)) - SUM(COALESCE (REVENUE, 0)) - SUM(COALESCE ([CLIENT.REC], 0))                               " & _
                    "                      - SUM(COALESCE ([CLIENT.SHORT], 0)) - SUM(COALESCE ([SPOUSE.SHORT], 0)) + SUM(COALESCE (ADJUSTMENT, 0)) + SUM(COALESCE (CSCH1, 0))                   " & _
                    "                      + SUM(COALESCE (PBack2, 0)) - SUM(COALESCE (CREVREC, 0)) - SUM(COALESCE ([CLIENT.REC2], 0)) - SUM(COALESCE ([CLIENT.SHORT2], 0))                     " & _
                    "                      - SUM(COALESCE ([SPOUSE.SHORT2], 0)) + SUM(COALESCE (ADJUSTMENT2, 0)) AS AR " & _
                    "                       FROM         (SELECT     SIN, YEAR, SUM(COALESCE (C_SCH1_REFUND, 0)) AS EXP_REF, 0 AS AMT_REC, 0 AS INT_AMT, 0 AS REVENUE, 0 AS [CLIENT.REC],       " & _
                    "                                              0 AS [CLIENT.SHORT], 0 AS [SPOUSE.SHORT], 0 AS ADJUSTMENT, SUM(CASE WHEN [Adinfo] = 'P' THEN COALESCE (CKAMT, 0)             " & _
                    "                                              ELSE 0 END) AS PayBack, 0 AS PBack2, 0 AS CSCH1, 0 AS AMT_REC2, 0 AS INT_AMT2, 0 AS CREVREC, 0 AS [CLIENT.REC2],             " & _
                    "                                              0 AS [CLIENT.SHORT2], 0 AS [SPOUSE.SHORT2], 0 AS ADJUSTMENT2                                                                 " & _
                    "                       FROM          Disbursements                                                                                                                         " & _
                    "                       WHERE      (CKDATE <= '" & dateFiscalYearEndMinusOne & "')                                                                                                                 " & _
                    "                       GROUP BY SIN, YEAR                                                                                                                                  " & _
                    "                       UNION ALL                                                                                                                                           " & _
                    "                       (SELECT     SIN, YEAR, 0 AS EXP_REF, 0 AS AMT_REC, 0 AS INT_AMT, 0 AS REVENUE, 0 AS [CLIENT.REC], 0 AS [CLIENT.SHORT],                              " & _
                    "                                               0 AS [SPOUSE.SHORT], 0 AS ADJUSTMENT, 0 AS PayBack, SUM(CASE WHEN [Adinfo] = 'P' THEN COALESCE (CKAMT, 0) ELSE 0 END)       " & _
                    "                                               AS PBack2, SUM(COALESCE (C_SCH1_REFUND, 0)) AS CSCH1, 0 AS AMT_REC2, 0 AS INT_AMT2, 0 AS CREVREC, 0 AS [CLIENT.REC2],       " & _
                    "                                               0 AS [CLIENT.SHORT2], 0 AS [SPOUSE.SHORT2], 0 AS ADJUSTMENT2                                                                " & _
                    "                        FROM         Disbursements AS Disbursements_1                                                                                                      " & _
                    "                        WHERE     (CKDATE > '" & dateFiscalYearEndMinusOne & "') AND (CKDATE <= '" & dateFiscalYearEnd & "')                                                                                     " & _
                    "                        GROUP BY SIN, YEAR                                                                                                                                 " & _
                    "                      UNION ALL                                                                                                                                            " & _
                    "                      SELECT     SIN, YEAR, 0 AS EXP_REF, SUM(COALESCE (AMT_REC, 0)) AS AMT_REC, SUM(COALESCE (INT_AMT, 0)) AS INT_AMT,                                    " & _
                    "                                            SUM(CASE WHEN [Type] = 'RV' THEN COALESCE (AMT_REC, 0) - COALESCE (INT_AMT, 0) ELSE 0 END) AS REVENUE,                         " & _
                    "                                            SUM(CASE WHEN [Type] = 'CL' THEN COALESCE (AMT_REC, 0) ELSE 0 END) AS [CLIENT.REC],                                            " & _
                    "                                            SUM(CASE WHEN [Type] = 'CS' THEN COALESCE (AMT_REC, 0) ELSE 0 END) AS [CLIENT.SHORT],                                          " & _
                    "                                            SUM(CASE WHEN [Type] = 'SS' THEN COALESCE (AMT_REC, 0) ELSE 0 END) AS [SPOUSE.SHORT], 0 AS ADJUSTMENT, 0 AS PayBack,           " & _
                    "                                            0 AS PBack2, 0 AS CSCH1, 0 AS AMT_REC2, 0 AS INT_AMT2, 0 AS CREVREC, 0 AS [CLIENT.REC2], 0 AS [CLIENT.SHORT2],                 " & _
                    "                                            0 AS [SPOUSE.SHORT2], 0 AS ADJUSTMENT2                                                                                         " & _
                    "                      FROM         Receipts                                                                                                                                " & _
                    "                      WHERE     (DATE_REC <= '" & dateFiscalYearEndMinusOne & "')                                                                                                                 " & _
                    "                      GROUP BY SIN, YEAR                                                                                                                                   " & _
                    "                      UNION ALL                                                                                                                                            " & _
                    "                      SELECT     SIN, YEAR, 0 AS EXP_REF, 0 AS AMT_REC, 0 AS INT_AMT, 0 AS REVENUE, 0 AS [CLIENT.REC], 0 AS [CLIENT.SHORT],                                " & _
                    "                                            0 AS [SPOUSE.SHORT], 0 AS ADJUSTMENT, 0 AS PayBack, 0 AS PBack2, 0 AS CSCH1, SUM(COALESCE (AMT_REC, 0)) AS AMT_REC2,           " & _
                    "                                            SUM(COALESCE (INT_AMT, 0)) AS INT_AMT2, SUM(CASE WHEN [Type] = 'RV' THEN COALESCE (AMT_REC, 0) - COALESCE (INT_AMT, 0)         " & _
                    "                                            ELSE 0 END) AS CREVREC, SUM(CASE WHEN [Type] = 'CL' THEN COALESCE (AMT_REC, 0) ELSE 0 END) AS [CLIENT.REC2],                   " & _
                    "                                            SUM(CASE WHEN [Type] = 'CS' THEN COALESCE (AMT_REC, 0) ELSE 0 END) AS [CLIENT.SHORT2],                                         " & _
                    "                                            SUM(CASE WHEN [Type] = 'SS' THEN COALESCE (AMT_REC, 0) ELSE 0 END) AS [SPOUSE.SHORT2], 0 AS ADJUSTMENT2                        " & _
                    "                      FROM         Receipts AS Receipts_1                                                                                                                  " & _
                    "                      WHERE     (DATE_REC > '" & dateFiscalYearEndMinusOne & "') AND (DATE_REC <= '" & dateFiscalYearEnd & "')                                                                                   " & _
                    "                      GROUP BY SIN, YEAR                                                                                                                                   " & _
                    "                      UNION ALL                                                                                                                                            " & _
                    "                      SELECT     strSIN, strYear, 0 AS EXP_REF, 0 AS AMT_REC, 0 AS INT_AMT, 0 AS REVENUE, 0 AS [CLIENT.REC], 0 AS [CLIENT.SHORT],                          " & _
                    "                                            0 AS [SPOUSE.SHORT], SUM(decAMT) AS ADJUSTMENT, 0 AS PayBack, 0 AS PBack2, 0 AS CSCH1, 0 AS AMT_REC2, 0 AS INT_AMT2,           " & _
                    "                                            0 AS CREVREC, 0 AS [CLIENT.REC2], 0 AS [CLIENT.SHORT2], 0 AS [SPOUSE.SHORT2], 0 AS ADJUSTMENT2                                 " & _
                    "                      FROM         tblAdjustments                                                                                                                          " & _
                    "                      WHERE     (Date <= '" & dateFiscalYearEndMinusOne & "')                                                                                                                     " & _
                    "                      GROUP BY strSIN, strYear                                                                                                                             " & _
                    "                      UNION ALL                                                                                                                                            " & _
                    "                      SELECT     strSIN, strYear, 0 AS EXP_REF, 0 AS AMT_REC, 0 AS INT_AMT, 0 AS REVENUE, 0 AS [CLIENT.REC], 0 AS [CLIENT.SHORT],                          " & _
                    "                                            0 AS [SPOUSE.SHORT], 0 AS ADJUSTMENT, 0 AS PayBack, 0 AS PBack2, 0 AS CSCH1, 0 AS AMT_REC2, 0 AS INT_AMT2, 0 AS CREVREC,       " & _
                    "                                            0 AS [CLIENT.REC2], 0 AS [CLIENT.SHORT2], 0 AS [SPOUSE.SHORT2], SUM(decAMT) AS ADJUSTMENT2                                     " & _
                    "                      FROM         tblAdjustments AS tblAdjustments_1                                                                                                      " & _
                    "                      WHERE     (Date > '" & dateFiscalYearEndMinusOne & "') AND (Date <= '" & dateFiscalYearEnd & "')                                                                                           " & _
                    "                      GROUP BY strSIN, strYear)) AS HI                                                                                                                     " & _
                    "                      GROUP BY SIN                                                                                                                                         " & _
                    "                      ORDER BY SIN     ", myConnection)

Open in new window

0
 

Author Comment

by:JohnnyBCJ
ID: 23055648
The issue with full outer or right join is that there is not necessarily a record in every table and I cannot base it on any 1 table. That is why I went with the union and group by to get rid of the duplicates. Thanks again. Enjoy the points!
0
 

Author Closing Comment

by:JohnnyBCJ
ID: 31516343
I really appreciate the help. I cannot imagine being able to get as far as I am (as quickly as I am) without the help of people like you! Enjoy the points
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23055886
With comments like that and a good healthy approach with good dialogue and feedback, it is an absolute joy and pleasure to work with you, look forward to doing it again...
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question