SQL in Neteeza, trying to eliminate duplicates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL in Neteeza, trying to eliminate duplicates

Author

Topic

ktomb
Starting Member

2 Posts

Posted - 2011-11-30 : 22:06:56

My goal is to generate totals and I don't want to include duplicates. I'm still getting some dups, and I've boiled it down to one field (GL acct nbr). If the service is in one GL which is higher on the hierarchy, then don't count the service in additional GL's associated with the same account number. This may not be the part of the code that I need to change? I'm new to Neteeza and not used to SQL this complex. Thanks for any help.

,ranked_dedup as
(
select bm.site_id
,bm.acct_nbr
--,bm.v_gratis_flg
--,bm.v_subscriber_type

,case
when rnk.gl_acct is null then 'N' /* don't dedup metrics that aren't ranked */
--when row_number() over(partition by bm.site_id,bm.acct_nbr,rnk.rank_group order by rnk.seq_no) > 1 then 'Y'
when dense_rank() over(partition by bm.site_id,bm.acct_nbr,rnk.rank_group order by rnk.seq_no) > 1 then 'Y'
else 'N'
end v_RANKED_DEDUP_FLG

,case v_ranked_dedup_flg
when 'Y'
then case when bm.base_metric_rule = '' then '' else bm.base_metric_rule||'|' end ||'Metric-Ranked Dedup'
else bm.base_metric_rule
end base_metric_rule

,case v_ranked_dedup_flg
when 'Y'
then case when bm.base_metric_rule_cde = '' then '' else bm.base_metric_rule_cde||'|' end ||'MRD'
else bm.base_metric_rule_cde
end base_metric_rule_cde

,case v_ranked_dedup_flg
when 'Y'
then case when bm.base_metric_rule_comments = '' then '' else bm.base_metric_rule_comments||'|' end ||'Metric-Ranked Dedup'
else bm.base_metric_rule_comments
end base_metric_rule_comments
,bm.bus_unit
,bm.oper_unit
,bm.cla
,bm.gl_acct
,bm.sum_cde
,bm.subscriber_type
,bm.src_amt

,case v_ranked_dedup_flg
when 'Y' then -bm.src_amt
else bm.adj_amt
end adj_amt

,case v_ranked_dedup_flg
when 'Y' then 0
else bm.tot_amt
end tot_amt
,bm.gratis_amt
,bm.sub_cnt

from base_metric bm
left join gl_rank rnk
on bm.gl_acct = rnk.gl_acct
)
,mtr as
(
select min(site_id) site_id_min
,max(site_id) site_id_max
,bus_unit
,oper_unit
--,cla
,gl_acct
,sum_cde
,subscriber_type
,sum(src_amt) src_amt
,sum(adj_amt) adj_amt
,sum(tot_amt) tot_amt
,sum(gratis_amt) gratis_amt
,sum(sub_cnt) sub_cnt
,max(base_metric_rule) base_metric_rule
from ranked_dedup
where gl_acct in ('90003','90004','90005','90007','90010','90020','96070','96104','96105','96106','96108','96110','96 113','96205','96206','96207','96305','96306','96309','96310','96311','96312','96505','96506','96508' ,'96816','96817','97203','97207','97700','98850','98855','98857','98858','98880','98881','98882','98 885','98886','98887','98888','98889')
-- and oper_unit = '1758'
group by bus_unit
,oper_unit
--,cla
,gl_acct
,sum_cde
,subscriber_type
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 00:06:00

no use posting query. first post some sample data and explain what you mean by duplicates. then give what output you're expecting after avoiding your duplicates

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources