sql server 2008 - SQL - Removing duplicate rows -
i've developed following sql. there 2 tables. in first part of sql i'm joining table 1 table 2 , pulling records table 1 , matching records table 1. union same thing time, i'm joining table 2 table 1 , pulling of records table 2 , matching records table 1.
the union in each half of query can both fixed asset accounts , accumulated depreciation accounts in same column.
i need because there data in table 1 , not table 2 , need make sure of there differences. expected, there duplicate rows returned in results. have couple of questions:
how rid of duplicate results?
i'm calculating difference in each half of query. there better way or place calculation?
is better way sql?
i appreciate these questions?
select a.fa_acct, a.eracbr, a.deptid, a.fa_amt, pgal.pstd_ttl_amt, case when pgal.pstd_ttl_amt null a.fa_amt else a.fa_amt - pgal.pstd_ttl_amt end diff (select pdr.account_fa fa_acct, ir.erac_branch_lgcy_cd eracbr, pdr.deptid, sum(pdr.cost) fa_amt psfs.ps_depr_rpt pdr left join intgrt_rpt.dim_location ir on pdr.deptid = ir.erac_branch_ps_org_cd , ir.curr_lrd_row_flg = 1 pdr.business_unit = 'a0465' , pdr.book = 'perform' , pdr.fiscal_year = 2015 , pdr.accounting_period = 8 , pdr.group_asset_flag <> 'm' --and ( ? null or soh.region_cd = ?) group fa_acct, eracbr, deptid union select pdr.account_ad fa_acct, ir.erac_branch_lgcy_cd eracbr, pdr.deptid, sum(pdr.depr_ltd) fa_amt psfs.ps_depr_rpt pdr left join intgrt_rpt.dim_location ir on pdr.deptid = ir.erac_branch_ps_org_cd , ir.curr_lrd_row_flg = 1 pdr.business_unit = 'a0465' , pdr.book = 'perform' , pdr.fiscal_year = 2015 , pdr.accounting_period = 8 , pdr.group_asset_flag <> 'm' --and ( ? null or soh.region_cd = ?) group fa_acct, eracbr, deptid ) left join psfs.ps_gl_acct_ldgr pgal on a.deptid =pgal.grp_br_ps_org_id , a.fa_acct = pgal.acct_nbr , pgal.fiscal_yr_mth_nbr = 201508 group 1,2,3,4,5,6 union select pgal.acct_nbr fa_acct, ir.erac_branch_lgcy_cd eracbr, pgal.grp_br_ps_org_id, b.fa_amt, pgal.pstd_ttl_amt, case when b.fa_amt null pgal.pstd_ttl_amt else b.fa_amt - pgal.pstd_ttl_amt end diff 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 , ir.curr_lrd_row_flg = 1 left join (select pdr.account_fa fa_acct, pdr.deptid, sum(pdr.cost) fa_amt psfs.ps_depr_rpt pdr pdr.business_unit = 'a0465' , pdr.book = 'perform' , pdr.fiscal_year = 2015 , pdr.accounting_period = 8 , pdr.group_asset_flag <> 'm' --and ( ? null or soh.region_cd = ?) group fa_acct, deptid union select pdr.account_ad fa_acct, pdr.deptid, sum(pdr.depr_ltd) fa_amt psfs.ps_depr_rpt pdr pdr.business_unit = 'a0465' , pdr.book = 'perform' , pdr.fiscal_year = 2015 , pdr.accounting_period = 8 , pdr.group_asset_flag <> 'm' --and ( ? null or soh.region_cd = ?) group fa_acct, deptid) b on pgal.grp_br_ps_org_id = b.deptid , pgal.acct_nbr = b.fa_acct pgal.fiscal_yr_mth_nbr = 201508 , pgal.acct_nbr between 130500 , 160500 , ir.ody_group_cd = 'a0465' order 1, 2
Comments
Post a Comment