select	a13.producttag  attrvalue,
	max(a13.branddesc)  Branddesc_01,
	a14.producttag  producttag,
	max(a14.branddesc)  branddesc,
	a13.countrytag  countrytag,
	max(a19.countryname)  countryname,
	a13.clienttag  clienttag,
	a13.countrytag  countrytag0,
	max(a18.clientname)  clientname,
	a13.categorytag  categorytag,
	a13.clienttag  clienttag0,
	a13.countrytag  countrytag1,
	max(a17.categoryname)  categoryname,
	coalesce(pa11.periodtag, pa12.periodtag)  periodtag,
	coalesce(pa11.categorytag1, pa12.categorytag1)  categorytag0,
	coalesce(pa11.clienttag1, pa12.clienttag1)  clienttag1,
	coalesce(pa11.countrytag1, pa12.countrytag1)  countrytag2,
	max(a16.seqno)  seqno,
	max(a16.othrdesc)  othrdesc,
	max(Case a16.othrdesc when  'Latest Month'  then 1 
                                      when  'Prior Month' then 2
                                      when  '3 Months Ago' then 3
                                      when  '6 Months Ago' then 4
                                      when  '12 Months Ago' then 5
                                      else 6 end)  CustCol_11,
	coalesce(pa11.mbdtag, pa12.mbdtag)  mbdtag,
	coalesce(pa11.categorytag2, pa12.categorytag2)  categorytag1,
	coalesce(pa11.clienttag2, pa12.clienttag2)  clienttag2,
	coalesce(pa11.countrytag2, pa12.countrytag2)  countrytag3,
	coalesce(pa11.producttag, pa12.producttag)  producttag0,
	coalesce(pa11.categorytag, pa12.categorytag)  categorytag2,
	coalesce(pa11.clienttag, pa12.clienttag)  clienttag3,
	coalesce(pa11.countrytag, pa12.countrytag)  countrytag4,
	a14.producttag  product_short_desc,
	max(a14.branddesc)  branddesc0,
	coalesce(pa11.producttag0, pa12.producttag0)  producttag1,
	coalesce(pa11.categorytag0, pa12.categorytag0)  categorytag3,
	coalesce(pa11.clienttag0, pa12.clienttag0)  clienttag4,
	coalesce(pa11.countrytag0, pa12.countrytag0)  countrytag5,
	a15.mbdtag  mbdtag0,
	max(a15.marketdesc)  marketdesc,
	max(a15.otherdesc)  otherdesc,
	max(pa11.WJXBFS1)  WJXBFS1,
	max(pa11.WJXBFS2)  WJXBFS2,
	max(pa11.WJXBFS3)  WJXBFS3,
	max(pa11.WJXBFS4)  WJXBFS4,
	max(pa11.WJXBFS5)  WJXBFS5,
	max(pa11.WJXBFS6)  WJXBFS6,
	max(pa11.WJXBFS7)  WJXBFS7,
	max(pa11.WJXBFS8)  WJXBFS8,
	max(pa11.WJXBFS9)  WJXBFS9,
	max(pa11.WJXBFSa)  WJXBFSa,
	max(pa11.WJXBFSb)  WJXBFSb,
	max(pa11.WJXBFSc)  WJXBFSc,
	max(pa11.WJXBFSd)  WJXBFSd,
	max(pa11.WJXBFSe)  WJXBFSe,
	max(pa11.WJXBFSf)  WJXBFSf,
	max(pa11.WJXBFS10)  WJXBFS10,
	max(pa11.WJXBFS11)  WJXBFS11,
	max(pa11.WJXBFS12)  WJXBFS12,
	max(pa11.WJXBFS13)  WJXBFS13,
	max(pa12.WJXBFS1)  WJXBFS14,
	max(pa12.WJXBFS2)  WJXBFS15,
	max(pa12.WJXBFS3)  WJXBFS16,
	max(pa12.WJXBFS4)  WJXBFS17,
	max(pa12.WJXBFS5)  WJXBFS18,
	max(pa12.WJXBFS6)  WJXBFS19,
	max(pa12.WJXBFS7)  WJXBFS1a,
	max(pa12.WJXBFS8)  WJXBFS1b,
	max(pa12.WJXBFS9)  WJXBFS1c,
	max(pa12.WJXBFSa)  WJXBFS1d,
	max(pa12.WJXBFSb)  WJXBFS1e,
	max(pa12.WJXBFSc)  WJXBFS1f,
	max(pa12.WJXBFSd)  WJXBFS20,
	max(pa12.WJXBFSe)  WJXBFS21,
	max(pa12.WJXBFSf)  WJXBFS22,
	max(pa12.WJXBFS10)  WJXBFS23,
	max(pa12.WJXBFS11)  WJXBFS24,
	max(pa12.WJXBFS12)  WJXBFS25
from	(select	a11.producttag  producttag,
		a11.categorytag  categorytag,
		a11.clienttag  clienttag,
		a11.countrytag  countrytag,
		a11.competitorproducttag  producttag0,
		a11.categorytag  categorytag0,
		a11.clienttag  clienttag0,
		a11.countrytag  countrytag0,
		a11.periodtag  periodtag,
		a11.categorytag  categorytag1,
		a11.clienttag  clienttag1,
		a11.countrytag  countrytag1,
		a11.mbdtag  mbdtag,
		a11.categorytag  categorytag2,
		a11.clienttag  clienttag2,
		a11.countrytag  countrytag2,
		sum(a11.clientstorecount)  WJXBFS1,
		sum(a11.clientwd)  WJXBFS2,
		sum(a11.competitorstorecount)  WJXBFS3,
		sum(a11.competitorwd)  WJXBFS4,
		sum(a11.bothstorecount)  WJXBFS5,
		sum(a11.bothwd)  WJXBFS6,
		sum(a11.clientvalueshare)  WJXBFS7,
		sum(a11.bothclientvalueshare)  WJXBFS8,
		sum(a11.bothcompetitorvalueshare)  WJXBFS9,
		sum(a11.competitorvalueshare)  WJXBFSa,
		sum(a11.totalstoreclientvalueshare)  WJXBFSb,
		sum(a11.totalstorecompetitorvalueshare)  WJXBFSc,
		sum(a11.clientvolumeshare)  WJXBFSd,
		sum(a11.bothclientvolumeshare)  WJXBFSe,
		sum(a11.bothcompetitorvolumeshare)  WJXBFSf,
		sum(a11.competitorvolumeshare)  WJXBFS10,
		sum(a11.totalstoreclientvolumeshare)  WJXBFS11,
		sum(a11.totalstorecompetitorvolumeshare)  WJXBFS12,
		sum(100)  WJXBFS13
	from	retail_intel_viz_rnd.fact_5d_distribution_overlap	a11
		join	(select	pa11.producttag  producttag,
			pa11.categorytag  categorytag,
			pa11.clienttag0  clienttag,
			pa11.countrytag1  countrytag,
			pa11.clienttag  clienttag0,
			pa11.countrytag0  countrytag0,
			pa11.countrytag  countrytag1,
			pa11.mbdtag0  mbdtag,
			pa11.mbdtag  mbdtag0,
			pa11.categorytag0  categorytag0,
			pa11.clienttag1  clienttag1,
			pa11.countrytag2  countrytag2,
			pa11.producttag0  producttag0,
			pa11.categorytag1  categorytag1,
			pa11.clienttag2  clienttag2,
			pa11.countrytag3  countrytag3,
			pa11.WJXBFS1  WJXBFS1,
			pa11.WJXBFS2  WJXBFS2
		from	(select	a15.producttag  producttag,
				a12.countrytag  countrytag,
				a12.clienttag  clienttag,
				a12.countrytag  countrytag0,
				a12.categorytag  categorytag,
				a12.clienttag  clienttag0,
				a12.countrytag  countrytag1,
				pa11.mbdtag  mbdtag,
				pa11.categorytag0  categorytag0,
				pa11.clienttag0  clienttag1,
				pa11.countrytag0  countrytag2,
				pa11.producttag  producttag0,
				pa11.categorytag  categorytag1,
				pa11.clienttag  clienttag2,
				pa11.countrytag  countrytag3,
				a12.mbdtag  mbdtag0,
				pa11.WJXBFS1  WJXBFS1,
				rank() over(partition by pa13.WJXBFS1,pa13.WJXBFS2,pa13.WJXBFS3,pa14.WJXBFS1 order by pa11.WJXBFS1 desc)   WJXBFS2
			from	(select	a11.producttag  producttag,
					a11.categorytag  categorytag,
					a11.clienttag  clienttag,
					a11.countrytag  countrytag,
					a11.mbdtag  mbdtag,
					a11.categorytag  categorytag0,
					a11.clienttag  clienttag0,
					a11.countrytag  countrytag0,
					sum(a11.salesvalue)  WJXBFS1
				from	retail_intel_viz_rnd.fact_4d_distribution_data	a11
					join	retail_intel_viz_rnd.product	a12
					  on 	(a11.categorytag = a12.categorytag and 
					a11.clienttag = a12.clienttag and 
					a11.countrytag = a12.countrytag and 
					a11.producttag = a12.producttag)
					join	retail_intel_viz_rnd.market	a13
					  on 	(a11.categorytag = a13.categorytag and 
					a11.clienttag = a13.clienttag and 
					a11.countrytag = a13.countrytag and 
					a11.mbdtag = a13.mbdtag)
					join	retail_intel_viz_rnd.period	a14
					  on 	(a11.categorytag = a14.categorytag and 
					a11.clienttag = a14.clienttag and 
					a11.countrytag = a14.countrytag and 
					a11.periodtag = a14.periodtag)
				where	(a11.countrytag in ('KR')
				 and (a11.clienttag = '0006' and a11.countrytag = 'KR')
				 and (a11.categorytag = 'HNB' and a11.clienttag = '0006' and a11.countrytag = 'KR')
				 and a13.mbdtypetag in ('AUDIT')
				 and a12.clientflagtag in ('0')
				 and a12.leveltag in ('3.BRAND')
				 and a12.hierarchytag in ('1')
				 and a14.othrdesc in ('Latest Month')
				 and a13.mbdtag in ('STKO', 'STHY', 'STCL', 'STNA', 'STIL', 'STCV', 'STIS', 'STRA', 'TKWF', 'TSWP'))
				group by	a11.producttag,
					a11.categorytag,
					a11.clienttag,
					a11.countrytag,
					a11.mbdtag,
					a11.categorytag,
					a11.clienttag,
					a11.countrytag
				)	pa11
				join	retail_intel_viz_rnd.market	a12
				  on 	(pa11.categorytag0 = a12.categorytag and 
				pa11.clienttag0 = a12.clienttag and 
				pa11.countrytag0 = a12.countrytag and 
				pa11.mbdtag = a12.mbdtag)
				left outer join	(select	a12.countrytag  countrytag,
					a12.clienttag  clienttag,
					a12.countrytag  countrytag0,
					a12.categorytag  categorytag,
					a12.clienttag  clienttag0,
					a12.countrytag  countrytag1,
					max(a12.countrytag)  WJXBFS1,
					max(a12.clienttag)  WJXBFS2,
					max(a12.categorytag)  WJXBFS3
				from	retail_intel_viz_rnd.product	a12
					join	(select	s22.categorytag  categorytag,
						s22.clienttag  clienttag,
						s22.countrytag  countrytag,
						s22.clienttag  clienttag0,
						s22.countrytag  countrytag0,
						s22.clientflagtag  attrvalue,
						s22.countrytag  countrytag1,
						s22.hierarchytag  hierarchytag,
						s22.leveltag  attrvalue0,
						s23.mbdtag  mbdtag
					from	retail_intel_viz_rnd.product	s22
						join	retail_intel_viz_rnd.market	s23
						  on 	(s22.categorytag = s23.categorytag and 
						s22.clienttag = s23.clienttag and 
						s22.countrytag = s23.countrytag)
						join	retail_intel_viz_rnd.period	s24
						  on 	(s22.categorytag = s24.categorytag and 
						s22.clienttag = s24.clienttag and 
						s22.countrytag = s24.countrytag)
					where	(s22.countrytag in ('KR')
					 and (s22.clienttag = '0006' and s22.countrytag = 'KR')
					 and (s22.categorytag = 'HNB' and s22.clienttag = '0006' and s22.countrytag = 'KR')
					 and s23.mbdtypetag in ('AUDIT')
					 and s22.clientflagtag in ('0')
					 and s22.leveltag in ('3.BRAND')
					 and s22.hierarchytag in ('1')
					 and s24.othrdesc in ('Latest Month')
					 and s23.mbdtag in ('STKO', 'STHY', 'STCL', 'STNA', 'STIL', 'STCV', 'STIS', 'STRA', 'TKWF', 'TSWP'))
					group by	s22.categorytag,
						s22.clienttag,
						s22.countrytag,
						s22.clienttag,
						s22.countrytag,
						s22.clientflagtag,
						s22.countrytag,
						s22.hierarchytag,
						s22.leveltag,
						s23.mbdtag
					)	pa13
					  on 	(a12.categorytag = pa13.categorytag and 
					a12.clientflagtag = pa13.attrvalue and 
					a12.clienttag = pa13.clienttag and 
					a12.clienttag = pa13.clienttag0 and 
					a12.countrytag = pa13.countrytag and 
					a12.countrytag = pa13.countrytag0 and 
					a12.countrytag = pa13.countrytag1 and 
					a12.hierarchytag = pa13.hierarchytag and 
					a12.leveltag = pa13.attrvalue0)
				group by	a12.countrytag,
					a12.clienttag,
					a12.countrytag,
					a12.categorytag,
					a12.clienttag,
					a12.countrytag
				)	pa13
				  on 	(a12.categorytag = pa13.categorytag and 
				a12.clienttag = pa13.clienttag and 
				a12.clienttag = pa13.clienttag0 and 
				a12.countrytag = pa13.countrytag and 
				a12.countrytag = pa13.countrytag0 and 
				a12.countrytag = pa13.countrytag1)
				left outer join	(select	a11.mbdtag  mbdtag,
					a11.categorytag  categorytag,
					a11.clienttag  clienttag,
					a11.countrytag  countrytag,
					a11.mbdtag  WJXBFS1
				from	retail_intel_viz_rnd.market	a11
				where	(a11.countrytag in ('KR')
				 and (a11.clienttag = '0006' and a11.countrytag = 'KR')
				 and (a11.categorytag = 'HNB' and a11.clienttag = '0006' and a11.countrytag = 'KR')
				 and a11.mbdtypetag in ('AUDIT')
				 and a11.mbdtag in ('STKO', 'STHY', 'STCL', 'STNA', 'STIL', 'STCV', 'STIS', 'STRA', 'TKWF', 'TSWP'))
				)	pa14
				  on 	(pa11.categorytag0 = pa14.categorytag and 
				pa11.clienttag0 = pa14.clienttag and 
				pa11.countrytag0 = pa14.countrytag and 
				pa11.mbdtag = pa14.mbdtag)
				join	retail_intel_viz_rnd.product	a15
				  on 	(pa11.categorytag = a15.categorytag and 
				pa11.clienttag = a15.clienttag and 
				pa11.countrytag = a15.countrytag and 
				pa11.producttag = a15.producttag)
			)	pa11
		where	(pa11.WJXBFS2 < 6.0)
		)	pa12
		  on 	(a11.categorytag = pa12.categorytag0 and 
		a11.categorytag = pa12.categorytag1 and 
		a11.clienttag = pa12.clienttag1 and 
		a11.clienttag = pa12.clienttag2 and 
		a11.competitorproducttag = pa12.producttag0 and 
		a11.countrytag = pa12.countrytag2 and 
		a11.countrytag = pa12.countrytag3 and 
		a11.mbdtag = pa12.mbdtag0)
		join	retail_intel_viz_rnd.market	a13
		  on 	(a11.categorytag = a13.categorytag and 
		a11.clienttag = a13.clienttag and 
		a11.countrytag = a13.countrytag and 
		a11.mbdtag = a13.mbdtag)
		join	retail_intel_viz_rnd.period	a14
		  on 	(a11.categorytag = a14.categorytag and 
		a11.clienttag = a14.clienttag and 
		a11.countrytag = a14.countrytag and 
		a11.periodtag = a14.periodtag)
	where	(a11.countrytag in ('KR')
	 and (a11.clienttag = '0006' and a11.countrytag = 'KR')
	 and (a11.categorytag = 'HNB' and a11.clienttag = '0006' and a11.countrytag = 'KR')
	 and a13.mbdtypetag in ('AUDIT')
	 and a14.othrdesc in ('Latest Month', 'Prior Month', '3 Months Ago', '6 Months Ago', '12 Months Ago')
	 and pa12.mbdtag in ('STKO', 'STHY', 'STCL', 'STNA', 'STIL', 'STCV', 'STIS', 'STRA', 'TKWF', 'TSWP')
	 and a11.producttag in ('0000006T3'))
	group by	a11.producttag,
		a11.categorytag,
		a11.clienttag,
		a11.countrytag,
		a11.competitorproducttag,
		a11.categorytag,
		a11.clienttag,
		a11.countrytag,
		a11.periodtag,
		a11.categorytag,
		a11.clienttag,
		a11.countrytag,
		a11.mbdtag,
		a11.categorytag,
		a11.clienttag,
		a11.countrytag
	)	pa11
	full outer join	(select	a11.producttag  producttag,
		a11.categorytag  categorytag,
		a11.clienttag  clienttag,
		a11.countrytag  countrytag,
		a11.competitorproducttag  producttag0,
		a11.categorytag  categorytag0,
		a11.clienttag  clienttag0,
		a11.countrytag  countrytag0,
		a12.periodtag  periodtag,
		a12.categorytag  categorytag1,
		a12.clienttag  clienttag1,
		a12.countrytag  countrytag1,
		a11.mbdtag  mbdtag,
		a11.categorytag  categorytag2,
		a11.clienttag  clienttag2,
		a11.countrytag  countrytag2,
		sum(a11.bothstorecount)  WJXBFS1,
		sum(a11.bothclientvalueshare)  WJXBFS2,
		sum(a11.competitorwd)  WJXBFS3,
		sum(a11.totalstorecompetitorvalueshare)  WJXBFS4,
		sum(a11.totalstoreclientvalueshare)  WJXBFS5,
		sum(a11.competitorvalueshare)  WJXBFS6,
		sum(a11.competitorstorecount)  WJXBFS7,
		sum(a11.competitorvolumeshare)  WJXBFS8,
		sum(a11.totalstorecompetitorvolumeshare)  WJXBFS9,
		sum(a11.clientstorecount)  WJXBFSa,
		sum(a11.totalstoreclientvolumeshare)  WJXBFSb,
		sum(a11.bothcompetitorvalueshare)  WJXBFSc,
		sum(a11.bothwd)  WJXBFSd,
		sum(a11.clientwd)  WJXBFSe,
		sum(a11.clientvalueshare)  WJXBFSf,
		sum(a11.bothclientvolumeshare)  WJXBFS10,
		sum(a11.clientvolumeshare)  WJXBFS11,
		sum(a11.bothcompetitorvolumeshare)  WJXBFS12
	from	retail_intel_viz_rnd.fact_5d_distribution_overlap	a11
		join	retail_intel_viz_rnd.transformation_single_month	a12
		  on 	(a11.categorytag = a12.categorytag and 
		a11.clienttag = a12.clienttag and 
		a11.countrytag = a12.countrytag and 
		a11.periodtag = a12.prior_periodtag)
		join	(select	pa11.producttag  producttag,
			pa11.categorytag  categorytag,
			pa11.clienttag0  clienttag,
			pa11.countrytag1  countrytag,
			pa11.clienttag  clienttag0,
			pa11.countrytag0  countrytag0,
			pa11.countrytag  countrytag1,
			pa11.mbdtag0  mbdtag,
			pa11.mbdtag  mbdtag0,
			pa11.categorytag0  categorytag0,
			pa11.clienttag1  clienttag1,
			pa11.countrytag2  countrytag2,
			pa11.producttag0  producttag0,
			pa11.categorytag1  categorytag1,
			pa11.clienttag2  clienttag2,
			pa11.countrytag3  countrytag3,
			pa11.WJXBFS1  WJXBFS1,
			pa11.WJXBFS2  WJXBFS2
		from	(select	a15.producttag  producttag,
				a12.countrytag  countrytag,
				a12.clienttag  clienttag,
				a12.countrytag  countrytag0,
				a12.categorytag  categorytag,
				a12.clienttag  clienttag0,
				a12.countrytag  countrytag1,
				pa11.mbdtag  mbdtag,
				pa11.categorytag0  categorytag0,
				pa11.clienttag0  clienttag1,
				pa11.countrytag0  countrytag2,
				pa11.producttag  producttag0,
				pa11.categorytag  categorytag1,
				pa11.clienttag  clienttag2,
				pa11.countrytag  countrytag3,
				a12.mbdtag  mbdtag0,
				pa11.WJXBFS1  WJXBFS1,
				rank() over(partition by pa13.WJXBFS1,pa13.WJXBFS2,pa13.WJXBFS3,pa14.WJXBFS1 order by pa11.WJXBFS1 desc)   WJXBFS2
			from	(select	a11.producttag  producttag,
					a11.categorytag  categorytag,
					a11.clienttag  clienttag,
					a11.countrytag  countrytag,
					a11.mbdtag  mbdtag,
					a11.categorytag  categorytag0,
					a11.clienttag  clienttag0,
					a11.countrytag  countrytag0,
					sum(a11.salesvalue)  WJXBFS1
				from	retail_intel_viz_rnd.fact_4d_distribution_data	a11
					join	retail_intel_viz_rnd.product	a12
					  on 	(a11.categorytag = a12.categorytag and 
					a11.clienttag = a12.clienttag and 
					a11.countrytag = a12.countrytag and 
					a11.producttag = a12.producttag)
					join	retail_intel_viz_rnd.market	a13
					  on 	(a11.categorytag = a13.categorytag and 
					a11.clienttag = a13.clienttag and 
					a11.countrytag = a13.countrytag and 
					a11.mbdtag = a13.mbdtag)
					join	retail_intel_viz_rnd.period	a14
					  on 	(a11.categorytag = a14.categorytag and 
					a11.clienttag = a14.clienttag and 
					a11.countrytag = a14.countrytag and 
					a11.periodtag = a14.periodtag)
				where	(a11.countrytag in ('KR')
				 and (a11.clienttag = '0006' and a11.countrytag = 'KR')
				 and (a11.categorytag = 'HNB' and a11.clienttag = '0006' and a11.countrytag = 'KR')
				 and a13.mbdtypetag in ('AUDIT')
				 and a12.clientflagtag in ('0')
				 and a12.leveltag in ('3.BRAND')
				 and a12.hierarchytag in ('1')
				 and a14.othrdesc in ('Latest Month')
				 and a13.mbdtag in ('STKO', 'STHY', 'STCL', 'STNA', 'STIL', 'STCV', 'STIS', 'STRA', 'TKWF', 'TSWP'))
				group by	a11.producttag,
					a11.categorytag,
					a11.clienttag,
					a11.countrytag,
					a11.mbdtag,
					a11.categorytag,
					a11.clienttag,
					a11.countrytag
				)	pa11
				join	retail_intel_viz_rnd.market	a12
				  on 	(pa11.categorytag0 = a12.categorytag and 
				pa11.clienttag0 = a12.clienttag and 
				pa11.countrytag0 = a12.countrytag and 
				pa11.mbdtag = a12.mbdtag)
				left outer join	(select	a12.countrytag  countrytag,
					a12.clienttag  clienttag,
					a12.countrytag  countrytag0,
					a12.categorytag  categorytag,
					a12.clienttag  clienttag0,
					a12.countrytag  countrytag1,
					max(a12.countrytag)  WJXBFS1,
					max(a12.clienttag)  WJXBFS2,
					max(a12.categorytag)  WJXBFS3
				from	retail_intel_viz_rnd.product	a12
					join	(select	s22.categorytag  categorytag,
						s22.clienttag  clienttag,
						s22.countrytag  countrytag,
						s22.clienttag  clienttag0,
						s22.countrytag  countrytag0,
						s22.clientflagtag  attrvalue,
						s22.countrytag  countrytag1,
						s22.hierarchytag  hierarchytag,
						s22.leveltag  attrvalue0,
						s23.mbdtag  mbdtag
					from	retail_intel_viz_rnd.product	s22
						join	retail_intel_viz_rnd.market	s23
						  on 	(s22.categorytag = s23.categorytag and 
						s22.clienttag = s23.clienttag and 
						s22.countrytag = s23.countrytag)
						join	retail_intel_viz_rnd.period	s24
						  on 	(s22.categorytag = s24.categorytag and 
						s22.clienttag = s24.clienttag and 
						s22.countrytag = s24.countrytag)
					where	(s22.countrytag in ('KR')
					 and (s22.clienttag = '0006' and s22.countrytag = 'KR')
					 and (s22.categorytag = 'HNB' and s22.clienttag = '0006' and s22.countrytag = 'KR')
					 and s23.mbdtypetag in ('AUDIT')
					 and s22.clientflagtag in ('0')
					 and s22.leveltag in ('3.BRAND')
					 and s22.hierarchytag in ('1')
					 and s24.othrdesc in ('Latest Month')
					 and s23.mbdtag in ('STKO', 'STHY', 'STCL', 'STNA', 'STIL', 'STCV', 'STIS', 'STRA', 'TKWF', 'TSWP'))
					group by	s22.categorytag,
						s22.clienttag,
						s22.countrytag,
						s22.clienttag,
						s22.countrytag,
						s22.clientflagtag,
						s22.countrytag,
						s22.hierarchytag,
						s22.leveltag,
						s23.mbdtag
					)	pa13
					  on 	(a12.categorytag = pa13.categorytag and 
					a12.clientflagtag = pa13.attrvalue and 
					a12.clienttag = pa13.clienttag and 
					a12.clienttag = pa13.clienttag0 and 
					a12.countrytag = pa13.countrytag and 
					a12.countrytag = pa13.countrytag0 and 
					a12.countrytag = pa13.countrytag1 and 
					a12.hierarchytag = pa13.hierarchytag and 
					a12.leveltag = pa13.attrvalue0)
				group by	a12.countrytag,
					a12.clienttag,
					a12.countrytag,
					a12.categorytag,
					a12.clienttag,
					a12.countrytag
				)	pa13
				  on 	(a12.categorytag = pa13.categorytag and 
				a12.clienttag = pa13.clienttag and 
				a12.clienttag = pa13.clienttag0 and 
				a12.countrytag = pa13.countrytag and 
				a12.countrytag = pa13.countrytag0 and 
				a12.countrytag = pa13.countrytag1)
				left outer join	(select	a11.mbdtag  mbdtag,
					a11.categorytag  categorytag,
					a11.clienttag  clienttag,
					a11.countrytag  countrytag,
					a11.mbdtag  WJXBFS1
				from	retail_intel_viz_rnd.market	a11
				where	(a11.countrytag in ('KR')
				 and (a11.clienttag = '0006' and a11.countrytag = 'KR')
				 and (a11.categorytag = 'HNB' and a11.clienttag = '0006' and a11.countrytag = 'KR')
				 and a11.mbdtypetag in ('AUDIT')
				 and a11.mbdtag in ('STKO', 'STHY', 'STCL', 'STNA', 'STIL', 'STCV', 'STIS', 'STRA', 'TKWF', 'TSWP'))
				)	pa14
				  on 	(pa11.categorytag0 = pa14.categorytag and 
				pa11.clienttag0 = pa14.clienttag and 
				pa11.countrytag0 = pa14.countrytag and 
				pa11.mbdtag = pa14.mbdtag)
				join	retail_intel_viz_rnd.product	a15
				  on 	(pa11.categorytag = a15.categorytag and 
				pa11.clienttag = a15.clienttag and 
				pa11.countrytag = a15.countrytag and 
				pa11.producttag = a15.producttag)
			)	pa11
		where	(pa11.WJXBFS2 < 6.0)
		)	pa13
		  on 	(a11.categorytag = pa13.categorytag0 and 
		a11.categorytag = pa13.categorytag1 and 
		a11.clienttag = pa13.clienttag1 and 
		a11.clienttag = pa13.clienttag2 and 
		a11.competitorproducttag = pa13.producttag0 and 
		a11.countrytag = pa13.countrytag2 and 
		a11.countrytag = pa13.countrytag3 and 
		a11.mbdtag = pa13.mbdtag0)
		join	retail_intel_viz_rnd.market	a14
		  on 	(a11.categorytag = a14.categorytag and 
		a11.clienttag = a14.clienttag and 
		a11.countrytag = a14.countrytag and 
		a11.mbdtag = a14.mbdtag)
		join	retail_intel_viz_rnd.period	a15
		  on 	(a12.categorytag = a15.categorytag and 
		a12.clienttag = a15.clienttag and 
		a12.countrytag = a15.countrytag and 
		a12.periodtag = a15.periodtag)
	where	(a12.countrytag in ('KR')
	 and (a12.clienttag = '0006' and a12.countrytag = 'KR')
	 and (a12.categorytag = 'HNB' and a12.clienttag = '0006' and a12.countrytag = 'KR')
	 and a14.mbdtypetag in ('AUDIT')
	 and a15.othrdesc in ('Latest Month', 'Prior Month', '3 Months Ago', '6 Months Ago', '12 Months Ago')
	 and pa13.mbdtag in ('STKO', 'STHY', 'STCL', 'STNA', 'STIL', 'STCV', 'STIS', 'STRA', 'TKWF', 'TSWP')
	 and a11.producttag in ('0000006T3'))
	group by	a11.producttag,
		a11.categorytag,
		a11.clienttag,
		a11.countrytag,
		a11.competitorproducttag,
		a11.categorytag,
		a11.clienttag,
		a11.countrytag,
		a12.periodtag,
		a12.categorytag,
		a12.clienttag,
		a12.countrytag,
		a11.mbdtag,
		a11.categorytag,
		a11.clienttag,
		a11.countrytag
	)	pa12
	  on 	(pa11.categorytag = pa12.categorytag and 
	pa11.categorytag0 = pa12.categorytag0 and 
	pa11.categorytag1 = pa12.categorytag1 and 
	pa11.categorytag2 = pa12.categorytag2 and 
	pa11.clienttag = pa12.clienttag and 
	pa11.clienttag0 = pa12.clienttag0 and 
	pa11.clienttag1 = pa12.clienttag1 and 
	pa11.clienttag2 = pa12.clienttag2 and 
	pa11.countrytag = pa12.countrytag and 
	pa11.countrytag0 = pa12.countrytag0 and 
	pa11.countrytag1 = pa12.countrytag1 and 
	pa11.countrytag2 = pa12.countrytag2 and 
	pa11.mbdtag = pa12.mbdtag and 
	pa11.periodtag = pa12.periodtag and 
	pa11.producttag = pa12.producttag and 
	pa11.producttag0 = pa12.producttag0)
	join	retail_intel_viz_rnd.product	a13
	  on 	(coalesce(pa11.categorytag, pa12.categorytag) = a13.categorytag and 
	coalesce(pa11.clienttag, pa12.clienttag) = a13.clienttag and 
	coalesce(pa11.countrytag, pa12.countrytag) = a13.countrytag and 
	coalesce(pa11.producttag, pa12.producttag) = a13.producttag)
	join	retail_intel_viz_rnd.product	a14
	  on 	(coalesce(pa11.categorytag0, pa12.categorytag0) = a14.categorytag and 
	coalesce(pa11.clienttag0, pa12.clienttag0) = a14.clienttag and 
	coalesce(pa11.countrytag0, pa12.countrytag0) = a14.countrytag and 
	coalesce(pa11.producttag0, pa12.producttag0) = a14.producttag)
	join	retail_intel_viz_rnd.market	a15
	  on 	(coalesce(pa11.categorytag2, pa12.categorytag2) = a15.categorytag and 
	coalesce(pa11.clienttag2, pa12.clienttag2) = a15.clienttag and 
	coalesce(pa11.countrytag2, pa12.countrytag2) = a15.countrytag and 
	coalesce(pa11.mbdtag, pa12.mbdtag) = a15.mbdtag)
	join	retail_intel_viz_rnd.period	a16
	  on 	(coalesce(pa11.categorytag1, pa12.categorytag1) = a16.categorytag and 
	coalesce(pa11.clienttag1, pa12.clienttag1) = a16.clienttag and 
	coalesce(pa11.countrytag1, pa12.countrytag1) = a16.countrytag and 
	coalesce(pa11.periodtag, pa12.periodtag) = a16.periodtag)
	join	retail_intel_viz_rnd.category	a17
	  on 	(a13.categorytag = a17.categorytag and 
	a13.clienttag = a17.clienttag and 
	a13.countrytag = a17.countrytag)
	join	retail_intel_viz_rnd.client	a18
	  on 	(a13.clienttag = a18.clienttag and 
	a13.countrytag = a18.countrytag)
	join	retail_intel_viz_rnd.country	a19
	  on 	(a13.countrytag = a19.countrytag)
