Incremental numbers by order ID

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)

Incremental numbers by order ID

Author

Topic

alemos
Starting Member

16 Posts

Posted - 2008-08-01 : 16:12:14

This must be simple, but I cannot find a solution. I have a query that returns the payment plan for the orders. I need to number the payments but there is no such field in the database. Example of what I need:


ORDER_ID  TIMESTAMP  INSTALLMENT  VALUE  TOTAL
===============================================
    1     2008-08-31  1           100.00 500.00
    1     2008-08-31  2           100.00 500.00
    1     2008-08-31  3           100.00 500.00
    1     2008-08-31  4           100.00 500.00
    1     2008-08-31  5           100.00 500.00
    2     2008-08-31  1           100.00 300.00
    2     2008-08-31  2           100.00 300.00
    2     2008-08-31  3           100.00 300.00
    3     2008-08-31  1           500.00 500.00

My query returns the information except the installment number. How can I do that in a query without having to use a cursor to loop through a temp table to update that field?

Thanks!

Aécio Lemos
http://www.vlsweb.com.br
O primeiro provedor de hospedagem gerenciada do Brasil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-02 : 05:40:41

something like this

SELECT IDENTITY(int,1,1) AS Sequence,
ORDER_ID,  
TIMESTAMP, 
VALUE , 
TOTAL
INTO #Temp
FROM YourTable


SELECT ORDER_ID,  
TIMESTAMP,
(SELECT COUNT(*) FROM #Temp WHERE ORDER_ID=t.ORDER_ID AND ID<t.ID) + 1 AS INSTALLMENT, 
VALUE , 
TOTAL
FROM #Temp t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources