Rozamunda
asked on
update with level break number
Hello, how would you do it in SQL
here is my table
Prod ID, seq , action
art1 1 'CREATE'
art1 1 'APPROVE'
art1 1 'CLOSE'
art1 1 'CREATE'
art1 1 'APPROVE'
art1 1 'CLOSE'
art1 1 'CREATE'
art1 1 'REJECT'
art1 1 'CLOSE'
I know that first action is always 'CREATE' for any PRod ID, now I want to correct sequence number for products which occur more than once, so I would want to have something like this:
Prod ID, seq , action
art1 1 'CREATE'
art1 1 'APPROVE'
art1 1 'CLOSE'
art1 2 'CREATE'
art1 2 'APPROVE'
art1 2 'CLOSE'
art1 3 'CREATE'
art1 3 'REJECT'
art1 3 'CLOSE'
here is my table
Prod ID, seq , action
art1 1 'CREATE'
art1 1 'APPROVE'
art1 1 'CLOSE'
art1 1 'CREATE'
art1 1 'APPROVE'
art1 1 'CLOSE'
art1 1 'CREATE'
art1 1 'REJECT'
art1 1 'CLOSE'
I know that first action is always 'CREATE' for any PRod ID, now I want to correct sequence number for products which occur more than once, so I would want to have something like this:
Prod ID, seq , action
art1 1 'CREATE'
art1 1 'APPROVE'
art1 1 'CLOSE'
art1 2 'CREATE'
art1 2 'APPROVE'
art1 2 'CLOSE'
art1 3 'CREATE'
art1 3 'REJECT'
art1 3 'CLOSE'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi Rozamunda,
Momi's query will properly generate a new SEQ number for all of the rows, but in terms of your data it may not be that simple.
Using just two items from your example:
Prod ID, seq , action
art1 1 'CREATE'
art1 1 'CREATE'
art1 1 'APPROVE'
art1 1 'CLOSE'
art1 1 'APPROVE'
art1 1 'CLOSE'
If the CREATE actions are resequenced, what protocol should be used to assign the correct row with an APPROVE or CLOSE action to the correct CREATE action? Is there a timestamp or other item in the row that can help to better identify the rows?
Kent
Momi's query will properly generate a new SEQ number for all of the rows, but in terms of your data it may not be that simple.
Using just two items from your example:
Prod ID, seq , action
art1 1 'CREATE'
art1 1 'CREATE'
art1 1 'APPROVE'
art1 1 'CLOSE'
art1 1 'APPROVE'
art1 1 'CLOSE'
If the CREATE actions are resequenced, what protocol should be used to assign the correct row with an APPROVE or CLOSE action to the correct CREATE action? Is there a timestamp or other item in the row that can help to better identify the rows?
Kent
ASKER
Hi Kent, action is never resequenced, CREATE is always starting the section, CLOSE finishing,
there is also a time stamp (date, time)
there is also a time stamp (date, time)
Hmm.... I'm not sure that my comment was clear, so...
Can it ever happen that for any product ID, you have two OPEN at the same time? That is, an OPEN occurs for a product, and before that event has a matching CLOSE, another OPEN is issued?
Kent
Can it ever happen that for any product ID, you have two OPEN at the same time? That is, an OPEN occurs for a product, and before that event has a matching CLOSE, another OPEN is issued?
Kent
ASKER
Kent, ultimately not, one pair (productid/seqno) should have one open and one close, all in sequence. Right now everything has only one sequence number i want to fix the data, so the above would take place
if you run my query (And adding the timestamp column to the order by part)
do you get the desired results?
if so, we will turn that into an update statement
do you get the desired results?
if so, we will turn that into an update statement
Based on what you've said, I think that I'd follow Momi's advise and resequence the "seq" values for all of the OPEN statements, then follow that up with an UPDATE of the other statements to match everything up.
Kent
Kent
ASKER