Instead of using MAX, an alternative for a specific year

[goslincm]

I was wondering if their is a way to work with this existing query, but instead of querying MAX(auditreceiveddate) I can search instead for the value 2005 in its place?

SELECT A.LocalUnitCd, A.LocalUnitType, A.CountyCd, L.LastAuditDate, A.FiscalYear AS LastFiscalYear
FROM dbo_localUnitAudit AS A INNER JOIN [SELECT LocalUnitCd,LocalUnitType, CountyCd, MAX(auditreceiveddate) AS LastAuditDate

FROM dbo_localUnitAudit GROUP BY LocalUnitCd, LocalUnitType, CountyCd
]. AS L ON (A.LocalUnitCd = L.LocalUnitCd) AND (A.AuditReceivedDate = L.LastAuditDate) AND (A.LocalUnitType = L.LocalUnitType) AND (A.CountyCd = L.CountyCd)
WHERE (((A.LocalUnitCd)<>"100") AND ((A.LocalUnitType)="0"));

 

[lespaul]

SELECT A.LocalUnitCd, A.LocalUnitType, A.CountyCd, L.LastAuditDate, A.FiscalYear AS LastFiscalYear
FROM dbo_localUnitAudit AS A INNER JOIN [SELECT LocalUnitCd,LocalUnitType, CountyCd, auditreceiveddate

FROM dbo_localUnitAudit GROUP BY LocalUnitCd, LocalUnitType, CountyCd
]. AS L ON (A.LocalUnitCd = L.LocalUnitCd) AND (A.AuditReceivedDate = L.LastAuditDate) AND (A.LocalUnitType = L.LocalUnitType) AND (A.CountyCd = L.CountyCd)
WHERE A.LocalUnitCd<>"100" AND A.LocalUnitType="0" [b]AND Year(AuditReceiveDate) = 2005[/b];

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[lespaul]

my bad, in the wrong spot:

SELECT A.LocalUnitCd, A.LocalUnitType, A.CountyCd, L.LastAuditDate, A.FiscalYear AS LastFiscalYear
FROM dbo_localUnitAudit AS A
INNER JOIN [SELECT LocalUnitCd,LocalUnitType, CountyCd, auditreceiveddate As LastAuditDate
FROM dbo_localUnitAudit WHERE Year(AuditReceiveDate) = 2005 GROUP BY LocalUnitCd, LocalUnitType, CountyCd
]. AS L ON (A.LocalUnitCd = L.LocalUnitCd) AND (A.AuditReceivedDate = L.LastAuditDate) AND (A.LocalUnitType = L.LocalUnitType) AND (A.CountyCd = L.CountyCd)
WHERE A.LocalUnitCd<>"100" AND A.LocalUnitType="0";

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[goslincm]

Thanks so much Leslie

 

[Remou]

So, how about a *purple* thank you for Leslie?