Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Calculate the position for a combination of columns Expand / Collapse
Author
Message
Posted Wednesday, December 12, 2012 3:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 5:14 AM
Points: 119, Visits: 486
Hi guys,

I have the following situation:

A table with receipts key and item key. One receipt has one or more items. I would like to query this table and for every receipt assing a position number to every item.

Example:

Receipt_key Item_key Position
0001 02654 1
0001 35544 2
0002 02654 1
0002 85466 2
0002 84945 3


I`ve spent some time but I can´t figure out a solution.

Any comment would be appreciated.

Kind Regards


Paul Hernández
http://hernandezpaul.wordpress.com/
https://twitter.com/paul_eng
Post #1395544
Posted Wednesday, December 12, 2012 3:51 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 16, 2014 10:02 AM
Points: 1,372, Visits: 1,566
You can do it like this:

Creating temp table and inserting test values
create table #temp
(Receipt_key varchar(10),
Item_key varchar(10) ,
Position int);

insert #temp
values
('0001','02654',NULL),
('0001','35544',NULL),
('0002','02654',NULL),
('0002','85466',NULL),
('0002','84945',NULL)


Final update statement
WITH upd_recs
AS (SELECT Receipt_key,
Item_key,
Row_number()
OVER (
partition BY Receipt_key
ORDER BY Receipt_key, Item_Key) rnk
FROM #temp)
UPDATE #temp
SET Position = rnk
FROM upd_recs a
INNER JOIN #temp b
ON a.Receipt_key = b.Receipt_key
AND a.Item_key = b.Item_key

Hope this is what you are looking for?


~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1395562
Posted Wednesday, December 12, 2012 7:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 5:14 AM
Points: 119, Visits: 486
Hi Lokesh Vij,

thanks so much for your quick and HELPFUL answer. That´s right, that is exactly what I needed.

In fact, I´m loading the final table using an SSIS DFT, I used a simplify version of my query in the original question, with this part of your query was enough:


SELECT Receipt_key,
Item_key,
Row_number()
OVER (
partition BY Receipt_key
ORDER BY Receipt_key, Item_Key) rnk
FROM [My Transaction Table]


The key point is the OVER statement, very useful, very handy.

Thanks again for helping me to learn something new .

Best Regards,

Paul




Paul Hernández
http://hernandezpaul.wordpress.com/
https://twitter.com/paul_eng
Post #1395678
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse