duplicate values after joining tables

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

duplicate values after joining tables

Author

Topic

anchoredwisdom
Starting Member

22 Posts

Posted - 2011-11-07 : 07:27:58

Hello

We have two tables where we have order amount and con amount.
In each row we have region,subregion,product and these two amounts.
We are trying to create one sql where we get both amounts and corresponding regions,products.
The idea is to retain either side amounts.

I wrote the below sql but i am getting duplicate.

The logic is this i created one sql where i bring sum of orderamount by region,product,fiscalweek etc. SQL 1

another sql for bringing sum of conamount by region,product,fiscalweek. SQL 2

I right joined the first sql with datemas and retain all fiscalweeks and then the resultant sql joined with the product table finally i did a full outer join with the sql which brings order amount

I am getting duplicate values.

Please share your thoughts and let me know if something is wrong in my approach

select
* from (

select distinct
s1.region_name,
s1.sub_region_name,
s3.tier1,
s3.tier2,
s3.tier3,
s3.tier4,
s1.orderamount,
s2.fiscal_week_name,
s4.camount

from

(select
region_name,sub_region_name,Trunc(sum(net_amount)) as orderamount,fiscal_week_name,tier4
from cx_sfdc_orders_fact orders
group by region_name,sub_region_name,fiscal_week_name,tier4
) S1

RIGHT JOIN
(select distinct fiscal_week_name,fiscal_month_name,fiscal_quarter_name
from date_mas ) S2
ON (S1.fiscal_week_name = s2.fiscal_weeK_name)

LEFT JOIN

(SELECT DISTINCT tier1,tier2,tier3,PNL tier4 from CX_PROD_HIER_XREF) s3
on s1.tier4 = s3.tier4

FULL OUTER JOIN

(select region_name,sub_region_name,pcng_ctgry_nm,fiscal_week,
sum(oprtnty_ttl_pr_cnvrtd_val) camount,tier_4_DE
from cx_sfdc_opportunity_det
where PCNG_CTGRY_NM in ('BestCase','Closed')
group by region_name,sub_region_name,pcng_ctgry_nm,fiscal_week,tier_4_dE ) s4
on
(S3.TIER4 = s4.tier_4_DE)
and
s4.fiscal_week=s2.fiscal_week_name
and
s4.region_name =s1.region_name
and
s4.sub_region_name =s1.sub_region_name
)

where camount is null

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 09:21:48

can you show some sample data and then show how you need to get output out of them

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

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-11-07 : 09:38:39

Please post your sample data and expected data with table structure. This will help us

Table data
col1 col2 col3
1 a x
2 c a
3 d a

Expected data
col1 col2 x a
1 a 1 0
2 c 0 1
3 d 0 1

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources