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.
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 :(
Approver query
Posted - 09/17/2012 : 23:37:05
