What we need to focus on in the three table structures I have are:
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).
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,