Solved

Determining Status

Posted on 2013-01-22
11
360 Views
Last Modified: 2013-01-23
Hello,

My brain's a bit shot today and I need a query to help me determine the current status a Widget is in.

Here are my tables:
Status:
Status_ID   INT (PK)
Status_Order   INT

Department:
Department_ID   INT (PK)

DepartmentStatus:
Status_ID   INT (PK, FK)
Department_ID   INT (PK, FK)

WidgetStatus:
Widget_ID   INT (PK, FK)
Status_ID   INT (PK, FK)
Department_ID   INT (PK, FK)
Status_Date   DATETIME
Initialed_By   CHAR(3)

Open in new window


A status is only "attained" when each department has signed off on it.  The status with the higher order in the Status table is always the current status.

Example:
1) Widget A is created, and no deparments have signed off.  It's current status is NULL.
2)  Status 1 requires three departments to sign off.  When all three departments have timestamped and initialed that status, Widget A is now in status 1.
3)  Status 2 requires only one department to sign off.  That department does so, and now Widget A is currently in Status 2.  (Even though it also qualifies as being in status 1)

Thanks in advance,
-Torrwin
0
Comment
Question by:Torrwin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 38807731
Respectfully, we are not supposed to do your homework for you.  Grab a SQL book or site and start playing with queries on these tables ... you'll get there!  :)
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 38807770
Apologies Torrwin, I believe my comments above are based on a faulty assumption.  I recant.
0
 
LVL 13

Author Comment

by:Torrwin
ID: 38807866
No worries. ;-)
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38807882
SELECT
    ws.Widget_ID, s.Status_Order,
    ws.Department_ID, ws.Status_Date,
    ws.Initialed_By
FROM (
    SELECT
        ws.*, s.Status_Order,
        ROW_NUMBER() OVER (PARTITION BY ws.Widget_ID ORDER BY s.Status_Order DESC) AS row_num
    FROM dbo.WidgetStatus ws
    INNER JOIN dbo.Status s ON
        s.Status_ID = ws.Status_ID
) AS derived
WHERE
    row_num = 1
0
 
LVL 13

Author Comment

by:Torrwin
ID: 38807944
That's a good idea, but it doesn't quite work.  It returns the latest/most recent status update, but doesn't take in to account whether all applicable departments have actually signed off on a given status.  (i.e. it returns false statuses)

To knightEknight's point, on the surface this looks like an easy problem, but once you start to work through it you realize it's much harder than it seems.  What I need could be accomplished by hard-coding CASE statements like so, but to make the statuses, departments, and combinations thereof dynamic is much more complex.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38808041
I was going by what you stated in your original q:

>> The status with the higher order in the Status table is always the current status. <<
...
>>  When all three departments have timestamped and initialed that status, Widget A is now in status 1. <<


You stated a Widget changed status (only) after all three/nnn depts had initialed it, so that's what I went by.

I don't have first-hand knowledge of your tables; I can go only by what you tell me.
0
 
LVL 13

Author Comment

by:Torrwin
ID: 38808085
Hi Scott,

I'm not sure what the disconnect was here.  I wasn't criticizing, I was just trying to give enough information to help us both further along.

You stated a Widget changed status (only) after all three/nnn depts had initialed it

That's correct.  What i was trying to say was that the SQL above doesn't take that into account.  The SQL above reports the status changed after a single department initials it.

I don't have first-hand knowledge of your tables
 The above tables are the only tables.
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 38808136
The above tables are the only tables.
what about the Widget table, you have a foriegn key to it?

Try this query:
;WITH CTE AS (
SELECT COUNT(WS.Department_ID) OVER (PARTITION BY W.Widget_ID, WS.Status_ID) DeptCnt, DeptNeeded.DeptNeededCnt, W.Widget_ID, DeptNeeded.Status_ID,
        ROW_NUMBER() OVER (PARTITION BY ws.Widget_ID ORDER BY s.Status_Order DESC) AS row_num
FROM dbo.Widget W
LEFT OUTER JOIN dbo.WidgetStatus WS 
INNER JOIN dbo.Status S ON WS.Status_ID = S.Status_ID ON W.Widget_ID = WS.Widget_ID
LEFT OUTER JOIN (SELECT DS.Status_ID, COUNT(DS.Department_ID) DeptNeededCnt FROM dbo.DepartmentStatus DS GROUP BY DS.Status_ID) DeptNeeded ON S.Status_ID = DeptNeeded.Status_ID
)
SELECT CTE.Widget_ID, CASE WHEN CTE.DeptCnt = CTE.DeptNeededCnt THEN CTE.Status_ID ELSE CTE.Status_ID - 1 END Status_ID
FROM CTE
WHERE CTE.row_num = 1
ORDER BY CTE.Widget_ID

Open in new window

Gave these resultsResultsComplete setup for testing purposes, uncomment out statements as needed:
/*Status:
Status_ID   INT (PK)
Status_Order   INT

Department:
Department_ID   INT (PK)

DepartmentStatus:
Status_ID   INT (PK, FK)
Department_ID   INT (PK, FK)

WidgetStatus:
Widget_ID   INT (PK, FK)
Status_ID   INT (PK, FK)
Department_ID   INT (PK, FK)
Status_Date   DATETIME
Initialed_By   CHAR(3)
*/

--CREATE TABLE Status (
--Status_ID   INT PRIMARY KEY,
--Status_Order   INT);

--CREATE TABLE Department (
--Department_ID   INT PRIMARY KEY);

--CREATE TABLE DepartmentStatus (
--Status_ID   INT FOREIGN KEY REFERENCES Status (Status_ID),
--Department_ID   INT FOREIGN KEY REFERENCES Department(Department_ID),
--CONSTRAINT PK_DepartmentStatus PRIMARY KEY CLUSTERED (Status_ID, Department_ID) );

--CREATE TABLE Widget (
--Widget_ID   INT PRIMARY KEY);


--CREATE TABLE WidgetStatus (
--Widget_ID   INT FOREIGN KEY REFERENCES Widget(Widget_ID),
--Status_ID   INT FOREIGN KEY REFERENCES Status(Status_ID),
--Department_ID   INT FOREIGN KEY REFERENCES Department(Department_ID),
--Status_Date   DATETIME,
--Initialed_By   CHAR(3),
--CONSTRAINT PK_WidgetStatus PRIMARY KEY CLUSTERED (Widget_ID, Status_ID, Department_ID) );

--INSERT INTO dbo.Status	( Status_ID, Status_Order ) VALUES	( 1, 1 ), (2,2), (3,3);
--INSERT INTO dbo.Department ( Department_ID ) VALUES	( 10 ), (20), (30), (40);
--INSERT INTO dbo.DepartmentStatus ( Status_ID, Department_ID )
--VALUES	( 1, 10 ),
--		( 1, 20 ),
--		( 1, 30 ),
--		( 2, 20 ),
--		( 3, 40 )

--INSERT INTO dbo.Widget ( Widget_ID ) VALUES 	( 100 ), (200), (300), (400)
--INSERT INTO dbo.WidgetStatus
--		( Widget_ID,
--		  Status_ID,
--		  Department_ID,
--		  Status_Date,
--		  Initialed_By
--		)
--VALUES	( 100, 1, 10, '2013-01-22 23:57:58', 'WHO' ),
--		( 100, 1, 20, '2013-01-22 23:57:58', 'WHO' ),
--		( 100, 1, 30, '2013-01-22 23:57:58', 'WHO' ),
--		( 100, 2, 20, '2013-01-22 23:57:58', 'WHO' ),
--		( 200, 1, 10, '2013-01-22 23:57:58', 'WHO' ),
--		( 200, 1, 20, '2013-01-22 23:57:58', 'WHO' ),
--		( 200, 1, 30, '2013-01-22 23:57:58', 'WHO' ),
--		( 300, 1, 10, '2013-01-22 23:57:58', 'WHO' ),
--		( 300, 1, 20, '2013-01-22 23:57:58', 'WHO' )



