How to Put Sequence Column in this table

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 2000 Forums

SQL Server Development (2000)

How to Put Sequence Column in this table

Author

Topic

Clages1
Yak Posting Veteran

69 Posts

Posted - 2007-05-30 : 15:31:48

Hi

I have a table like this

Customer , Invoice , Goods ,seq , etc
33 , 12345 , AA , 0 , xx
33 , 12345 , BB , 0 , xx
33 , 12345 , CC , 0 , xx

36 , 5566 , CC , 0 , xx
36 , 5566 , SS , 0 , xx

In the Insert Section , I dont have a sequence Yet
after the Insert i would like to do an UPDATE to put
sequence in Column SEQ
them my new table would be

Customer , Invoice , Goods ,seq , etc
33 , 12345 , AA , 1 , xx
33 , 12345 , BB , 2 , xx
33 , 12345 , CC , 3 , xx

36 , 5566 , CC , 1 , xx
36 , 5566 , SS , 2 , xx

The Sequence is broken by Customer+Invoice

Any help will be apreciated
Tks
CLages

Clages1
Yak Posting Veteran

69 Posts

Posted - 2007-05-30 : 18:02:05

Hi, O solved by my self using this Cursor
despite is not a elegant solution

If somebody else has a better solution , i would like to know

C. Lages

-- =============================================
-- Declare and using an UPDATE cursor
-- =============================================
DECLARE @cgc Numeric (14,0) ,
@numero Int ,
@produto Char(14) ,

@cgc1 Numeric (14,0) ,
@numero1 Int ,
@produto1 Char(14)

DECLARE cursor_name CURSOR
FOR SELECT tmp54_cgc , tmp54_numero, tmp54_codigoproduto
FROM teste.dbo.dectmp54x
Order BY TMP54_CGC, tmp54_numero ,tmp54_codigoproduto
--FOR UPDATE of tmp54_sequencia

DECLARE @count smallint
SELECT @count = 0

OPEN cursor_name
FETCH NEXT FROM cursor_name INTO @cgc1 , @numero1 , @produto1
SET @cgc = @cgc1
set @numero = @numero1
set @produto = @Produto1

WHILE (@@fetch_status = 0)
BEGIN
If @cgc = @cgc1 and
@numero = @numero1
BEGIN
SELECT @count = @count + 1
UPDATE teste.dbo.dectmp54x
SET tmp54_sequencia = @count
WHERE @cgc = tmp54_cgc and
@numero = tmp54_numero and
@Produto = tmp54_codigoproduto
FETCH NEXT FROM cursor_name INTO @cgc , @numero , @produto
END
else
BEGIN
SELECT @count = 1
UPDATE teste.dbo.dectmp54x
SET tmp54_sequencia = @count
WHERE @cgc = tmp54_cgc and
@numero = tmp54_numero and
@Produto = tmp54_codigoproduto

SET @cgc1 = @cgc
set @numero1 = @numero
set @produto1 = @produto
FETCH NEXT FROM cursor_name INTO @cgc , @numero , @produto
END
END

CLOSE cursor_name
DEALLOCATE cursor_name
GO

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-05-30 : 19:05:41

You could try something like this:

DECLARE @T TABLE (Customer INT, Invoice INT, Goods VARCHAR(2), seq INT)

INSERT @T
SELECT 33 , 12345 , 'AA' , 0
UNION SELECT 33 , 12345 , 'BB' , 0 
UNION SELECT 33 , 12345 , 'CC' , 0
UNION SELECT 36 , 5566 , 'CC' , 0
UNION SELECT 36 , 5566 , 'SS' , 0

SELECT *
FROM @T

UPDATE t
SET Seq = (
		SELECT COUNT(*) 
		FROM @t AS a 
		WHERE
			a.Customer = t.Customer
			AND a.Invoice = t.Invoice
			AND a.Goods <= t.Goods
		)
FROM @t t

SELECT *
FROM @T

-Ryan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-31 : 01:05:45

Clages1, where do you want to show the data?
If you use Reports, you can easily group it by customer and put report number and reset it by each group

Madhivanan

Failing to plan is Planning to fail

Clages1
Yak Posting Veteran

69 Posts

Posted - 2007-05-31 : 15:26:54

Tks Ryan

Your solutions works fine for me

C.Lages
Rio de Janeiro

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-01 : 00:39:10

quote:
Originally posted by Clages1

Tks Ryan

Your solutions works fine for me

C.Lages
Rio de Janeiro

So, you will run that update statement whenever new data are added?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources