INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Adding version numbers to orders

Adding version numbers to orders

(OP)
Here's what I'm looking to accomplish:

I have a set of data that we receive from a 3rd party application. The way the data is sent to us is in a log format where each record of the table is a change that's been made to a contract. And what we need to do is add version numbers to each contract so that we can see how many different revisions are made and can tell the difference between early revisions and later ones.

Here's an example of what the table/data looks like:

ContractNo Name StatusDesc UserName UpdateDateTime
11363708 TN MOC Revised to Traffic kcotonio 11:37.0
11363708 TN MOC Traffic Updates Accepted tmarks 33:10.7
11369136 TN MOC Revised to Traffic sbowen 34:57.4
11369136 TN MOC Traffic Updates Accepted vwarren 15:38.7
11369937 Birmingham-CLA Revised to Traffic sareyes 54:46.3
11369937 Birmingham-CLA Rev Accepted by Traffic tmarks 11:24.8
11369937 Birmingham-CLA Traffic Updates Accepted sareyes 53:37.0
11369937 Birmingham-CLA Revised to Traffic sareyes 54:00.8
11370534 Birmingham-CLA Revised to Traffic sareyes 14:54.3
11370534 Birmingham-CLA Rev Accepted by Traffic tmarks 28:41.0
11370534 Birmingham-CLA Traffic Updates Accepted sareyes 29:42.1
11370534 Birmingham-CLA Revised to Traffic sareyes 38:50.5
11370576 Birmingham-CLA Revised to Traffic sareyes 02:46.0
11370576 Birmingham-CLA Rev Accepted by Traffic tmarks 20:59.1
11370576 Birmingham-CLA Traffic Updates Accepted sareyes 53:43.2
11371192 Birmingham-CLA Revised to Traffic sareyes 53:29.4
11371192 Birmingham-CLA Rev Accepted by Traffic tmarks 58:34.7
11371192 Birmingham-CLA Traffic Updates Accepted sareyes 18:07.3
11371333 Birmingham-CLA Traffic Updates Accepted tmarks 42:44.6
11375400 TN MOC Revised to Traffic sbowen 41:41.7
11375400 TN MOC Traffic Updates Accepted vwarren 26:09.2
11375400 TN MOC Revised to Traffic sbowen 18:35.4
11375400 TN MOC Rev Accepted by Traffic vwarren 24:08.5
11375400 TN MOC Traffic Updates Accepted sbowen 27:30.0
11375400 TN MOC Revised to Traffic sbowen 16:03.4
11375400 TN MOC Traffic Updates Accepted vwarren 51:30.7


Now what we want to do is add a column for version number and then add a version number to each record listed and increment that number up 1 every time it comes across a record with the description "Revised to Traffic". And then every time it comes across a new contract number the version number goes back down to 1.

So at this point I would usually include some bit of code to show you where I've started but to be honest, I'm not even sure where to start. I imagine that there's some function or query that would get me what I want but I haven't been able to find anything online that works.

Any suggestions that you guys may have would be greatly appreciated.

Travis
www.apexsystemsinc.com

RE: Adding version numbers to orders

select ContractNo ,count(*) version
from log_table
where StatusDesc ='Revised to Traffic'
group by ContractNo

will give the amount of revision

try
update contract inner join (select ContractNo ,count(*) versions from log_table where StatusDesc ='Revised to Traffic' group by ContractNo ) log on log.ContractNo = contract.ContractNo

set version =versions

RE: Adding version numbers to orders

(OP)
PWise,
Thanks for the suggestion. I was able to get the first part to work but can you explain this part a little more?

"update contract inner join (select ContractNo ,count(*) versions from log_table where StatusDesc ='Revised to Traffic' group by ContractNo ) log on log.ContractNo = contract.ContractNo

set version =versions"

I keep trying to get the syntax to work but I'm running into errors all over the place.

Travis
Charter Media

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close