Urgent query helop - 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

Urgent query helop

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-03 : 08:17:18

HI guys

i need some help linking two tables together to get an end result

Please find below the code i have used

The first part of the query provides me with the info i need

SELECT sub.*,
case when rm_sales_band = '2M to 4M' then 'Kirsty' else RM end as rm
into #rmtmp
FROM
(SELECT[fdmsaccountno],
[ho],
[rm_sales_band],
[rm_code],
[post_code],
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].[dim_outlet]
WHERE [rm_sales_band]IN ( '2M to 4m', '4m +' )
AND [ho] = 'Y'
AND rm_code = 'na'
AND iso_account = 'N'
AND fdmsaccountno NOT IN (SELECT [ta_mid]
FROM
fdms_partnerreporting.tmp.trade_assocations)) Sub
INNER JOIN [geo_pca_sellers]
ON [pca] = spostcode
select * from #rmtmp

i have created a second query

Which looks at all all mccs which “r” seller code have dealt with and gives highest seller code which deals with that particular mcc

and the query is

with
CTE_Group as
(
select seller_code, mcc_code, count(mcc_code) as mcc_count
from [FDMS].[dbo].[Dim_Outlet]
where ( Rm_Sales_Band = '2M to 4m' or Rm_Sales_Band = '4m +' ) and
(Seller_Code like 'r%') group by seller_code, mcc_code
),

CTE_RowNum as
(
select
c.*,
ROW_NUMBER() OVER(PARTITION BY mcc_code
ORDER BY mcc_count desc) as RowNum
from CTE_Group c
)

select * from CTE_RowNum
where RowNum = 1
order by MCC_Code desc,mcc_count desc

What i need from here is the seller code linked to that particular mcc code, brought over to the original query. If the following seller codes are not assigned to a particular mcc can you populate N.A
R05
R10
R12
R13

Please find attached three images
query1-
query2
ideal results

From the ideal results image,i only need the seller code brought over

Please see links below

http://www.freeimagehosting.net/bsstt
http://www.freeimagehosting.net/wxgcp
http://www.freeimagehosting.net/r817f

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-03 : 09:40:29

looks like what you need is to join first query to second select using left join on related columns...

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

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-03 : 09:42:53

OOh, thank you for your response, How can i do a left query ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-03 : 10:04:30

[code]
with
CTE_Group as
(
select seller_code, mcc_code, count(mcc_code) as mcc_count
from [FDMS].[dbo].[Dim_Outlet]
where ( Rm_Sales_Band = '2M to 4m' or Rm_Sales_Band = '4m +' ) and
(Seller_Code like 'r%') group by seller_code, mcc_code
),

CTE_RowNum as
(
select
c.*,
ROW_NUMBER() OVER(PARTITION BY mcc_code
ORDER BY mcc_count desc) as RowNum
from CTE_Group c
)

select * from ( copy first query here) q
left join CTE_RowNum c
on q.mcc_code = c.mcc_code
and c.RowNum = 1
order by q.MCC_Code desc,c.mcc_count desc
[/code]

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

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-03 : 10:24:38

Hi Visakhm

are you suggesting the code should be

with
CTE_Group as
(
select seller_code, mcc_code, count(mcc_code) as mcc_count
from [FDMS].[dbo].[Dim_Outlet]
where ( Rm_Sales_Band = '2M to 4m' or Rm_Sales_Band = '4m +' ) and
(Seller_Code like 'r%') group by seller_code, mcc_code
),

CTE_RowNum as
(
select
c.*,
ROW_NUMBER() OVER(PARTITION BY mcc_code
ORDER BY mcc_count desc) as RowNum
from CTE_Group c
)

select * from ( SELECT sub.*,
case when rm_sales_band = '2M to 4M' then 'Kirsty' else RM end as rm
into #rmtmp
FROM
(SELECT[fdmsaccountno],
[ho],
[rm_sales_band],
[rm_code],
[post_code],
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].[dim_outlet]
WHERE [rm_sales_band]IN ( '2M to 4m', '4m +' )
AND [ho] = 'Y'
AND rm_code = 'na'
AND iso_account = 'N'
AND fdmsaccountno NOT IN (SELECT [ta_mid]
FROM
fdms_partnerreporting.tmp.trade_assocations)) Sub
INNER JOIN [geo_pca_sellers]
ON [pca] = spostcode
select * from #rmtmp) q
left join CTE_RowNum c
on q.mcc_code = c.mcc_code
and c.RowNum = 1
order by q.MCC_Code desc,c.mcc_count desc

i am recieving the following error msg in sql

Msg 156, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'into'.
Msg 102, Level 15, State 1, Line 43
Incorrect syntax near 'Sub'.
Msg 102, Level 15, State 1, Line 46
Incorrect syntax near ')'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-03 : 10:30:28

oh you're dumping it to a temp table. then you need only this


with
CTE_Group as
(
select seller_code, mcc_code, count(mcc_code) as mcc_count
from [FDMS].[dbo].[Dim_Outlet]
where ( Rm_Sales_Band = '2M to 4m' or Rm_Sales_Band = '4m +' ) and
(Seller_Code like 'r%') group by seller_code, mcc_code
),



CTE_RowNum as
(
select 
c.*, 
ROW_NUMBER() OVER(PARTITION BY mcc_code 
ORDER BY mcc_count desc) as RowNum
from CTE_Group c
) 

select * from  #rmtmp q
left join CTE_RowNum c
on q.mcc_code = c.mcc_code
and c.RowNum = 1
order by q.MCC_Code desc,c.mcc_count desc

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

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-03 : 10:33:28

visakh16
you are my *

thank you very much for your help :)

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-03 : 10:42:24

visakh16

The results bring over mmc_code, mcc_count and also row num }

Is there anyway you can remove that ? so it only bring over the seller_code

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-03 : 11:12:43

yep... remove * with whatever columns you want in SELECT

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources