Hi all,
Sorry for the lack of a descriptive title.
I'm trying to associate revision levels of SOPs with historical training records (ie, someone trained on SOP-001 on 1/1/2020 was trained to Revision 3, because Revision 3 is dated 12/31/2020, and Revision 4 is dated 1/2/2020).
So I need to use an aggregate query of some sort to select all the revisions of each SOP listed in our "Master List of Controlled Documents" table, (eg, GROUP BY SOP) but with a compound criteria: maximum revision date less than or equal to the training date in the training table.
How would one accomplish this?
Thanks in advance for any insight you kind folks can offer.
Sorry for the lack of a descriptive title.
I'm trying to associate revision levels of SOPs with historical training records (ie, someone trained on SOP-001 on 1/1/2020 was trained to Revision 3, because Revision 3 is dated 12/31/2020, and Revision 4 is dated 1/2/2020).
So I need to use an aggregate query of some sort to select all the revisions of each SOP listed in our "Master List of Controlled Documents" table, (eg, GROUP BY SOP) but with a compound criteria: maximum revision date less than or equal to the training date in the training table.
How would one accomplish this?
Thanks in advance for any insight you kind folks can offer.