The query looks reasonable.
Can you describe it? How many rows are being processed? How many returned? Have you seen an explain plan? Are the appropriate indexes in place? Can you add indexes if necessary?
Kent
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
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
sql calculate averages | 18 | 47 | |
Need help subtracting a value within my script | 7 | 42 | |
Unable to save view in SSMS | 21 | 59 | |
SQL Query Conversion of IIF statement into CASE - Syntax issue | 17 | 33 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
20 Experts available now in Live!