Adding version numbers to orders 1

[newguy86]

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

http://www.apexsystemsinc.com

 

[PWise]

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

 

[newguy86]

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