Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.
SELECT c.LastOffice, c.LNAME, c.FNAME, ms.SIN, ms.YEAR, ms.CSCH1, ms.CREVREC, ms.CPBACK, ms.CSCOL AS CLREC, ms.ADJ2, ms.AR FROM
(SELECT
SIN, YEAR, 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') AS CSCOL, 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, 0 AS AMT_REC, 0 AS INT_AMT, 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 <= STR_TO_DATE('10/31/2010', '%m/%d/%Y')) GROUP BY SIN, YEAR UNION ALL SELECT SIN, YEAR, SUM(COALESCE (AMT_REC, 0)) AS AMT_REC, SUM(COALESCE (INT_AMT, 0)) AS INT_AMT, 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 <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))
GROUP BY SIN, YEAR
UNION ALL
SELECT SIN, YEAR, 0 AS AMT_REC, 0 AS INT_AMT, 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 <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))
GROUP BY SIN, YEAR
UNION ALL
SELECT strSIN, strYear, 0 AS AMT_REC, 0 AS INT_AMT, 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 <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))
GROUP BY strSIN, strYear) AS HI
GROUP BY SIN, YEAR
HAVING (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)) <> 0)) AS ms
INNER JOIN Clients AS c ON c.SIN = ms.SIN
GROUP BY c.LNAME, c.FNAME, ms.SIN, c.LastOffice, ms.YEAR, ms.CSCH1, ms.CREVREC, ms.CINT, ms.CPBACK, ms.CL, ms.CS, ms.SS, ms.ADJ2, ms.CSCOL, ms.AR
ORDER BY c.LNAME, c.FNAME, ms.SIN;
SELECT
c.LastOffice, c.LNAME, c.FNAME, ms.SIN, ms.YEAR, ms.CSCH1, ms.CREVREC, ms.CPBACK, ms.CSCOL AS CLREC, ms.ADJ2, ms.AR
FROM
(
SELECT
SIN, YEAR, 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') AS CSCOL, 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, 0 AS AMT_REC, 0 AS INT_AMT, 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 <= STR_TO_DATE('10/31/2010', '%m/%d/%Y')) GROUP BY SIN, YEAR UNION ALL SELECT SIN, YEAR, SUM(COALESCE (AMT_REC, 0)) AS AMT_REC, SUM(COALESCE (INT_AMT, 0)) AS INT_AMT, 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 <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))
GROUP BY SIN, YEAR
UNION ALL
SELECT SIN, YEAR, 0 AS AMT_REC, 0 AS INT_AMT, 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 <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))
GROUP BY SIN, YEAR
UNION ALL
SELECT strSIN, strYear, 0 AS AMT_REC, 0 AS INT_AMT, 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 <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))
GROUP BY strSIN, strYear
) AS HI
GROUP BY SIN, YEAR
HAVING (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)) <> 0)
) AS ms
INNER JOIN Clients AS c
ON c.SIN = ms.SIN
GROUP BY c.LNAME, c.FNAME, ms.SIN, c.LastOffice, ms.YEAR, ms.CSCH1, ms.CREVREC, ms.CINT, ms.CPBACK, ms.CL, ms.CS, ms.SS, ms.ADJ2, ms.CSCOL, ms.AR
ORDER BY c.LNAME, c.FNAME, ms.SIN;
--
--
--
SELECT count(*)
FROM
(
SELECT
SIN, YEAR, 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') AS CSCOL, 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, 0 AS AMT_REC, 0 AS INT_AMT, 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 <= STR_TO_DATE('10/31/2010', '%m/%d/%Y')) GROUP BY SIN, YEAR UNION ALL SELECT SIN, YEAR, SUM(COALESCE (AMT_REC, 0)) AS AMT_REC, SUM(COALESCE (INT_AMT, 0)) AS INT_AMT, 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 <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))
GROUP BY SIN, YEAR
UNION ALL
SELECT SIN, YEAR, 0 AS AMT_REC, 0 AS INT_AMT, 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 <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))
GROUP BY SIN, YEAR
UNION ALL
SELECT strSIN, strYear, 0 AS AMT_REC, 0 AS INT_AMT, 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 <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))
GROUP BY strSIN, strYear
) AS HI
GROUP BY SIN, YEAR
HAVING (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)) <> 0)
) AS ms
INNER JOIN Clients AS c
ON c.SIN = ms.SIN
SELECT
c.LastOffice,
c.LNAME,
c.FNAME,
ms.SIN,
ms.YEAR,
ms.CSCH1,
ms.CREVREC,
ms.CPBACK,
ms.CSCOL AS CLREC,
ms.ADJ2,
ms.AR
FROM
(
SELECT
SIN, YEAR, 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') AS CSCOL, 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,
0 AS AMT_REC,
0 AS INT_AMT,
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 <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))
GROUP BY SIN, YEAR
UNION ALL
SELECT
SIN,
YEAR,
SUM(COALESCE (AMT_REC, 0)) AS AMT_REC,
SUM(COALESCE (INT_AMT, 0)) AS INT_AMT,
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 <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))
GROUP BY SIN, YEAR
UNION ALL
SELECT
SIN,
YEAR,
0 AS AMT_REC,
0 AS INT_AMT,
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 <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))
GROUP BY SIN, YEAR
UNION ALL
SELECT
strSIN, strYear,
0 AS AMT_REC,
0 AS INT_AMT,
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 <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))
GROUP BY strSIN, strYear
) AS HI
GROUP BY SIN, YEAR
HAVING (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)) <> 0)
) AS ms
INNER JOIN Clients AS c
ON c.SIN = ms.SIN
GROUP BY c.LNAME, c.FNAME, ms.SIN, c.LastOffice, ms.YEAR, ms.CSCH1, ms.CREVREC, ms.CINT, ms.CPBACK, ms.CL, ms.CS, ms.SS, ms.ADJ2, ms.CSCOL, ms.AR
ORDER BY c.LNAME, c.FNAME, ms.SIN;
SELECT SIN, YEAR
, SUM(AMT_REC) AS AMT_REC
, SUM(INT_AMT) AS INT_AMT
, 0 AS PBack2, 0 AS CSCH1
, SUM(AMT_REC) AS AMT_REC2, SUM(INT_AMT) AS INT_AMT2
, SUM(CASE `Type` WHEN 'RV' THEN COALESCE(AMT_REC, 0) - COALESCE(INT_AMT, 0) END) AS CREVREC
, SUM(CASE `Type` WHEN 'CL' THEN AMT_REC END) AS `CLIENT.REC2`
, SUM(CASE `Type` WHEN 'CS' THEN AMT_REC END) AS `CLIENT.SHORT2`
, SUM(CASE `Type` WHEN 'SS' THEN AMT_REC END) AS `SPOUSE.SHORT2`
, 0 AS ADJUSTMENT2
FROM Receipts
WHERE (DATE_REC <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))
GROUP BY SIN, YEAR
SELECT
c.LastOffice,
c.LNAME,
c.FNAME,
ms.SIN,
ms.YEAR,
ms.CSCH1,
ms.CREVREC,
ms.CPBACK,
ms.CSCOL AS CLREC,
ms.ADJ2,
ms.AR
FROM
(
SELECT
SIN, YEAR, 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`) AS CSCOL, SUM(CSCH1) + 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,
0 AS AMT_REC,
0 AS INT_AMT,
SUM(CASE `Adinfo` WHEN 'P' THEN CKAMT 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 <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))
GROUP BY SIN, YEAR
UNION ALL
SELECT SIN, YEAR
, SUM(AMT_REC) AS AMT_REC
, SUM(INT_AMT) AS INT_AMT
, 0 AS PBack2, 0 AS CSCH1
, SUM(AMT_REC) AS AMT_REC2, SUM(INT_AMT) AS INT_AMT2
, SUM(CASE `Type` WHEN 'RV' THEN COALESCE(AMT_REC, 0) - COALESCE(INT_AMT, 0) END) AS CREVREC
, SUM(CASE `Type` WHEN 'CL' THEN AMT_REC END) AS `CLIENT.REC2`
, SUM(CASE `Type` WHEN 'CS' THEN AMT_REC END) AS `CLIENT.SHORT2`
, SUM(CASE `Type` WHEN 'SS' THEN AMT_REC END) AS `SPOUSE.SHORT2`
, 0 AS ADJUSTMENT2
FROM Receipts
WHERE (DATE_REC <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))
GROUP BY SIN, YEAR
UNION ALL
SELECT
strSIN, strYear,
0 AS AMT_REC,
0 AS INT_AMT,
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 <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))
GROUP BY strSIN, strYear
) AS HI
GROUP BY SIN, YEAR
HAVING ((SUM(CSCH1) + SUM(PBack2) - SUM(CREVREC) - SUM(`CLIENT.REC2`)
- SUM(`CLIENT.SHORT2`) - SUM(`SPOUSE.SHORT2`) + SUM(ADJUSTMENT2)) <> 0)
) AS ms
INNER JOIN Clients AS c
ON c.SIN = ms.SIN
ORDER BY c.LNAME, c.FNAME, ms.SIN;
SELECT
c.LastOffice,
c.LNAME,
c.FNAME,
ms.SIN,
ms.YEAR,
ms.CSCH1,
ms.CREVREC,
ms.CPBACK,
ms.CSCOL AS CLREC,
ms.ADJ2,
ms.AR
FROM
(
SELECT
SIN, YEAR, 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`) AS CSCOL, SUM(CSCH1) + 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,
0 AS AMT_REC,
0 AS INT_AMT,
CASE `Adinfo` WHEN 'P' THEN CKAMT END AS PBack2,
C_SCH1_REFUND 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 <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))
UNION ALL
SELECT SIN, YEAR
, AMT_REC
, INT_AMT
, 0 AS PBack2, 0 AS CSCH1
, AMT_REC AS AMT_REC2, INT_AMT AS INT_AMT2
, CASE `Type` WHEN 'RV' THEN COALESCE(AMT_REC, 0) - COALESCE(INT_AMT, 0) END AS CREVREC
, CASE `Type` WHEN 'CL' THEN AMT_REC END AS `CLIENT.REC2`
, CASE `Type` WHEN 'CS' THEN AMT_REC END AS `CLIENT.SHORT2`
, CASE `Type` WHEN 'SS' THEN AMT_REC END AS `SPOUSE.SHORT2`
, 0 AS ADJUSTMENT2
FROM Receipts
WHERE (DATE_REC <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))
UNION ALL
SELECT
strSIN, strYear,
0 AS AMT_REC,
0 AS INT_AMT,
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`,
decAMT AS ADJUSTMENT2
FROM tblAdjustments AS tblAdjustments_1
WHERE (Date <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))
) AS HI
GROUP BY SIN, YEAR
HAVING ((SUM(CSCH1) + SUM(PBack2) - SUM(CREVREC) - SUM(`CLIENT.REC2`)
- SUM(`CLIENT.SHORT2`) - SUM(`SPOUSE.SHORT2`) + SUM(ADJUSTMENT2)) <> 0)
) AS ms
INNER JOIN Clients AS c
ON c.SIN = ms.SIN
ORDER BY c.LNAME, c.FNAME, ms.SIN;
SELECT
SIN,
YEAR,
0 AS AMT_REC,
0 AS INT_AMT,
SUM(CASE `Adinfo` WHEN 'P' THEN CKAMT 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 <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))
GROUP BY SIN, YEAR
UNION all
SELECT * FROM
(
SELECT SIN, YEAR
, SUM(AMT_REC) AS AMT_REC
, SUM(INT_AMT) AS INT_AMT
, 0 AS PBack2, 0 AS CSCH1
, SUM(AMT_REC) AS AMT_REC2, SUM(INT_AMT) AS INT_AMT2
, SUM(CASE `Type` WHEN 'RV' THEN COALESCE(AMT_REC, 0) - COALESCE(INT_AMT, 0) END) AS CREVREC
, SUM(CASE `Type` WHEN 'CL' THEN AMT_REC END) AS `CLIENT.REC2`
, SUM(CASE `Type` WHEN 'CS' THEN AMT_REC END) AS `CLIENT.SHORT2`
, SUM(CASE `Type` WHEN 'SS' THEN AMT_REC END) AS `SPOUSE.SHORT2`
, 0 AS ADJUSTMENT2
FROM Receipts
WHERE (DATE_REC <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))
GROUP BY SIN, YEAR
) t0
SELECT * FROM
(
SELECT
SIN,
YEAR,
0 AS AMT_REC,
0 AS INT_AMT,
SUM(CASE `Adinfo` WHEN 'P' THEN CKAMT 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 <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))
GROUP BY SIN, YEAR
UNION all
SELECT SIN, YEAR
, SUM(AMT_REC) AS AMT_REC
, SUM(INT_AMT) AS INT_AMT
, 0 AS PBack2, 0 AS CSCH1
, SUM(AMT_REC) AS AMT_REC2, SUM(INT_AMT) AS INT_AMT2
, SUM(CASE `Type` WHEN 'RV' THEN COALESCE(AMT_REC, 0) - COALESCE(INT_AMT, 0) END) AS CREVREC
, SUM(CASE `Type` WHEN 'CL' THEN AMT_REC END) AS `CLIENT.REC2`
, SUM(CASE `Type` WHEN 'CS' THEN AMT_REC END) AS `CLIENT.SHORT2`
, SUM(CASE `Type` WHEN 'SS' THEN AMT_REC END) AS `SPOUSE.SHORT2`
, 0 AS ADJUSTMENT2
FROM Receipts
WHERE (DATE_REC <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))
GROUP BY SIN, YEAR
) t0
order by 1, 2
SELECT * FROM
(
SELECT
SIN,
YEAR,
0 AS AMT_REC,
0 AS INT_AMT,
SUM(CASE `Adinfo` WHEN 'P' THEN CKAMT 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 <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))
GROUP BY SIN, YEAR
UNION all
SELECT
SIN,
YEAR,
SUM(AMT_REC) AS AMT_REC,
SUM(INT_AMT) AS INT_AMT,
0 AS PBack2,
0 AS CSCH1,
SUM(AMT_REC) AS AMT_REC2,
SUM(INT_AMT) AS INT_AMT2,
SUM(CASE `Type` WHEN 'RV' THEN COALESCE(AMT_REC, 0) - COALESCE(INT_AMT, 0) ELSE 0 END) AS CREVREC,
SUM(CASE `Type` WHEN 'CL' THEN AMT_REC ELSE 0 END) AS `CLIENT.REC2`,
SUM(CASE `Type` WHEN 'CS' THEN AMT_REC ELSE 0 END) AS `CLIENT.SHORT2`,
SUM(CASE `Type` WHEN 'SS' THEN AMT_REC ELSE 0 END) AS `SPOUSE.SHORT2`,
0 AS ADJUSTMENT2
FROM Receipts
WHERE (DATE_REC <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))
GROUP BY SIN, YEAR
) t0
order by 1, 2
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join the community of 500,000 technology professionals and ask your questions.