Results 1 to 8 of 8

Thread: Status Array

  1. #1
    Join Date
    Jun 2008
    Location
    Leyland in NW England, UK
    Posts
    19

    Question Unanswered: Status Array

    Can anyone help me out with a bit of code?

    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'
    e.g.
    WOStatus - '37','38','20' or '37','38','02' or '37,'38','03'

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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.
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2008
    Location
    Leyland in NW England, UK
    Posts
    19
    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.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Perhaps this will help:
    Code:
    SELECT unique_id
    FROM   your_table
    WHERE  wostatus IN ('37','38','20','02','03')
    GROUP
        BY unique_id
    HAVING Count(*) = 5
    This essentially gives you the unique_ids for those records that have all 5 wostatus's...

    Or have I missed target here?
    George
    Home | Blog

  5. #5
    Join Date
    Jun 2008
    Location
    Leyland in NW England, UK
    Posts
    19
    Trouble is I only want the records where '20','02','03' occur after '37','38'
    e.g. '37','38','20'
    or '37','38','02'
    or '37','38','03'

    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.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    To me, these look like random sets of string values that you are putting in some sort of order... What defines this order (e.g. date record created)?
    George
    Home | Blog

  7. #7
    Join Date
    Jun 2008
    Location
    Leyland in NW England, UK
    Posts
    19
    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

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What RDBMS are you using btw?
    Quote Originally Posted by Sugsy
    after being at status 37
    This is the issue - we need to define exactly what makes one status occur after another. We need some kind of ORDER BY clause...
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •