Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

Determining Status

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
Torrwin
Asked:
Torrwin
  • 4
  • 3
  • 2
  • +1
1 Solution
 
knightEknightCommented:
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
 
knightEknightCommented:
Apologies Torrwin, I believe my comments above are based on a faulty assumption.  I recant.
0
 
TorrwinAuthor Commented:
No worries. ;-)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Scott PletcherSenior DBACommented:
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
 
TorrwinAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
TorrwinAuthor Commented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
TorrwinAuthor Commented:
Spot on, thanks!
0
 
Scott PletcherSenior DBACommented:
>> 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now