Cursor or clever SQL needed? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Cursor or clever SQL needed?

Author

Topic

vweb1
Starting Member

3 Posts

Posted - 2009-11-11 : 05:03:04

Hi all

I'm writing a stored proc to process some data. I have written a query to gather the information I need, but then need to process this data (which will be inside a temp table) again to add an extra column which will contain a 'ranking'. for example:

The temp table would have the following:

DEPART PRODUCTID SHOPREF NUMBER_SOLD
--------------------------------------------
242 BASKET-F-XSTR-46 40 1
242 BASKET-SGFC50-7 40 2
242 BASKET-PS-T5 40 4
242 BASKET-TFG-MH-ALARM 40 4
242 Basket-TFG-REEL 40 4
242 Basket-TFG-XSLEEP 40 7

267 FREE-F-SGFC100-10 40 5
267 FREE-TFG-FC34-M 40 6
267 FREE-TFG-FC34-XXL 40 6
267 FREE-TFG-FC34-L 40 8
267 FREE-TFG-CAMOC-AP 40 33

I then need to process this somehow and add extra column called ranking that, for each Department set a ranking value depending on the number sold (ie for dept 242, Basket-TFG-XSLEEP sold 7, thus rank would be 1)

Desired results:

DEPART PRODUCTID SHOPREF NUMBER_SOLD RANK
-----------------------------------------------------
242 BASKET-F-XSTR-46 40 1 6
242 BASKET-SGFC50-7 40 2 5
242 BASKET-PS-T5 40 4 4
242 BASKET-TFG-MH-ALARM 40 4 3
242 Basket-TFG-REEL 40 4 2
242 Basket-TFG-XSLEEP 40 7 1

267 FREE-F-SGFC100-10 40 5 5
267 FREE-TFG-FC34-M 40 6 4
267 FREE-TFG-FC34-XXL 40 6 3
267 FREE-TFG-FC34-L 40 8 2
267 FREE-TFG-CAMOC-AP 40 33 1

What are peoples best ideas for achieving this... Is there any clever sql that could be used? We are using MS SQL 2000

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-11-11 : 10:38:38

how are you creating the temp table, you might be able to do it then. show us how you are creating your temp table. do it one sweep

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

vweb1
Starting Member

3 Posts

Posted - 2009-11-11 : 10:57:11

This is the SQL that creates the temp table

select ms.shoplistrec,
ms.shopref,
count(os.shoplistrec) as thecount,
ms.DEPARTMENTREF,
ms.CounterRef
into #tmppASP
from orderskus os,
orders o,
MultipleShopList ms
where os.orderid=o.orderid
and o.orderdate between (getdate()-14) and getdate()
and ms.shoplistrec = os.shoplistrec
and ms.active = 'Yes'
group by ms.shoplistrec,
ms.shopref,
ms.DEPARTMENTREF,
ms.CounterRef
order by ms.shopref,
ms.DEPARTMENTREF,
ms.CounterRef,thecount,
ms.shoplistrec

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-11-11 : 13:00:01

You should be able to do this without a temp table using a Ranking function, for example:

select 
	ms.shoplistrec,
	ms.shopref, 
	count(os.shoplistrec) as thecount,
	ms.DEPARTMENTREF,
	ms.CounterRef,
	ROW_NUMBER() OVER (PARTITION BY DEPARTMENTREF ORDER BY count(os.shoplistrec) DESC) AS [Rank]
from 
	orderskus os, 
	orders o,
	MultipleShopList ms
where 
	os.orderid=o.orderid
	and o.orderdate between (getdate()-14) and getdate()
	and ms.shoplistrec = os.shoplistrec
	and ms.active = 'Yes'
group by 
	ms.shoplistrec,
	ms.shopref,
	ms.DEPARTMENTREF,
	ms.CounterRef
order by 
	DEPARTMENTREF,
	shopref,
	CounterRef,
	thecount, 
	shoplistrec

PS: You should also look into using ANSI style joins.

vweb1
Starting Member

3 Posts

Posted - 2009-11-11 : 16:40:12

Hi thanks, but SQL 2000 I'm afraid, so above won't work

p.s I really don't like ANSI joins, looks too much like ACCESS database queries and for me makes it harder to see.

Old school Sybase LOL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources