Urgent Row Number query - 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 Row Number query

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-22 : 04:50:13

Hey guys and girls

I need some help, In excel i know how to get the answer i want, however in sql i am struggling.

In excel i would put the filter on the headers and select the column
“RMSC” R15

From there i would then move along to the column
“Sales_Annualised” and arrange From largest to smallest

The next step would be is to go back to the RMSC Column

Put the first row as ‘R15’ and the next row as ‘R16’, ‘r15’, ‘r16’, etc etc, Untill all the rows have been changed

I believe i need a row_number function, but iam not too sure.

Can anyone help me out with this ?

My current query is

SELECT sub.*,

case when SalesBandRM = '2M to 4M'
and MCC_Code not in ('7997','7941') then 'R15'
when fdmsaccountno IN ('878177270880','878030700883','878231021881','878233596880','878970059886','878970013883') OR MCC_Code in ('7997','7941')
then 'R05'

else RMSC end as RMSC

into #RM
FROM
(SELECT
[DBA_Name],
o.[fdmsaccountno],
[ho],
sum ([Gross_Sales]) as Sales_Annualised,
dbo.salesbandRM(sum(Gross_Sales))as SalesBandRM,
[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 [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],
[post_code],
[Open_Date],
[MCC_Code]

having sum([Gross_Sales])> 1999999

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

order by DBA_Name

--Drop table #RM

Declare @dateYTD varchar(10)
set @dateYTD = (select year(MAX(hst_date_processed))
from fdms.dbo.Fact_Financial_History)
SELECT ParentID
,sum([hst_sales_amt]) as Actual_SalesYTD
Into #YTD
FROM [FDMS].[dbo].[Fact_Financial_History] f inner join dim_outlet o
on f.hst_merchnum = o.FDMSAccountNo_First9
Where year(hst_date_processed) = @dateYTD
group by o.ParentID

--Drop table #YTD

--Rolling 12 Months --
Declare @date varchar(10)
set @Date = (select dateadd(MM,-12,max(hst_date_processed))
from FDMS.dbo.Fact_Financial_History)
SELECT ParentID
,sum([hst_sales_amt]) as Rolling_12
Into #R12
FROM [FDMS].[dbo].[Fact_Financial_History] f full outer join Dim_Outlet o
on f.hst_merchnum = o.FDMSAccountNo_First9
Where hst_date_processed > @date
group by o.ParentID

Select
r.FDMSAccountNo,
r.DBA_Name,
Post_Code,
Open_Date,
Sales_Annualised,
SalesBandRM,
Rolling_12,
Actual_SalesYTD,
RMSC
from #R12 r12
Inner Join #RM r on r12.parentid = r.fdmsaccountno
inner join #YTD y on r.FDMSAccountNo = y.ParentID

--Drop table #R12

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-22 : 05:03:24

yes. There is a row_number() http://msdn.microsoft.com/en-us/library/ms186734.aspx

KH
[spoiler]Time is always against us[/spoiler]

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-22 : 05:19:03

Hi khtan, Thank you for your reply

I have read numerous pages on row_number() but i still dont get where to implment it within my query, i also dont understand whether it can do what i listed above. Confused.Com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-22 : 05:43:11

i don't really understand what you want here, perhaps you can explain it further ?

KH
[spoiler]Time is always against us[/spoiler]

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-22 : 05:57:49

Hi

I have uploaded some pictures on the following link
Which should show you the steps

http://postimage.org/gallery/gtofx7i/

There should be four photos attached

First photo , is how sql excutes the file
Seond photo = R15 accounts
Third photo, assigning r15 accounts largest to smallest using the Sales_Annualised” tab
fourth photo= The final outcome, how i want to assign the data

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-22 : 08:09:15

run this query after your first query


update	rm
set	RMSC	= case when rn % 2 = 0 then 'R15' else 'R16' end
from
(
	select	RMSC, rn = row_number() over (order by Sales_Annualised desc)
	from	#RM
	where	RMSC	= 'R15'
) rm

KH
[spoiler]Time is always against us[/spoiler]

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-22 : 08:39:02

HI khtan

Thank you for your help

You have saved the day :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-22 : 08:57:57

you can actually deleted that duplicate thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=178075

provided that nobody has posted in it

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources