Thread 701-1351569

[goslincm]

Awhile back I was getting help with what I call some complex queries. While most of the issues were resolved at the time, one was not and its resurfaced. In reference to thread 701-1351569(last couple posts) I have a query:

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.LocalUnitType)="2"));


Which is showing me how to obtain some good information that I was able to use in determining when someone sent in their audit and when it was due. At the time I asked, how then would I be able to use this logic to then pull out the MOST RECENT FISCAL YEAR, or even a given FISCAL YEAR, like 2006. I know I have a local unit who was suppose to file an audit in Fiscal Year 2006, but did not file. I need to be able to compute WHEN the audit was due, and show that they did not file the audit for Fiscal Year 2006.

It was suggested in the previous thread I replace the INNER JOIN above with a LEFT JOIN. That does not quiet do it because I don't have an audit received date.

Can someone please help me....

 

[SkipVought]

Hi,

"That does not quiet do it because I don't have an audit received date."

...and you want help with WHAT?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[goslincm]

I have a table: LocalUnitAudit which provides me CountyCd, LocalUnitType, LocalUnitCd, FiscalYear, AuditRecDate. Currently I have information in there for a local unit which indicates the last auditrecdate was 6/1/2006 and it was for FiscalYear 2005. However, there are also two additional records since the last audit receivede date of 6/1/2006 which shows fiscal year 2006 and fiscal year 2007 entries without any AuditRecDate being completed. Based on the query above that I run, it displays my last audit received date and last fiscal year as 6/1/2006 and 2005 respectively. While that is correct in a sense, it does not help me capture and report that no audit was received for fiscal year 2006 or fiscal year 2007.