Solved

Determining Status

Posted on 2013-01-22
11
359 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Copy Database Wizard 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.
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

733 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