-
Query Help - Sequential numbers
I have a query which returns information about transactions similar to this:
Select account, trans_code, quantity
from ledger_table
This returns something like:
acct trans_code quantity
----- ---------- ------------
2 2 1000
2 3 500
2 3 300
3 2 100
3 2 500
etc.
What I need to do is add a lot number for each acct/trans code type. This is merely a sequential number for the transaction. This changes the output as follows:
acct trans_code quantity lot
----- ---------- ------------ ---
2 2 1000 1
2 3 500 1
2 3 300 2
3 2 100 1
3 2 500 2
3 3 1000 3
3 5 200 1
etc.
The lot number is reset for each grouping.
Does anyone have a way to do this outside of a cursor or temp table?
TIA, any help greatly appreciated.
-
Query Help - Sequential numbers (reply)
This is a very interesting problem. I will copy it and work on it off line. However here is my basic suggestion.
Use a column that uniquely identifies a row with a given acct and trans_code. I'm not sure if quantity is appropriate here.
Count how many rows with the same acct and trans_code have a value in this column which is equal or less to the that row, this is your lot number
Eg
2 2 1000 1 There is 1 row with 2,2 and <=1000
2 3 500 2 There are 2 rows with 2,3 and <=500
2 3 300 1 There is 1 row with 2,3 and <=300
3 2 100 1 There is 1 row with 3,2 and <=100
3 2 500 2 There are 2 rows with 3,2 and <=500
------------
Steve Jones at 4/27/99 11:46:18 AM
I have a query which returns information about transactions similar to this:
Select account, trans_code, quantity
from ledger_table
This returns something like:
acct trans_code quantity
----- ---------- ------------
2 2 1000
2 3 500
2 3 300
3 2 100
3 2 500
etc.
What I need to do is add a lot number for each acct/trans code type. This is merely a sequential number for the transaction. This changes the output as follows:
acct trans_code quantity lot
----- ---------- ------------ ---
2 2 1000 1
2 3 500 1
2 3 300 2
3 2 100 1
3 2 500 2
3 3 1000 3
3 5 200 1
etc.
The lot number is reset for each grouping.
Does anyone have a way to do this outside of a cursor or temp table?
TIA, any help greatly appreciated.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|