I've developed the following SQL. There are basically 2 tables. In the first part of this SQL I'm joining table 1 to table 2 and pulling all the records from table 1 and the matching records from table 1. I then do a UNION to the same thing but this time, I'm joining table 2 to table 1 and pulling all of the records from table 2 and only the matching records from table 1.
The UNION in each half of the query is so I can get both fixed asset accounts and the accumulated depreciation accounts in the same column.
I need to do this because there could be data in table 1 and not table 2 and I need to make sure I get all of there differences. As I expected, there are duplicate rows returned in the results. I have a couple of questions:
How do I get rid of the duplicate results?
I'm calculating the difference in each half of the query. Is there a better way or place to do this calculation?
Is a better way to do this SQL?
I appreciate your help with these questions?
SELECT
a.FA_ACCT,
a.ERACBR,
a.deptid,
a.FA_AMT,
pgal.pstd_ttl_amt,
CASE WHEN pgal.pstd_ttl_amt IS NULL THEN a.FA_AMT
ELSE a.FA_AMT - pgal.pstd_ttl_amt END AS DIFF
FROM
(SELECT
pdr.account_fa AS FA_ACCT,
ir.erac_branch_lgcy_cd AS ERACBR,
pdr.deptid,
SUM(pdr.COST) AS FA_Amt
FROM PSFS.PS_DEPR_RPT pdr
LEFT JOIN INTGRT_RPT.DIM_LOCATION ir ON pdr.deptid = ir.erac_branch_ps_org_cd AND ir.curr_lrd_row_flg = 1
WHERE pdr.BUSINESS_UNIT = 'A0465'
AND pdr.BOOK = 'PERFORM'
AND pdr.FISCAL_YEAR = 2015
AND pdr.ACCOUNTING_PERIOD = 8
AND pdr.GROUP_ASSET_FLAG <> 'M'
--AND ( ? is null or soh.REGION_CD = ?)
GROUP BY FA_ACCT, ERACBR, deptid
UNION All
SELECT
pdr.account_ad AS FA_ACCT,
ir.erac_branch_lgcy_cd AS ERACBR,
pdr.deptid,
SUM(pdr.depr_ltd) AS FA_Amt
FROM PSFS.PS_DEPR_RPT pdr
LEFT JOIN INTGRT_RPT.DIM_LOCATION ir ON pdr.deptid = ir.erac_branch_ps_org_cd AND ir.curr_lrd_row_flg = 1
WHERE pdr.BUSINESS_UNIT = 'A0465'
AND pdr.BOOK = 'PERFORM'
AND pdr.FISCAL_YEAR = 2015
AND pdr.ACCOUNTING_PERIOD = 8
AND pdr.GROUP_ASSET_FLAG <> 'M'
--AND ( ? is null or soh.REGION_CD = ?)
GROUP BY FA_ACCT, ERACBR, deptid ) a
LEFT JOIN PSFS.PS_GL_ACCT_LDGR PGAL ON a.deptid =pgal.grp_br_ps_org_id
AND a.fa_acct = pgal.acct_nbr
AND pgal.fiscal_yr_mth_nbr = 201508
GROUP BY 1,2,3,4,5,6
UNION ALL
SELECT
pgal.acct_nbr AS FA_ACCT,
ir.erac_branch_lgcy_cd AS ERACBR,
pgal.grp_br_ps_org_id,
b.FA_AMT,
pgal.pstd_ttl_amt,
CASE WHEN b.fa_amt IS NULL THEN pgal.pstd_ttl_amt
ELSE b.FA_AMT - pgal.pstd_ttl_amt END AS DIFF
FROM psfs.ps_gl_acct_ldgr pgal
LEFT JOIN INTGRT_RPT.DIM_LOCATION ir ON pgal.grp_br_ps_org_id = ir.erac_branch_ps_org_cd AND ir.curr_lrd_row_flg = 1
LEFT JOIN
(SELECT
pdr.account_fa AS FA_ACCT,
pdr.deptid,
SUM(pdr.COST) AS FA_Amt
FROM PSFS.PS_DEPR_RPT pdr
WHERE pdr.BUSINESS_UNIT = 'A0465'
AND pdr.BOOK = 'PERFORM'
AND pdr.FISCAL_YEAR = 2015
AND pdr.ACCOUNTING_PERIOD = 8
AND pdr.GROUP_ASSET_FLAG <> 'M'
--AND ( ? is null or soh.REGION_CD = ?)
GROUP BY FA_ACCT, deptid
UNION All
SELECT
pdr.account_ad AS FA_ACCT,
pdr.deptid,
SUM(pdr.depr_ltd) AS FA_Amt
FROM PSFS.PS_DEPR_RPT pdr
WHERE pdr.BUSINESS_UNIT = 'A0465'
AND pdr.BOOK = 'PERFORM'
AND pdr.FISCAL_YEAR = 2015
AND pdr.ACCOUNTING_PERIOD = 8
AND pdr.GROUP_ASSET_FLAG <> 'M'
--AND ( ? is null or soh.REGION_CD = ?)
GROUP BY FA_ACCT, deptid) b
ON pgal.grp_br_ps_org_id = b.deptid
AND pgal.acct_nbr = b.fa_acct
WHERE pgal.fiscal_yr_mth_nbr = 201508
AND pgal.acct_nbr BETWEEN 130500 AND 160500
AND ir.ody_group_cd = 'A0465'
ORDER BY 1, 2
