inner join help - SQL Server Forums

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

inner join help

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-15 : 04:00:08

Hey guys

Sorry to pester you so much, But i am really struggling with this particular query

my query so far is

SELECT sub.*,

case when rm_sales_band = '2M to 4M'
and MCC_Code not in ('7997','7941') then 'Kirsty'
when fdmsaccountno IN ('878177270880','878231021881','878233596880','878970059886','878970013883') OR MCC_Code in ('7997','7941')
then 'Ian Hatton'

else RM end as RM

FROM
(SELECT
[DBA_Name],
o.[fdmsaccountno],
[ho],
[rm_sales_band],
sum ([Gross_Sales]) as Sales,
[rm_code],
[post_code],
[Open_Date],

CASE
WHEN Isnumeric(RIGHT(LEFT(post_code, 2), 1)) = '0' THEN
LEFT(post_code, 2)
ELSE LEFT(post_code, 1)
END AS 'sPostcode',
[mcc_code]
From [FDMS].[dbo].[Fact_Financial_History_Annualised] f
inner join Dim_Outlet o
on f.FDMSAccountNo = o.FDMSAccountNo
WHERE [rm_sales_band]IN ( '2M to 4m', '4m +' )
AND [ho] = 'Y'
and Account_Status = '16'
and LBG_Status <> 'Accepted'
AND iso_account = 'N'
AND Open_Date < dateadd(mm, -3, getdate())
and Agent_Chain_No not in ('878970059886', '878970013883')
AND o.fdmsaccountno NOT IN (SELECT [ta_mid]
FROM
fdms_partnerreporting.tmp.trade_assocations)

group by
[DBA_Name],
o.[fdmsaccountno],
[ho],
[rm_sales_band],
[rm_code],
[post_code],
[Open_Date],
[MCC_Code]

) Sub
INNER JOIN [geo_pca_sellers]
ON [pca] = spostcode

order by DBA_Name

On a separate query i have the following

SELECT TOP 1000 [Band_Key]
,[Sales_Band]
FROM [FDMS].[dbo].[Dim_Sales_Band]

which produces these sales banding.

Band_Key Sales_Band
00 No Sales
01 0 to 50k
02 50k to 100k
03 100k to 250k
04 250k to 500k
05 500k to 1m
06 1m to 5m
07 5m to 10m
08 10m to 50m
09 50m to 100m
10 100m +

i would like to get the sales band from the query above into my first query

i would appreciate any help possible

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-15 : 04:26:32

You already have the sales_band - what do you want to do with the data in this table.
It looks redundant as you already have the text in the table

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-15 : 04:29:36

Or maybe yoou don't and that's the issue.
I would usea ct e to make the final query clearer

;with sub as
(
SELECT
[DBA_Name],
o.[fdmsaccountno],
[ho],
[rm_sales_band] = fb.Sales_Band ,
sum ([Gross_Sales]) as Sales,
[rm_code],
[post_code],
[Open_Date],
CASE
WHEN Isnumeric(RIGHT(LEFT(post_code, 2), 1)) = '0' THEN
LEFT(post_code, 2)
ELSE LEFT(post_code, 1)
END AS 'sPostcode',
[mcc_code]
From [FDMS].[dbo].[Fact_Financial_History_Annualised] f
inner join FDMS].[dbo].[Dim_Sales_Band] sb
on fb.Band_Key = f.Band_Key
inner join Dim_Outlet o
on f.FDMSAccountNo = o.FDMSAccountNo
WHERE fb.Sales_Band IN ( '2M to 4m', '4m +' )
AND [ho] = 'Y'
and Account_Status = '16'
and LBG_Status <> 'Accepted'
AND iso_account = 'N'
AND Open_Date < dateadd(mm, -3, getdate())
and Agent_Chain_No not in ('878970059886', '878970013883')
AND o.fdmsaccountno NOT IN (SELECT [ta_mid]
FROM
fdms_partnerreporting.tmp.trade_assocations)
group by
[DBA_Name],
o.[fdmsaccountno],
[ho],
[rm_sales_band],
[rm_code],
[post_code],
[Open_Date],
[MCC_Code]
)
SELECT sub.*,
case when rm_sales_band = '2M to 4M'
and MCC_Code not in ('7997','7941') then 'Kirsty'
when fdmsaccountno IN ('878177270880','878231021881','878233596880','878970059886','878970013883') OR MCC_Code in ('7997','7941')
then 'Ian Hatton'
else RM end as RM
FROM
Sub
INNER JOIN [geo_pca_sellers]
ON [pca] = spostcode
order by DBA_Name

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-15 : 04:39:22

