By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,231 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Need help putting together the most efficient query

TheSmileyCoder
Expert Mod 100+
P: 2,321
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?
Sep 19 '12 #1
Share this Question
Share on Google+
9 Replies


zmbd
Expert Mod 5K+
P: 5,287
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
Sep 19 '12 #2

zmbd
Expert Mod 5K+
P: 5,287
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.
Sep 19 '12 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
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:
Expand|Select|Wrap|Line Numbers
  1. ID_Review  ID_User  ID_Type  ID_SignedBy
  2. 8429       246      1        Null
  3. 8429       748      2        Null
  4. 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.
Sep 19 '12 #4

zmbd
Expert Mod 5K+
P: 5,287
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. tbl_qa.QA_FK_User, 
  3. tbl_qa.QA_FK_Review, 
  4. tbl_qa.QA_Type, 
  5. Count(tbl_qa.QA_FK_SignedBy) 
  6.    AS CountOfQA_FK_SignedBy
  7. FROM tbl_qa
  8. GROUP BY 
  9.  tbl_qa.QA_FK_User, 
  10.  tbl_qa.QA_FK_Review, 
  11.  tbl_qa.QA_Type
  12. 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).
Sep 19 '12 #5

Rabbit
Expert Mod 10K+
P: 12,316
You could do something like this.
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.    q.ID_Review,
  3.    MIN(q.ID_Type) AS sigLevel
  4. FROM 
  5.    tbl_Review r INNER JOIN
  6.    tbl_QA q ON
  7.       r.PK_Review = q.ID_Review
  8. WHERE
  9.    r.ID_Status = 1 AND
  10.    q.ID_SignedBy IS NULL
  11. GROUP BY
  12.    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.
Sep 19 '12 #6

zmbd
Expert Mod 5K+
P: 5,287
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.
Sep 19 '12 #7

Rabbit
Expert Mod 10K+
P: 12,316
lol z. You pick up a few things when most of what you do all day is write queries.
Sep 19 '12 #8

TheSmileyCoder
Expert Mod 100+
P: 2,321
@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
Sep 19 '12 #9

Rabbit
Expert Mod 10K+
P: 12,316
It comes from years of practice and reading. I have read my fair share of articles about SQL algorithms and efficient SQL code.
Sep 19 '12 #10

Post your reply

Sign in to post your reply or Sign up for a free account.