SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Approver query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ccbuilder
Starting Member

USA
17 Posts

Posted - 09/17/2012 :  23:37:05  Show Profile  Reply with Quote
I have a question similar to this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=178040 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

USA
17 Posts

Posted - 09/21/2012 :  01:10:46  Show Profile  Reply with Quote
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.
Go to Top of Page

ccbuilder
Starting Member

USA
17 Posts

Posted - 09/22/2012 :  00:29:12  Show Profile  Reply with Quote
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?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000