Solved

Determining Status

Posted on 2013-01-22
11
350 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
 
LVL 69

Expert Comment

by:ScottPletcher
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 69

Expert Comment

by:ScottPletcher
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:ScottPletcher
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

762 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now