Query Criteria Where Date = Max Date - 3 months

Davisro
Davisro used Ask the Experts™
on
I currently have quarterly data for employees where PerEndDt represents the end date for each quarter

The following Sql queries the current quarter. How can I modify it to return the prior quarter?
SELECT qEmplByMth.Clock, qEmplByMth.EmployeeName, qEmplByMth.Program, qEmplByMth.PerEndDt
FROM qEmplByMth
WHERE PerEndDt IN(SELECT MAX(PerEndDt ) FROM qEmplByMth)
ORDER BY qEmplByMth.EmployeeName, qEmplByMth.PerEndDt;
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
WHERE PerEndDt IN(SELECT MIN(PerEndDt) FROM (SELECT TOP (2) PerEndDt FROM qEmplByMth ORDER BY PerEndDt DESC))
Top Expert 2016
Commented:
try this

SELECT qEmplByMth.Clock, qEmplByMth.EmployeeName, qEmplByMth.Program, qEmplByMth.PerEndDt
FROM qEmplByMth
WHERE PerEndDt IN (SELECT TOP 1 [PerEndDt] From qEmplByMth Where [PerEndDt] < DMax("PerEndDt", "qEmplByMth") Order By [PerEndDt] Desc)
ORDER BY qEmplByMth.EmployeeName, qEmplByMth.PerEndDt;
DavisroBudget Analyst

Author

Commented:
Works!!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial