Run the below into the DMS Database
BEGIN
SELECT c.client_id AS ClientID ,
COALESCE(CONVERT(DECIMAL(15, 2), c.client_balance), 0) AS ClientBalance ,
CASE WHEN COALESCE(CONVERT(DECIMAL(15, 2), c.client_balance),
0) > 0
THEN COALESCE(CONVERT(DECIMAL(15, 2), c.client_balance),
0)
ELSE 0
END AS PositiveBalance ,
CASE WHEN COALESCE(CONVERT(DECIMAL(15, 2), c.client_balance),
0) < 0
THEN COALESCE(CONVERT(DECIMAL(15, 2), c.client_balance),
0)
ELSE 0
END AS NegativeBalance ,
COALESCE(chk.uncleared_amt, 0) + COALESCE(ph.check_amt,
0) AS UnclearedCheque ,
CASE WHEN ( COALESCE(CONVERT(DECIMAL(15, 2), c.client_balance),
0) )
+ ( COALESCE(chk.uncleared_amt, 0)
+ COALESCE(ph.check_amt, 0) ) < 0 THEN 0
ELSE ( COALESCE(CONVERT(DECIMAL(15, 2), c.client_balance),
0) )
+ ( COALESCE(chk.uncleared_amt, 0)
+ COALESCE(ph.check_amt, 0) )
END AS TotalByClient
FROM client AS c
LEFT JOIN ( SELECT c.client_id AS client_id ,
SUM(CASE WHEN ph.bdn_code = 'D'
THEN CONVERT(DECIMAL(15,
2), ph.amount)
- CONVERT(DECIMAL(15,
2), ( CONVERT(DECIMAL(15,
2), ph.amount)
* CONVERT(MONEY, ph.transaction_percent) ))
ELSE CONVERT(DECIMAL(15,
2), ph.amount)
END) AS check_amt
FROM payment_history AS ph ( NOLOCK )
LEFT JOIN ( SELECT DISTINCT
check_number AS check_num
FROM payment_history
WHERE transaction_type = 'VD'
AND ISNUMERIC(check_number) = 1
AND transaction_date > DATEADD(m,
-24, GETDATE())
) v ON ph.check_number = v.check_num
LEFT JOIN creditor AS cr ( NOLOCK ) ON ph.creditor_id = cr.creditor_id
LEFT JOIN debt_info AS di ( NOLOCK ) ON di.debt_info_id = ph.debt_info_id
LEFT JOIN client AS c ON c.client_id = di.client_id
WHERE COALESCE(ph.transaction_type, '') NOT IN (
'CR', 'IN', 'VD' )
AND ph.paid_date IS NULL
AND COALESCE(ph.amount, 0) > 0
AND COALESCE(ph.batch_id, '') <> 'REISSUE'
AND ISNUMERIC(ph.check_number) = 1
AND v.check_num IS NULL
GROUP BY c.client_id
) AS ph ON ph.client_id = c.client_id
LEFT JOIN ( SELECT crh.client_id AS client_id ,
SUM(crh.amount) AS uncleared_amt
FROM client_refund_history AS crh ( NOLOCK )
WHERE crh.post_date IS NULL
GROUP BY crh.client_id
) AS chk ON chk.client_id = c.client_id
WHERE ( -- one of the amounts is not zero INC108968
ABS(COALESCE(c.client_balance, 0)) >= 0.01
OR ABS(COALESCE(ph.check_amt, 0)) >= 0.01
OR ABS(COALESCE(chk.uncleared_amt, 0)) >= 0.01
)
AND c.client_id > 0
AND c.client_id NOT IN ( '881111', '880000', '999999' )
ORDER BY c.client_id;
END
Copy this with headings into a blank excel
At the end put in Column A ‘Subtotal’ and for column B to F do an auto sum of all figures above in that column
Then run the below
BEGIN
SELECT c.client_id AS ClientID ,
COALESCE(CONVERT(DECIMAL(15, 2), c.client_balance), 0) AS ClientBalance ,
CASE WHEN COALESCE(CONVERT(DECIMAL(15, 2), c.client_balance),
0) > 0
THEN COALESCE(CONVERT(DECIMAL(15, 2), c.client_balance),
0)
ELSE 0
END AS PositiveBalance ,
CASE WHEN COALESCE(CONVERT(DECIMAL(15, 2), c.client_balance),
0) < 0
THEN COALESCE(CONVERT(DECIMAL(15, 2), c.client_balance),
0)
ELSE 0
END AS NegativeBalance ,
COALESCE(chk.uncleared_amt, 0) + COALESCE(ph.check_amt,
0) AS UnclearedCheque ,
CASE WHEN ( COALESCE(CONVERT(DECIMAL(15, 2), c.client_balance),
0) )
+ ( COALESCE(chk.uncleared_amt, 0)
+ COALESCE(ph.check_amt, 0) ) < 0 THEN 0
ELSE ( COALESCE(CONVERT(DECIMAL(15, 2), c.client_balance),
0) )
+ ( COALESCE(chk.uncleared_amt, 0)
+ COALESCE(ph.check_amt, 0) )
END AS TotalByClient
FROM client AS c
LEFT JOIN ( SELECT c.client_id AS client_id ,
SUM(CASE WHEN ph.bdn_code = 'D'
THEN CONVERT(DECIMAL(15,
2), ph.amount)
- CONVERT(DECIMAL(15,
2), ( CONVERT(DECIMAL(15,
2), ph.amount)
* CONVERT(MONEY, ph.transaction_percent) ))
ELSE CONVERT(DECIMAL(15,
2), ph.amount)
END) AS check_amt
FROM payment_history AS ph ( NOLOCK )
LEFT JOIN ( SELECT DISTINCT
check_number AS check_num
FROM payment_history
WHERE transaction_type = 'VD'
AND ISNUMERIC(check_number) = 1
AND transaction_date > DATEADD(m,
-24, GETDATE())
) v ON ph.check_number = v.check_num
LEFT JOIN creditor AS cr ( NOLOCK ) ON ph.creditor_id = cr.creditor_id
LEFT JOIN debt_info AS di ( NOLOCK ) ON di.debt_info_id = ph.debt_info_id
LEFT JOIN client AS c ON c.client_id = di.client_id
WHERE COALESCE(ph.transaction_type, '') NOT IN (
'CR', 'IN', 'VD' )
AND ph.paid_date IS NULL
AND COALESCE(ph.amount, 0) > 0
AND COALESCE(ph.batch_id, '') <> 'REISSUE'
AND ISNUMERIC(ph.check_number) = 1
AND v.check_num IS NULL
GROUP BY c.client_id
) AS ph ON ph.client_id = c.client_id
LEFT JOIN ( SELECT crh.client_id AS client_id ,
SUM(crh.amount) AS uncleared_amt
FROM client_refund_history AS crh ( NOLOCK )
WHERE crh.post_date IS NULL
GROUP BY crh.client_id
) AS chk ON chk.client_id = c.client_id
WHERE ( -- one of the amounts is not zero
ABS(COALESCE(c.client_balance, 0)) >= 0.01
OR ABS(COALESCE(ph.check_amt, 0)) >= 0.01
OR ABS(COALESCE(chk.uncleared_amt, 0)) >= 0.01
)
AND c.client_id > 0
AND c.client_id IN ( '881111', '880000', '999999' )
ORDER BY c.client_id;
END;
Copy this and put without headings onto the end of the spreadsheet
At the end put in Column A ‘Total’ and for column B to F do an auto sum of all figures you have just pasted and the subtotal from the previous step.
The result should look like this with all the client references above.
To Tidy the document up you will copy and paste values so there are no formulas left in.
Also highlight column B to F and set this to Accounting so the figures move to 2 DP.
Save the Excel file in the below folder
\\ldsfileproapp01\Client Money\Client Money Reconciliation Team\01. Client Money Calculations\Internal Reconciliation Reports\Client Money Int Rec Report