Sequential Numbering

Question

Post reply

Sequential Numbering

rdg

Mr or Mrs. 500

Points: 516
More actions
May 10, 2004 at 9:38 am

#108198

I am having a problem with a sequence number I need to generate in an extract.
The extract will show (amongst other data) a store code, a sale transaction number and a method of payment. There may be multiple methods of payment for each sale transaction.
What I am trying to achieve is a sequence number for the method of payment which for the most part will be 1. This sequence number will need to reset after each sale transaction number:
Store Code   Sale Code   Payment Type   Seq
A123            1234         Cash                1
A123            1234         Chq                  2
A123            1235         Cash                 1
A123            1236         Credit Card        1
A123            1236         Gift Vocuher       2
A123            1236         Coupon              3

The code I have written at the moment includes a cursor (please don't shout at me!) which I would like to get rid of, because it slows down the code.
Here is a sample
CREATE TABLE
[dbo].[#SAP_Sales_Tender] (
[tenderline_id] VARCHAR (14) NULL
,[sale_code] VARCHAR (12) NULL
,[tenderline_id] VARCHAR (14) NULL
,[seq_num] VARCHAR (4) NULL
&nbsp ON [PRIMARY]
GO
/* Insert the tender detail data into the tender detail temp table */
INSERT INTO #Sales_Tender
SELECT
tl.tenderline_id
  ,tl.sale_code
,1 AS [Sequence]
FROM tenderline tl /* This table has 60 million records */
,#Sales_Header ssh /* This is a temp table which has the relevant sale_codes for the period in question - usually around 200,000 */
WHERE (tl.sale_code = ssh.sale_code)
GO
/* Now the bit no one likes! */
/* Set up cursor with sale codes with multiple tenders */
/* This reduces the number of uniques sale_codes to around 2000 */
DECLARE SaleList CURSOR FOR
SELECT DISTINCT
sst.sale_code
FROM #Sales_Tender sst
GROUP BY
sst.sale_code
HAVING count(*) > 1
ORDER BY
sst.sale_code
OPEN SaleList
DECLARE @SaleCode VARCHAR(20)
/* Update tender table with new sequence number */
FETCH NEXT FROM SaleList INTO @SaleCode
WHILE (@@FETCH_STATUS = 0 )
BEGIN
DECLARE @TenderCount INT
SET @TenderCount = 0
UPDATE #Sales_Tender
SET #Sales_Tender.[seq_num] = @TenderCount
  ,@TenderCount = @TenderCount + 1
WHERE #Sales_Tender.sale_code = @SaleCode
FETCH NEXT FROM SaleList INTO @SaleCode
END
CLOSE SaleList
DEALLOCATE SaleList
GO
Can any one help with a better solution?
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

vishy Mr or Mrs. 500 Points: 596 More actions · Answer 1

rdg,

here is how you can do it.

example in notrhwind db

select customerid, orderid, orderdate, freight ,(select count(*) from orders y where y.orderid <= x.orderid and y.customerid = x.customerid) as seq_num

from orders x

order by customerid, orderid

just giving seq_num for a given customer. hope it helps.

thanks

Vishy

rdg Mr or Mrs. 500 Points: 516 More actions · Answer 2

Vishy,

Absolutely perfect - thanks!

I was obviously trying to complicate matters.

Thanks again

rdg