group by	a13.producttag,
	a14.producttag,
	a13.countrytag,
	a13.clienttag,
	a13.countrytag,
	a13.categorytag,
	a13.clienttag,
	a13.countrytag,
	coalesce(pa11.periodtag, pa12.periodtag),
	coalesce(pa11.categorytag1, pa12.categorytag1),
	coalesce(pa11.clienttag1, pa12.clienttag1),
	coalesce(pa11.countrytag1, pa12.countrytag1),
	coalesce(pa11.mbdtag, pa12.mbdtag),
	coalesce(pa11.categorytag2, pa12.categorytag2),
	coalesce(pa11.clienttag2, pa12.clienttag2),
	coalesce(pa11.countrytag2, pa12.countrytag2),
	coalesce(pa11.producttag, pa12.producttag),
	coalesce(pa11.categorytag, pa12.categorytag),
	coalesce(pa11.clienttag, pa12.clienttag),
	coalesce(pa11.countrytag, pa12.countrytag),
	a14.producttag,
	coalesce(pa11.producttag0, pa12.producttag0),
	coalesce(pa11.categorytag0, pa12.categorytag0),
	coalesce(pa11.clienttag0, pa12.clienttag0),
	coalesce(pa11.countrytag0, pa12.countrytag0),
	a15.mbdtag