I've brought up records that have wostatus in ['37','38','20','02','03'] but I only want the records that are in a particular order i.e. the ones that have a wostatus of '37' then '38' then either '20','02' or ''03'
WOStatus - '37','38','20' or '37','38','02' or '37,'38','03'
Do you mean "those without breaks in sequence?" i.e. 37, 38, 39 is acceptable but 37, 39, 40 is not?
The order of data in a database has no inherent meaning, so if you do not have another field to order by (e.g. date the record was created) then depending on the actual question, you may have problems.
The wostatus doesn't have to run in sequential order - you could have wostatus '1' then '20' then '02' then '37' then '38' then '20' again.
Basically I'm stuck - the DB the company is using is an elderly Progress DB which is on its last legs and is being replaced by new MS SQL Server backend with Business Objects Universe front end in the new year.
The project I'm trying to get data for is a new SMS project, by which jobs are deployed to Contractors via SMS, and the developer has basically cobbled it onto Progress, making it very difficult to pull BI off.
At the moment I know if it is an SMS job because in the Wostatus table the job would show a status of 37 (Contractor sent SMS text), then 38 (awaiting Contractor response).
If the job times out then in the Wocra table it shows a code of 'SMSTO' in the reasoncode column; if the job is refused there is a code of 'SMS' in the reasoncode column. However, if the job is successful then the reasoncode column is left blank, so blank means the job was accepted (which is pretty poor way of doing things). However, a blank column obviously means we have nothing to link it to SMS - if a manual deployment was successful then that too would just be blank, so there is no way of pulling up Accepted SMS Jobs.
This is why I'm trying to find a way through using the status code as I know status 37, then 38 then 20 in wostatus column means the job was accepted. But you could get status 20 in other circumstances so I need to be able to pull from record occasions when wostatus sequence is 37, then 38 then 20.
Trouble is I only want the records where '20','02','03' occur after '37','38'
In some records they occur prior to '37','38' and I don't want these records - I only want the records where wostatus showing in that particular order. To be honest I'm not even sure that it's possible but I just thought someone might have some clever code that I could use.
The wostatus records the status of the job deployment (Company does Home repairs & insurance). Initially when a deployment needs to be made it goes to status 01. If workman deployed to manually (e.g. someone rings him up & asks him if can do job) then goes to 02. If job deployed via system (without speaking to Contractor) then goes to 03 (auto-deployment). If job deployed via SMS then goes to status 37 and while waiting for Contractor to return SMS message goes to 38. If Contractor accepts deployment then goes to status 20. If attempted deployment is not responded to within 10 mins then is classed as a timeout & company can attempt to deploy to another contractor so process can be repeated.
So status is not really defined by date or time - just by the process taken in making deployment.
All I want to see is the jobs that were deployed via SMS (status 37) and whether they were accepted (status 20), refused (status 02), or timed out (status 03) but must go to these status numbers after being at status 37 - as if company tried to deploy manually then could go to status 02, 03, 20 so if I just looked for these status numbers then data set would show jobs that were not deployed via SMS. Also comapny might have tried to deploy job either manually or by auto-deploy before deploying by SMS so again record would have 02, 03, 20 in it without it being an SMS job.
I only want jobs that went in wostatus order
either: 37, 38, 20
or 37, 38, 02
or 37, 38, 03