Mikewith78
Starting Member
1 Post |
Posted - 2012-05-09 : 06:52:16
|
I'm pretty new to SQL Server, and am having a bit of a nightmare joining multiple tables to one, the only was I have been able to do this is to name the table (with the multi joins name variations - mart.mat_calendar), is there an easier way around this?I have attached my code below (table joins in red), still getting back up to speed with SQL, so please don't rip it apart too much ;) Thanks :)selectAA.Spend_Channel as Spend_Channel,AA.PARTY as Party,AA.shopper_profile as CustType,AA.MW as MatWeek,AA.MY as MatYear,AA.trancluster as TranCluster,AA.WEEKSOPEN as WeeksOpen,AA.Open_Year as Open_Year,AA.Open_Week as Open_Week,SUM (AA.Spend)as Spend,SUM (AA.Transactions) as Transactions,SUM (AA.items) as Items,Count(distinct AA.NEWCUSTCOUNT) as OpenCustinWeekfrom (selectSpend_Channel = Case When b.account_type='S' then 'Store'when b.account_type='O' then 'Online'else 'Offcard'end,Party = Casewhen a.party_id is null then 'Anon'when a.party_id <0 then 'Anon'else 'Party'end,Shopper_Profile = Casewhen pp.shopper_profile = 'M' then 'Multi'when pp.shopper_profile = 'S' then 'Store'when pp.shopper_profile = 'O' then 'Online'else 'None'end,Transactions = Casewhen b.total_value_exc_vat >0 then 1end,/*Customer Count*/NEWCUSTCOUNT = CASEwhen a.party_id Is nullthen a.account_idwhen a.party_id <0then a.account_idwhen a.party_id>=0then pp.party_idelse '0'end,/*Adjust open week grouping here*/WEEKSOPEN = case when a.party_id IS null then case whenceiling ((datediff (ww,a.enrolment_date,b.transaction_date)+1)) between 1 and 52 then ceiling ((datediff (ww,a.enrolment_date,b.transaction_date)+1))else '9999' end when a.party_id <0 then case whenceiling ((datediff (ww,a.enrolment_date,b.transaction_date)+1)) between 1 and 52 then ceiling ((datediff (ww,a.enrolment_date,b.transaction_date)+1))else '9999' end when a.party_id >=0 then case whenceiling ((datediff (WW,pp.inception_date,b.transaction_date)+1)) between 1 and 52then ceiling ((datediff (ww,pp.inception_date,b.transaction_date)+1))else '9999' end end,/*Adjust Open Year Logic Here*/Open_Week = case when a.party_id IS null then case when mca.mat_year_end >=2011 then mca.mat_week_short else '9999' end when a.party_id <0 then case when mca.mat_year_end >=2011 then mca.mat_week_short else '9999' end when a.party_id >=0 then case when mcpp.mat_year_end >=2011 then mcpp.mat_week_short else '9999' end else'9999' end,/*Adjust Open Year Logic Here*/Open_Year = case when a.party_id IS null then case when mca.mat_year_end >=2011 then mca.mat_year_end else '9999' end when a.party_id <0 then case when mca.mat_year_end >=2011 then mca.mat_year_end else '9999' end when a.party_id >=0 then case when mcpp.mat_year_end >=2011 then mcpp.mat_year_end else '9999' end else '9999' end,Spend = b.total_value_exc_vat,Items = b.item_count,TranCluster = sa.store_cluster_name,mw = mc.mat_week_short,my = mc.mat_year_endfrom mart.account aright join mart.basket bon a.account_id = b.account_idleft join mart.party_profile ppon a.party_id = pp.party_idjoin mart.MAT_CALENDAR mcon b.transaction_date = mc.dim_dateleft join mart.MAT_CALENDAR mcaon a.enrolment_date = mca.dim_dateleft join mart.MAT_CALENDAR mcppon pp.inception_date = mcpp.dim_datejoin mart.store_analysis saon b.store_id = sa.store_idwhere mc.mat_week_short between 1 and 52and mc.mat_year_end = 2011) AAgroup by AA.Spend_Channel,AA.Party,AA.Shopper_Profile,AA.mw,AA.my,AA.TranCluster,AA.WEEKSOPEN,AA.Open_Year,AA.Open_Week |
|