Solved

Determining Status

Posted on 2013-01-22
11
354 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 26

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 26

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

770 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