the rm sales band is different to the sales band

The rm sales band says either
2M to 4M
4M +

The sales band in [FDMS].[dbo].[Dim_Sales_Band], give me a better break down

So realistically, what needs to happen is that i need to link the sales band to the sum ([Gross_Sales]) as Sales,

And that would give me the correct /accurate banding

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-15 : 04:52:14

hi

i am getting the error msg

Msg 102, Level 15, State 1, Line 19
Incorrect syntax near ']'.
Msg 156, Level 15, State 1, Line 33
Incorrect syntax near the keyword 'group'.

any advice ?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-15 : 05:20:55

In that case you need to chage the sales_band table to have min and max columns for the compare. You can parse it in the query but that would be unnecessarily complex.

I just missed out a [ but doesn't matter as you need the sales band in the outer query.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-15 : 05:31:45

I am new to this so bare with me if my terminology is incorrect

what do you think is the easiest solution ?
needs need to be completed by 11, as its being rolled out :( and i am so far behind

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-15 : 05:35:58

maybe something like

;with sub as
SELECT
[DBA_Name],
o.[fdmsaccountno],
[ho],
[rm_sales_band] ,
sum ([Gross_Sales]) as Sales,
[rm_code],
[post_code],
[Open_Date],
CASE
WHEN Isnumeric(RIGHT(LEFT(post_code, 2), 1)) = '0' THEN
LEFT(post_code, 2)
ELSE LEFT(post_code, 1)
END AS 'sPostcode',
[mcc_code]
From [FDMS].[dbo].[Fact_Financial_History_Annualised] f
inner join Dim_Outlet o
on f.FDMSAccountNo = o.FDMSAccountNo
WHERE fb.Sales_Band IN ( '2M to 4m', '4m +' )
AND [ho] = 'Y'
and Account_Status = '16'
and LBG_Status <> 'Accepted'
AND iso_account = 'N'
AND Open_Date < dateadd(mm, -3, getdate())
and Agent_Chain_No not in ('878970059886', '878970013883')
AND o.fdmsaccountno NOT IN (SELECT [ta_mid]
FROM
fdms_partnerreporting.tmp.trade_assocations)
group by
[DBA_Name],
o.[fdmsaccountno],
[ho],
[rm_sales_band],
[rm_code],
[post_code],
[Open_Date],
[MCC_Code]
) ,
sales_band_tmp as
(
select minval = case when Sales_Band = 'No Sales' then null
when Sales_Band = '100m+' then '100m'
else LEFT(Sales_Band,charindex('to',Sales_Band)-1) end
maxval = case when Sales_Band = 'No Sales' then '0k'
when Sales_Band = '100m+' then null
else right(Sales_Band,charindex('ot',reverse(Sales_Band))-1) end
from [FDMS].[dbo].[Dim_Sales_Band]
) ,
salesband as
(
select minval = left(minval,LEN(minval)-1) * case when RIGHT(minval,1) = 'K' then 1000 else 1000000 end + 1
, maxval = left(maxval,LEN(maxval)-1) * case when RIGHT(maxval,1) = 'K' then 1000 else 1000000 end
from sales_band_tmp
)
SELECT sub.*,
case when rm_sales_band = '2M to 4M'
and MCC_Code not in ('7997','7941') then 'Kirsty'
when fdmsaccountno IN ('878177270880','878231021881','878233596880','878970059886','878970013883') OR MCC_Code in ('7997','7941')
then 'Ian Hatton'
else RM end as RM
FROM
Sub
INNER JOIN [geo_pca_sellers]
ON [pca] = spostcode
inner join salesband sb
on Sales between coalesce(sb.minval, 0) and coalesce(sb.maxval, Sales)
order by DBA_Name

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-15 : 05:39:56

better if your sales band table was

Band_Key minval, maxval
0 null, 0
1 1, 50000
2 50001, 100000
3 100001, 250000
4 250001, 500000
5 ...
6
7
8
9
10 100000001, null

then yoou can use that instead of the two temp tables. You can have another column with the text if you like.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources