Need help putting together the most efficient query

[TheSmileyCoder]

I have a document review system, in which users perform reviews of documents.

When a review is done, it gets set in a status of "Ready for Signing" reflected by a field in the table tbl_Review
tbl_Review
PK_Review (Primary Key, autonumber)
ID_Status (Long, value of 1="Ready for signing")

Furthermore I have a table tbl_QA in which I information is stored on who is supposed to sign for the review and whether its done or not.
tbl_QA
ID_Review (Long, Foreign Key of Review)
ID_User (Long, Foreign Key of User supposed to sign)
ID_SignedBy (Long, Foreign key of user who signed)
ID_Type (Long, Type of signature)

The ID_Type of signature can be:
1 - Prepared By
2 - Checked By
3 - Approved By


More then 1 person can be required to sign for a particular process, for example you could have 3 people who needs to sign off that they prepared the document.

Now to the question
I want create a form that shows all the reviews that User X has to sign off on, but has not yet done. To complicate it a bit, a user cannot sign the checked field until all prepare's has signed, and likewise it cannot be approved until all checkers has signed.

So a query to show User X all the document that are waiting for his signature. How would be the most efficient way of setting that up?

[zmbd]

TheSmileyCoder

What I'm missing here is how do you know which document requires what type of signiture? Do they all require all three? Once I have that I think I can work towards an answer for you. (although Rabbit will have it!)

-z

[zmbd]

Should have added the following question:

Why have tbl_review? If the document is ready for: "Prepared By"; "Checked By"; or "Approved By" signitures then that entry should appear in your tbl_QA at that point.

[TheSmileyCoder]

Before a review can be issued, there must be minimum of 1 signature for each field. I.e. someone must sign to indicate he/she has prepared it, and someone else (cannot be the same as the preparer, but don't worry about that. That logic is handled elsewhere) must sign for the checking. Finally the Approval is signed by someone in management.


The tbl_Review has alot more information then shown here, I only showed what is relevant for the question at hand. The tbl_Review is important since a document does not require signing until it has reached the state "Ready for Signing". Other status can be "Under Review"(Prior to signing) or "Review Submitted" (After signing is complete).

When a user marks the review as ready for signing he is forced to select minimum 1 preparer, 1 checker and 1 approver. That means 3 entries in the tbl_QA table like so for review 8429:

ID_Review  ID_User  ID_Type  ID_SignedBy
8429       246      1        Null
8429       748      2        Null
8429       15       3        Null

When user 246 signs (Click of button) the ID_SignedBy switches to the user id (246)

Now I need to present a form to the user showing all reviews waiting for his signature. In the example illustrated the review should only show up on user 246's form. (Since user 748 cannot sign the checked field until the prepared field has been signed) Once user 246 has signed, it should no longer appear on his form.

[zmbd]

Great! I was hoping that ID_Type was being used as the required signiture class and not something else...

I started with an aggregate query such as:

SELECT 
tbl_qa.QA_FK_User, 
tbl_qa.QA_FK_Review, 
tbl_qa.QA_Type, 
Count(tbl_qa.QA_FK_SignedBy) 
   AS CountOfQA_FK_SignedBy
FROM tbl_qa
GROUP BY 
 tbl_qa.QA_FK_User, 
 tbl_qa.QA_FK_Review, 
 tbl_qa.QA_Type
HAVING ((Count(tbl_qa.QA_FK_SignedBy)=0));

We can toss a parameter in there to filter down by QA_FK_User so now we only get back documents that are related to the specific user that are not signed.

I'm thinking thru the rest of the logic. I'm thinking a self join back against the tbl_qa... just don't quite have it yet.

I do this best when I'm burning things in the lab and it just so happens I have few samples to just that to.... (maniacal laughter fills the room as the MadChemist skips away to the lab).

[Rabbit]

You could do something like this.

SELECT 
   q.ID_Review,
   MIN(q.ID_Type) AS sigLevel
FROM 
   tbl_Review r INNER JOIN
   tbl_QA q ON
      r.PK_Review = q.ID_Review
WHERE
   r.ID_Status = 1 AND
   q.ID_SignedBy IS NULL
GROUP BY
   q.ID_Review

Which will return the lowest signature level still needed for each review that is ready to be signed. You can then use that to join back to tbl_QA on the ID_Review and sigLevel to get everyone that still needs to sign at the lowest signature level.

[zmbd]

Rabbit,
I am truely impressed!!!!!
The only mod I made to your query was to add a line in the WHERE clause to filter by user.
<bow to Rabbit>

I'd hit on the solution from my starting point and there's this truely elegant one from Rabbit. I think I should sit at Rabbit's feet and learn for awhile.

[Rabbit]

lol z. You pick up a few things when most of what you do all day is write queries.

[TheSmileyCoder]

@Zmbd You cannot filter on the user too early, as that might result in incorrect "signature" levels.

@Rabbit I knew you would come through for me on this one, presenting something that was simpler and more elegant then my solution which consisted of several nested queries. You always seem to turn SQL into an art. Thank you

[Rabbit]

It comes from years of practice and reading. I have read my fair share of articles about SQL algorithms and efficient SQL code.