;WITH CTE AS (
SELECT COUNT(WS.Department_ID) OVER (PARTITION BY W.Widget_ID, WS.Status_ID) DeptCnt, DeptNeeded.DeptNeededCnt, W.Widget_ID, DeptNeeded.Status_ID,
        ROW_NUMBER() OVER (PARTITION BY ws.Widget_ID ORDER BY s.Status_Order DESC) AS row_num
FROM dbo.Widget W
LEFT OUTER JOIN dbo.WidgetStatus WS 
INNER JOIN dbo.Status S ON WS.Status_ID = S.Status_ID ON W.Widget_ID = WS.Widget_ID
LEFT OUTER JOIN (SELECT DS.Status_ID, COUNT(DS.Department_ID) DeptNeededCnt FROM dbo.DepartmentStatus DS GROUP BY DS.Status_ID) DeptNeeded ON S.Status_ID = DeptNeeded.Status_ID
)
SELECT CTE.Widget_ID, CASE WHEN CTE.DeptCnt = CTE.DeptNeededCnt THEN CTE.Status_ID ELSE CTE.Status_ID - 1 END Status_ID
FROM CTE
WHERE CTE.row_num = 1
ORDER BY CTE.Widget_ID

--DROP TABLE dbo.WidgetStatus;
--DROP TABLE dbo.Widget;
--DROP TABLE dbo.DepartmentStatus;
--DROP TABLE dbo.Department;
--DROP TABLE dbo.Status;

Open in new window

0
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 500 total points
ID: 38808164
Or this one does not go to the elusive Widget table and therefore will not list a Widget unless it has at least one status record in the WidgetStatus table.
;WITH CTE AS (
SELECT COUNT(WS.Department_ID) OVER (PARTITION BY WS.Widget_ID, WS.Status_ID) DeptCnt, DeptNeeded.DeptNeededCnt, WS.Widget_ID, DeptNeeded.Status_ID,
        ROW_NUMBER() OVER (PARTITION BY ws.Widget_ID ORDER BY s.Status_Order DESC) AS row_num
FROM dbo.WidgetStatus WS 
INNER JOIN dbo.Status S ON WS.Status_ID = S.Status_ID --ON W.Widget_ID = WS.Widget_ID
LEFT OUTER JOIN (SELECT DS.Status_ID, COUNT(DS.Department_ID) DeptNeededCnt FROM dbo.DepartmentStatus DS GROUP BY DS.Status_ID) DeptNeeded ON S.Status_ID = DeptNeeded.Status_ID
)
SELECT CTE.Widget_ID, CASE WHEN CTE.DeptCnt = CTE.DeptNeededCnt THEN CTE.Status_ID ELSE CTE.Status_ID - 1 END Status_ID
FROM CTE
WHERE CTE.row_num = 1
ORDER BY CTE.Widget_ID

Open in new window

The only difference in the results is it does not have the row for Widget_ID 400 above.

Also, in both examples I displayed the row(s) where the status 1 was not complete yet but displayed 0 for the Status_ID, that can be tweaked if that is not what you need.
0
 
LVL 13

Author Closing Comment

by:Torrwin
ID: 38808544
Spot on, thanks!
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38810268
>> You stated a Widget changed status (only) after all three/nnn depts had initialed it

That's correct.  What i was trying to say was that the SQL above doesn't take that into account.  The SQL above reports the status changed after a single department initials it.

<<

That's a SELF CONTRADICTION!

You state:

1) the status changes ONLY after ALL depts have initialed it

then you state:

2) "reports the status changed after a single dept initials it"

IMPOSSIBLE unless you've misstated your procedure: the status WON'T be changed until ALL depts initial it.
0

Featured Post

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question