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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Table join help

Author  Topic 

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 :)

select
AA.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 OpenCustinWeek

from (select

Spend_Channel = Case
When b.account_type='S' then 'Store'
when b.account_type='O' then 'Online'
else 'Offcard'
end,

Party = Case
when a.party_id is null then 'Anon'
when a.party_id <0 then 'Anon'
else 'Party'
end,

Shopper_Profile = Case
when pp.shopper_profile = 'M' then 'Multi'
when pp.shopper_profile = 'S' then 'Store'
when pp.shopper_profile = 'O' then 'Online'
else 'None'
end,

Transactions = Case
when b.total_value_exc_vat >0 then 1
end,

/*Customer Count*/
NEWCUSTCOUNT = CASE

when a.party_id Is null
then a.account_id

when a.party_id <0
then a.account_id

when a.party_id>=0
then pp.party_id

else '0'

end,



/*Adjust open week grouping here*/
WEEKSOPEN = case

when a.party_id IS null
then
case
when
ceiling ((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
when
ceiling ((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
when
ceiling ((datediff (WW,pp.inception_date,b.transaction_date)+1)) between 1 and 52
then 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_end

from mart.account a

right join mart.basket b
on
a.account_id = b.account_id

left join mart.party_profile pp
on
a.party_id = pp.party_id

join mart.MAT_CALENDAR mc
on b.transaction_date = mc.dim_date

left join mart.MAT_CALENDAR mca
on a.enrolment_date = mca.dim_date

left join mart.MAT_CALENDAR mcpp
on pp.inception_date = mcpp.dim_date


join mart.store_analysis sa
on b.store_id = sa.store_id

where mc.mat_week_short between 1 and 52
and mc.mat_year_end = 2011

) AA

group by
AA.Spend_Channel,
AA.Party,
AA.Shopper_Profile,
AA.mw,
AA.my,
AA.TranCluster,
AA.WEEKSOPEN,
AA.Open_Year,
AA.Open_Week
   

- Advertisement -