Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.
Become a Premium Member and unlock a new, free course in leading technologies each month.
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;
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
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
Join the community of 500,000 technology professionals and ask your questions.