I have three tables in my database:
tbl_documents
DocumentID
DocumentName
DocumentFilename
AuthorID
tbl_authors
AuthorID
AuthorName
AuthorEmail
tbl_updates
UpdateID
DocumentID
UpdateDate (The date it was last updated)
NextUpdate (The date it needs to be updated next)
AuthorID (This is only used to record the person who made the update)
Each month, I send an automatically generated email is sent to Authors letting them know which of their documents are due for an update in the next 30 days.
Here is my current query:
SELECT doc.DocID, doc.DocName, doc.DocFilename, updt.NextUpdate, athr.AuthorID, athr.AuthorName, athr.AuthorEmail
FROM tbl_documents AS doc
INNER JOIN tbl_updates AS updt
USING (DocID)
INNER JOIN tbl_authors AS athr
ON doc.AuthorID = athr.AuthorID
WHERE doc.DocID IN
[INDENT](SELECT updt.DocID
FROM tbl_updates AS updt
WHERE updt.NextUpdate < (30 days from now)
AND updt.NextUpdate > (today)
ORDER BY updt.NextUpdate DESC)[/INDENT]
ORDER BY athr.AuthorID ASC
My dates are tracked with UNIX timestamps so where it says (30 days from now) and (today), those are the appropriate timestamps.
This query works fine if the document has only one record in tbl_updates with a NextUpdate date in the next 30 days. However, some documents are updated more frequently and will show up for each record from tbl_update with a NextUpdate date in the next 30 days.
For instance, “Policy A” is set to update monthly. Each time the document is updated, the value in the NextUpdate field will default to a month from whatever day it is being updated (Updated on Jun 15, NextUpdate = July 15). It was updated 4 times in April (4/5, 4/10, 4/15, 4/20) so there are four records on tbl_updates where the NextUpdate date is within the month of May (5/5, 5/10, 5/15, 5/20). I only want it to show the most recent record from tbl_update (the one from the 20th).
I do reasonably well with SQL but I am not a guru. I hope someone out there can shed some light on what I am missing.
Thanks,
-B