July 4, 2015 at 2:00 am
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
July 4, 2015 at 11:28 am
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
July 4, 2015 at 12:40 pm
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
July 4, 2015 at 8:28 pm
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;
July 5, 2015 at 1:10 am
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');
July 6, 2015 at 10:25 am
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.
July 18, 2015 at 8:26 pm
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