March 27, 2007 at 7:46 am
Hi,
I have a set of sales data coming from a legacy system in another part of the world... the sales data is in an excel format, with all of the std information you would expect to see on sales order line items.
I would like to add a composite key to the data to make sure that it satisfies table constraints when I consolidate the data into a WW dataset.
One selection for part of the composite primary key is the invoice number. What I want to add is a line number for each invoice number present, such as
INVOICE linenum
12345 1
12345 2
12346 1
12347 1
12347 2
12347 3
and so on. Obviously I can use an aggregrate fn to find the total number of lines per invoice, but then how to split and label each invoice line with a line number?
Any ideas / thoughts greatly appreciated.
March 27, 2007 at 8:28 am
--Table
CREATE TABLE LineNums (INVOICE int, linenum int)
--Data
INSERT INTO LineNums (INVOICE) VALUES (12345)
INSERT INTO LineNums (INVOICE) VALUES (12345)
INSERT INTO LineNums (INVOICE) VALUES (12346)
INSERT INTO LineNums (INVOICE) VALUES (12347)
INSERT INTO LineNums (INVOICE) VALUES (12347)
INSERT INTO LineNums (INVOICE) VALUES (12347);
--Update
WITH CTE AS
(SELECT INVOICE, linenum, ROW_NUMBER() OVER (PARTITION BY INVOICE ORDER BY INVOICE) AS linenum2
FROM LineNums)
UPDATE CTE SET linenum = linenum2
--Test it
SELECT * FROM LineNums
John
March 27, 2007 at 9:00 am
Cheers John that works a treat...
got some brushing up to do on my t-sql code!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply