Approver query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Approver query

Author

Topic

ccbuilder
Starting Member

17 Posts

Posted - 2012-09-17 : 23:37:05

I have a question similar to this [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=178040[/url] post but with different criteria and cannot get it to work :(

My table structure is as follows:
aRowID int auto pk
TID int not null fk
Seq int not null
UID varchar(30) not null
DTS_Assigned datetime not null
DTS_Approved datetime not null

How can I get a query of items awaiting approval for the following janedoe using the following data:
9 100 0 jdoe '9/17/2012 9:07:56 AM' '9/17/2012 1:51:59 PM'
15 100 1 janedoe '9/17/2012 1:51:59 PM' NULL
21 120 0 janie NULL NULL
25 120 1 janedoe NULL NULL

the query should pick up jane doe at TID 100 I cannot get a query to work. Please note that seq items are sequential so that janedoe may be found either at seq 1 or 2 or X

I hope this makes sense, thanks in advance for the help.

ccbuilder
Starting Member

17 Posts

Posted - 2012-09-21 : 01:10:46

What about using a query to get all items where approver is located?
Select * from Approvals
Where UID='janedoe'

This will give the rows where janedoe is present
This query will give:
15 100 1 janedoe '9/17/2012 1:51:59 PM' NULL
25 120 1 janedoe NULL NULL

Based on the results, only janedoe at TID 100 should be returned

At this point how about cursors to traverse thru the records returned by the above query?

I just don't know how to do it?
Your help is greatly appreciated.

ccbuilder
Starting Member

17 Posts

Posted - 2012-09-22 : 00:29:12

I think it's almost close, i currently have:
SELECT DISTINCT t1.aRowID, t1.Seq, t1.TID, t1.UID, t1.RowStatus
FROM Assignments t1
CROSS APPLY(SELECT DTS_Approved
FROM Approvals
WHERE TID = t1.TID
AND aRowID < t1.aRowID)t2
WHERE t2.DTS_Approved IS NOT NULL
AND t1.DTS_Approved IS NULL
AND t1.UID = 'janedoe' and RowStatus='S

this gives me almost what i need except that janedoe is still able to see TID 120 even though is not her turn to approve :(

21 120 0 janie NULL NULL
25 120 1 janedoe NULL NULL

how can I modify this query to that she is only able to see TID when janie has approved?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources