CODE
DECLARE @accounting_date DATE = '02-28-2023' -- pick an accounting date
SELECT glt.coa_account_id AS account_id
, glt.activity_date AS activity_date
, SUM(glt.amount) AS glt_amount
INTO #glt_total
FROM gl_transactions glt
JOIN gl_distributions gld
ON gld.id = glt.line_id
JOIN accounting_periods acp
ON acp.id = glt.accounting_period_id
JOIN chart_of_accounts coa
ON coa.id = glt.coa_account_id
WHERE acp.period_date = @accounting_date
AND ISNULL(coa.jib_bill_code_id, 0) <> 0
AND ISNULL(gld.jib_suppress, 0) = 0
GROUP BY glt.coa_account_id
, glt.activity_date
SELECT dcr.coa_account_id AS account_id
, dcr.activity_date AS activity_date
, sum(dcr.amount) AS jib_amount
INTO #jib_total
FROM direct_cost_results dcr
WHERE dcr.moment_id = (
SELECT id
FROM moments
WHERE company_code_id = dcr.company_code_id
AND accounting_date = @accounting_date
AND asset_id IS NULL
AND timeline_id IS NULL
)
GROUP BY dcr.coa_account_id
, dcr.activity_date
SELECT juc.coa_account_id AS account_id
, juc.activity_date AS activity_date
, sum(juc.functional_amount) AS unbilled_amount
INTO #gl_unbilled
FROM jib_gl_unbilled_charges juc
WHERE juc.accounting_date = @accounting_date
GROUP BY juc.coa_account_id
, juc.activity_date
SELECT juc.coa_account_id AS account_id
, juc.activity_date AS activity_date
, sum(juc.amount) AS unbilled_amount
INTO #jib_unbilled
FROM jib_unbilled_charges juc
WHERE juc.accounting_date = @accounting_date
GROUP BY juc.coa_account_id
, juc.activity_date
SELECT coa.account_code AS account_code
, glt.activity_date AS activity_date
, glt.glt_amount AS glt_amount
, isnull(jib.jib_amount, 0) AS dcr_amount
, isnull(unb.unbilled_amount, 0) AS gl_unbilled_amount
, isnull(jub.unbilled_amount, 0) AS jib_unbilled_amount
, glt.glt_amount - isnull(jib.jib_amount, 0) - isnull(unb.unbilled_amount, 0) - isnull(jub.unbilled_amount, 0) AS DIFFERENCE
FROM #glt_total glt
LEFT JOIN #jib_total jib
ON jib.account_id = glt.account_id
AND jib.activity_date = glt.activity_date
LEFT JOIN #gl_unbilled unb
ON unb.account_id = glt.account_id
AND unb.activity_date = glt.activity_date
LEFT JOIN #jib_unbilled jub
ON jub.account_id = glt.account_id
AND jub.activity_date = glt.activity_date
JOIN chart_of_accounts coa
ON coa.id = glt.account_id
WHERE glt.glt_amount - isnull(jib.jib_amount, 0) - isnull(unb.unbilled_amount, 0) - isnull(jub.unbilled_amount, 0) <> 0 -- only show difference
DROP TABLE #glt_total
DROP TABLE #jib_total
DROP TABLE #gl_unbilled
DROP TABLE #jib_unbilled