Help with an update query

  • I hope someone can point me in the right direction. I can solve this using a stored procedure and a cursor but i suspect that there is a way to do this in a straight SQL query.

    I am using SQL SERVER 2012

    I have a table that stores data about the status of steps in a manufacturing process.

    Some of these steps are grouped and the overall status of the group has to be calculated.

    A group can be made up of between 1 and 5 steps.

    This is the structure of the Data table. I've removed columns that are not appropriate for this problem.

    ID,Stepnumber,Stepgroup,Stepstatus,Groupstatus

    This is a sample of the data in the main process table.

    I have the status of each step in the group but i need to calculate the value for the combined group status. This group status is a single value that is dependant upon a priority list of the statuses in the status table.

    123,Step1,Group1,Approved,''

    234,Step2,Group1,Complete,''

    345,Step3,Group1,Incomplete,''

    456,Step4,Group2,Bypassed,''

    567,Step5,Group2,Approved,''

    678,Step6,Group3,Approved,''

    789,Step7,Group3,Approved,''

    890,Step8,Group4,Omitted,''

    901,Step8,Group4,Completed,''

    ---------------------------------------

    This is the end result that I require. The group status has to be the status with the lowest value in the priority table

    123,Step1,Group1,Approved,Approved

    234,Step2,Group1,Complete,Approved

    345,Step3,Group1,Incomplete,Approved

    456,Step4,Group2,Bypassed,Bypassed

    567,Step5,Group2,Approved,Bypassed

    678,Step6,Group3,Approved,Approved

    789,Step7,Group3,Approved,Approved

    890,Step8,Group4,Omitted,Omitted

    901,Step8,Group4,Completed,Omitted

    -------------------------------------

    StatusTable

    Bypassed10

    Omitted20

    Approved30

    Complete40

    Incomplete 50

    ''50 These records seem to be a defect in the data and we are working on a data resolution

    NULL50

  • We will be much more willing to help you if you script create table statements and inserts for us that set the data up the way you show it. That way we can just write a statement show you how to do what you desire.

    Also, putting the full strings in for stuff such as status codes is VERY inefficient from a storage and processing perspective!!

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • without set up scripts of r create table / insert data....here is a guess

    WITH cte_st

    AS (

    SELECT

    S.GroupID

    , MIN(ST.StatusID) AS minst

    FROM StepTable AS S INNER JOIN

    StatusTable AS ST ON S.StatusName = ST.StatusName

    GROUP BY

    S.GroupID)

    SELECT

    S.ID

    , S.StepID

    , S.GroupID

    , S.StatusName

    , ST.StatusName AS NewStatus

    FROM StepTable AS S INNER JOIN

    cte_st ON S.GroupID = cte_st.GroupID

    INNER JOIN

    StatusTable AS ST ON cte_st.minst = ST.StatusID

    ORDER BY

    S.ID;

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Kevin

    Thanks for responding

    i take your point about providing some data creation scripts.

    Please find my attempt below.

    About your other comments. I can understand the hit on storage however I am intrigued about the performance hit.

    if i were to use a code for the status, every time i insert or read the code i would have to have a join to another lookup table to receive from or provide the response to the user.

    Is there much of a performance hit if a value is a 10 character string as against a 1 character string or an int.

    The issue i have here is that the data is inserted and retrieved by a 3rd party interpreted application. its much faster for the user interface to receive the data that has to be displayed.

    Thanks again

    Peter

    create table mydata (

    ID varchar(10),

    Stepnumber varchar(10),

    Stepgroup varchar(10),

    Stepstatus varchar(10),

    Groupstatus varchar(10)

    );

    insert into mydata (ID,Stepnumber,Stepgroup,Stepstatus,Groupstatus) values ('123','Step1','Group1','Approved','');

    insert into mydata (ID,Stepnumber,Stepgroup,Stepstatus,Groupstatus) values ('234','Step2','Group1','Complete','');

    insert into mydata (ID,Stepnumber,Stepgroup,Stepstatus,Groupstatus) values ('345','Step3','Group1','Incomplete','');

    insert into mydata (ID,Stepnumber,Stepgroup,Stepstatus,Groupstatus) values ('456','Step4','Group2','Bypassed','');

    insert into mydata (ID,Stepnumber,Stepgroup,Stepstatus,Groupstatus) values ('567','Step5','Group2','Approved','');

    insert into mydata (ID,Stepnumber,Stepgroup,Stepstatus,Groupstatus) values ('678','Step6','Group3','Approved','');

    insert into mydata (ID,Stepnumber,Stepgroup,Stepstatus,Groupstatus) values ('789','Step7','Group3','Approved','');

    insert into mydata (ID,Stepnumber,Stepgroup,Stepstatus,Groupstatus) values ('890','Step8','Group4','Omitted','');

    insert into mydata (ID,Stepnumber,Stepgroup,Stepstatus,Groupstatus) values ('901','Step8','Group4','Completed','');

    select * from mydata order by ID;

    create table statustable (

    statustring varchar(10),

    statuspriority int

    );

    insert into statustable (statustring,statuspriority) values ('Bypassed', 10);

    insert into statustable (statustring,statuspriority) values ('Omitted',20);

    insert into statustable (statustring,statuspriority) values ('Approved', 30);

    insert into statustable (statustring,statuspriority) values ('Complete', 40);

    insert into statustable (statustring,statuspriority) values ('Incomplete', 50);

    insert into statustable (statustring,statuspriority) values ('',50 );

    insert into statustable (statustring,statuspriority) values (NULL,50);

    select * from statustable order by statuspriority;

  • Many thanks J Livingston.

    Thanks also for introducing me to the WITH statement.

    I've utilised your code into my application and it seems to be doing the trick.

    I had to do a bit of research to figure out what the WITH statement was doing but it was research that was well worth it.

    I'm sure that I will find a use for this feature very soon.

    Again thankyou for your help.(':-P');

  • As you said you're looking for an update query, I thought on posting an answer using some 2012 capabilities. Along with the use of updatable CTE's which work under the same rules as updatable views.

    WITH updtCTE AS(

    select d.*, FIRST_VALUE(d.Stepstatus) OVER(PARTITION BY d.Stepgroup ORDER BY ISNULL( s.statuspriority,50)) HigherStatus

    from mydata d

    LEFT

    JOIN statustable s ON d.Stepstatus = s.statustring

    )

    UPDATE updtCTE SET

    Groupstatus = HigherStatus;

    SELECT * FROM mydata ORDER BY ID;

    Feel free to ask any questions about anything that you don't understand and comment your final code accordingly.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Firstly let me apologise for not responding to your response earlier. Life has been quite frenetic around here lately.

    Your response was exactly what i needed. It your code that I have finally incorporated into my application. It updates about 40K rows in a second or two.

    Its part of a manufacturing process system. The native application has proven to be very slow in gathering data from a bunch of tables and generating a report. We are now throwing some parameters at the sql server and using a stored procedure to populate a temporary reporting table. We then just report on the one table.

    It has cut down the reporting time from over a minute to 3-4 seconds.

    Your query was the final brick in the wall.

    Once again many thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply