January 29, 2009 at 11:17 am
I have a routing table with a route ID, sort order, and employeeID, and route status. Users of the system need to see route info only where everyone before them has approved the route (Status = 1 0r 6) or where they are the first person in the route sortorder =1 and Status = NULL
any help would be appreciated
Chuck
CREATE TABLE [RequestRoute](
[RequestID_FK] [uniqueidentifier] NOT NULL,
[SortOrder] [int] NOT NULL,
[ReviewerEmployeeId] [char](5) NULL,
[RuleCollectionType] [int] NOT NULL,
[Status] [int] NULL
) ON [PRIMARY]
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{113BC94B-4A9B-4DC4-A38C-8780AC7E1CCB}','1','16975','2','6')
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{113BC94B-4A9B-4DC4-A38C-8780AC7E1CCB}','2','16312','2',NULL)
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{113BC94B-4A9B-4DC4-A38C-8780AC7E1CCB}','3','08784','2',NULL)
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{55EA2EC5-2D74-4E4D-B552-9D35B0F57466}','1','16312','2',NULL)
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{55EA2EC5-2D74-4E4D-B552-9D35B0F57466}','2','08784','2',NULL)
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{55EA2EC5-2D74-4E4D-B552-9D35B0F57466}','3','16975','2',NULL)
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{2194BD87-248C-44B9-A4DF-0048F8CBBB0D}','1','16975','2','1')
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{2194BD87-248C-44B9-A4DF-0048F8CBBB0D}','2','08784','2',NULL)
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{2194BD87-248C-44B9-A4DF-0048F8CBBB0D}','3','16312','2',NULL)
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{0C38444D-A26D-4115-8FB2-00AD5F4744C9}','1','13713','1','2')
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{64B03572-136D-4A8D-9F75-00FEC20B70C7}','1','13713','2','2')
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{70B11C27-1A1A-4933-8978-013112E34AB3}','1','13713','2','1')
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{881BD75E-9045-4E01-97B4-013EAC7E2595}','1','16975','2','1')
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{881BD75E-9045-4E01-97B4-013EAC7E2595}','2','13713','2','2')
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{663C0E6E-9CF5-4B06-BE99-01F1B91B2F53}','1','16975','2','1')
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{663C0E6E-9CF5-4B06-BE99-01F1B91B2F53}','2','13352','2','1')
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{663C0E6E-9CF5-4B06-BE99-01F1B91B2F53}','3','16975','2',NULL)
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{E3491A26-701D-4906-A3D4-02470ACE8EE9}','1','13713','0','2')
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{F6DAB82F-1771-48D5-9ED6-024AFA8B8FB6}','1','13713','2','2')
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{F6DAB82F-1771-48D5-9ED6-024AFA8B8FB6}','2','16975','2','1')
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{B09F527D-C993-4D9E-8B65-02926F525F01}','1','16975','2','1')
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{B09F527D-C993-4D9E-8B65-02926F525F01}','2','08784','2',NULL)
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{B09F527D-C993-4D9E-8B65-02926F525F01}','3','16312','2',NULL)
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{7098406A-7C6B-41C7-85B1-02D4A5654550}','1','13713','2','2')
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{FF38B6DA-0AD4-4BF1-8C6B-030BC77E5BD5}','1','16975','2','1')
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{FF38B6DA-0AD4-4BF1-8C6B-030BC77E5BD5}','2','08784','2',NULL)
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{FF38B6DA-0AD4-4BF1-8C6B-030BC77E5BD5}','3','16312','2',NULL)
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{385C2646-0B17-4007-A159-0377D12F95FE}','1','16975','2','1')
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{385C2646-0B17-4007-A159-0377D12F95FE}','2','08784','2',NULL)
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{385C2646-0B17-4007-A159-0377D12F95FE}','3','16312','2',NULL)
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{67F6E513-C6A1-45F8-890A-04085D6D432F}','1','13713','1','2')
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{67F6E513-C6A1-45F8-890A-04085D6D432F}','2','16975','1','1')
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{BE971490-7997-400A-923B-0426F4A5FCBA}','1','13713','2','2')
INSERT INTO dbo.RequestRoute (RequestID_FK,SortOrder,ReviewerEmployeeId,RuleCollectionType,Status) VALUES('{EF8924A1-AA33-4E98-ABD4-0456FB1AFB94}','1','13713','2','2')
January 29, 2009 at 11:25 am
Sounds to me like you could use a "Where Not Exists" to accomplish that.
Something like:
select *
from dbo.RequestRoute
where ReviewerEmployeeId = @EmployeeID_in
and not exists
(select *
from dbo.RequestRoute R2
where R2.RequestID_FK = RequestRoute.RequestID_FK
and R2.SortOrder < RequestRoute.SortOrder
and R2.Status not in (1,6))
Assuming you have an input parameter on the query that has the EmployeeID in it.
Would that do what you need?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 29, 2009 at 11:31 am
Let me give this a try thank you. I'm not sure it deals with the possibility that @EmpID_In has a sortorder of 1 and a status of Null
Cheers!
Chuck
January 29, 2009 at 11:38 am
You can add those to the Where clause pretty easily.
If the sort order isn't 1, and all prior people have approved it, should the status be null? If so, just add "and Status is null" to the Where clause and you'll have it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 29, 2009 at 1:39 pm
Thank yo again!
I'll give it a go,
chuck
January 30, 2009 at 11:49 am
It's still returning incorrect rows.
I'm getting rows back where empin = 16975
this routeid should not show up in the resultset
RouteUD SortOrder EmpNum Status
1a0e865 1 122761
1a0e865 2 16975 1
1a0e865 3 16975NULL
Thanks
CHuck
January 30, 2009 at 2:30 pm
I'm not clear on why that one should not show. The prior steps have status 1 and the current step has status null. Per what I'm reading in the spec, that means it should show. What am I missing?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 30, 2009 at 4:27 pm
Chuck Craig (1/30/2009)
It's still returning incorrect rows.I'm getting rows back where empin = 16975
this routeid should not show up in the resultset
RouteUD SortOrder EmpNum Status
1a0e865 1 122761
1a0e865 2 16975 1
1a0e865 3 16975NULL
Thanks
CHuck
Chuck, is the same employee supposed to show up twice? It looks like 16975 approved it at the second record but they are showing up again. Does that follow your business rules?
GSquared, I think I know what's wrong with your query (However Chuck must have separate data in his input table because the results I get do not match his results). Your subquery accounts for ANY records that that has a sort order less than the selected employee with a status of (1,6). Chuck needs ALL records less than the selected employee accounted for.
So if employee at sort order 1 & 2 have a status of 1, but employee at sort order 3 is null, and you're querying on employee at sort order 4, your query will return that Route.
Try this
declare @emp_id int
select @emp_id = 16975
select *
from dbo.RequestRoute
where ReviewerEmployeeId = @emp_id
and
(sortorder - 1 =
(select Max(sortorder)
from dbo.RequestRoute R2
where R2.RequestID_FK = RequestRoute.RequestID_FK
and R2.Status IN (1,6) ) OR
(sortorder = 1 and status is null))
I am cheating a little bit with this query. I am assuming that if the sortorder prior to the one I'm selecting has a status of (1,6), then all the ones before it also have a status of (1,6). However I'm guessing if that business rule is violated, this won't be the only part of your application having problems.
January 30, 2009 at 4:45 pm
Actually I don't know why I made it even more difficult. You can account for all previous sortorder records using Count(*) (assuming that they will always start with 1, and none of the records per this Route get deleted)
select *
from dbo.RequestRoute
where ReviewerEmployeeId = @emp_id
and
(
(sortorder - 1 =
(select Count(*)
from dbo.RequestRoute R2
where R2.RequestID_FK = RequestRoute.RequestID_FK
and RequestRoute.sortorder > r2.sortorder
and R2.Status IN (1,6)
) AND sortorder > 1
)
OR
(sortorder = 1 and status is null)
)
It may come down to what business rules you have as well as the performance of each of the queries. There are probably more efficient ways process this data, but I don't know what indexes you have or how big your dataset is.
January 30, 2009 at 4:54 pm
No you're right a person can get notified more than once in a route (1 to X). The business rule really is to route to someone more than once. Perhaps they get the doc first, change it, then it goes to a second person, they modify it, then back to you for changes and on to someone else.
Thanks for you help I'll take the script and plug it in
Cheers
Chuck
February 4, 2009 at 8:22 am
Thanks this did the trick
Cheers
Chuck
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply