Trying to calculate due dates

[goslincm]

I am at a loss for doing this task, been working on it for a month now.

I need to calculate when units have their audits due. I also need to placed the units into a status, "late", "not filed", "not due", "timely"

Business Rule: If the unit population is >4000 its due annually, six months after their fiscal year ends.

Business Rule: Units can have any months for their fiscal year end month

Business Rule: If the unit population is <4000 the audit is due at a minimum every 24 months. Some can and do file it annually even though they need not.

With the help of others this is how my query looks so far, but I still cannot determine not due from not filed. I'm am just at a loss.

SELECT dbo_localUnitAudit.CountyCd, dbo_localUnitAudit.LocalUnitType, dbo_localUnitAudit.LocalUnitCd, dbo_localUnitAudit.FiscalYear, dbo_localUnitAudit.Type, dbo_localUnitAudit.Extension, dbo_localUnitAudit.AuditReceivedDate, Format(DateSerial([FiscalYear],[FiscalEndMM]+7,0),'mm/dd/yyyy') AS AuditDue, IIf([auditreceiveddate]>[auditdue],"LATE",IIf([auditreceiveddate]<=[auditdue],"TIMELY",IIf([auditreceiveddate] Is Null And DateAdd("m",GetDUeMths([population]),[auditdue] Is Null),"NOT FILED",IIf([auditdue]>Date(),"NOT DUE")))) AS AnnualAuditFiledTimely, DateAdd("m",GetDueMths([Population]),([auditdue])) AS NextAuditDue, dbo_localUnitAudit.Auditor, dbo_localUnitAudit.Notes, dbo_localUnitAudit.AuditLocation, dbo_localUnitAudit.ID, dbo_Population.Population, dbo_Population.CensusYear

FROM dbo_Population INNER JOIN (dbo_LocalUnit INNER JOIN dbo_localUnitAudit ON (dbo_LocalUnit.CountyCd = dbo_localUnitAudit.CountyCd) AND (dbo_LocalUnit.LocalUnitType = dbo_localUnitAudit.LocalUnitType) AND (dbo_LocalUnit.LocalUnitCd = dbo_localUnitAudit.LocalUnitCd)) ON (dbo_Population.LocalUnitCd = dbo_LocalUnit.LocalUnitCd) AND (dbo_Population.LocalUnitType = dbo_LocalUnit.LocalUnitType) AND (dbo_Population.CountyCd = dbo_LocalUnit.CountyCd)

WHERE (((dbo_Population.CensusYear)="2000"));

 

[rac2]

You have

SELECT dbo_localUnitAudit.CountyCd, dbo_localUnitAudit.LocalUnitType, dbo_localUnitAudit.LocalUnitCd, dbo_localUnitAudit.FiscalYear, dbo_localUnitAudit.Type, dbo_localUnitAudit.Extension, dbo_localUnitAudit.AuditReceivedDate, Format(DateSerial([FiscalYear],[FiscalEndMM]+7,0),'mm/dd/yyyy') AS AuditDue, IIf([auditreceiveddate]>[auditdue],"LATE",IIf([auditreceiveddate]<=[auditdue],"TIMELY",IIf([auditreceiveddate] Is Null And DateAdd("m",GetDUeMths([population]),[auditdue] Is Null),"NOT FILED",IIf([auditdue]>Date(),"NOT DUE")))) AS AnnualAuditFiledTimely, DateAdd("m",GetDueMths([Population]),([auditdue])) AS NextAuditDue, dbo_localUnitAudit.Auditor, dbo_localUnitAudit.Notes, dbo_localUnitAudit.AuditLocation, dbo_localUnitAudit.ID, dbo_Population.Population, dbo_Population.CensusYear
...

Readable version of what you have

SELECT A.CountyCd, 
       A.LocalUnitType, 
       A.LocalUnitCd, 
       A.FiscalYear, 
       A.Type, 
       A.Extension,  
       A.AuditReceivedDate, 

Format(DateSerial([FiscalYear],[FiscalEndMM]+7,0),'mm/dd/yyyy') AS AuditDue, 

       IIf( [auditreceiveddate] > [auditdue],
            "LATE",
            IIf( [auditreceiveddate] <= [auditdue],
                 "TIMELY",
                 IIf( [auditreceiveddate] Is Null 
                       And 
                 DateAdd( "m",  
                          GetDUeMths([population]),  
                          [auditdue] Is Null),
                 "NOT FILED", 
                 IIf( [auditdue] > Date(),"NOT DUE")
            )
            )
       ) AS AnnualAuditFiledTimely, 

       DateAdd("m", GetDueMths([Population]), ([auditdue])) AS NextAuditDue, 

       A.Auditor, 
       A.Notes, 
       A.AuditLocation, 
       A.ID, 
       P.Population, 
       P.CensusYear
...

There seem to be some syntax errors in the IIf() expression.
Does the query you posted execute and give a result?


The IIf() piece might be

...
 IIf( [auditreceiveddate] > [auditdue],
      "LATE",
      IIf( [auditreceiveddate] <= [auditdue],
           "TIMELY",
           IIf( [auditreceiveddate] Is Null 
                 And 
                DateAdd( "m", 
                         GetDUeMths([population]), 
                         [auditdue] 
                       ) < Date(), 
                "NOT FILED" , 
                IIf( [auditdue] > Date(), 
                     "NOT DUE", 
                     "IMPOSSIBLE"
                )
           ) 
      )
 ) AS AnnualAuditFiledTimely, 
...

So those nested IIf() should result in
If the audit was received after the due date,
it is "LATE",
else if the audit was received before the due date,
it is "TIMELY",
else if the audit has not been received
and the due date plus the grace period has passed
it is "NOT FILED"
else if the due date is in the future
it is "NOT DUE"
else something "IMPOSSIBLE" has happened.


Also it seems that you have joined the dbo_localUnitAudit
table to itself unnecessarily.

 

[goslincm]

Rac2, thanks for the feedback, much appreciated. You mentioned it looks like I joined the dbo_localUnitAudit table to itself unnecessarily. There is not much name difference, but the other table is called dbo_localUnit.

The query runs, yes, however I'm having trouble with the population less than 4000 records. The reason is because I actually need to check the previous records [nextauditdue] against the current records [auditdue] to determine if that current records status. Yes it may show not filed based on all the computations, but unless it looks at [nextauditdue] field in the previous record, it considers the audit NOT FILED, when in fact its NOT DUE.

Do I need to rewrite this somehow to eliminate the [auditdue] field since its not accurate anyhow for those with the less than 4000 population? I thought I needed that as a starting point as all I have for a known value is [auditreceiveddate].

Based on an [auditreceiveddate] a [population]and a [FiscalEndMM]I didn't know how to compute an [auditdue].

 

[rac2]

...
FROM dbo_Population 
INNER JOIN (
            dbo_LocalUnit 
            INNER JOIN dbo_localUnitAudit ON
                      (dbo_LocalUnit.CountyCd = 
                  dbo_localUnitAudit.CountyCd) 

                  AND (dbo_LocalUnit.LocalUnitType = 
                  dbo_localUnitAudit.LocalUnitType) 

                  AND (dbo_LocalUnit.LocalUnitCd = 
                  dbo_localUnitAudit.LocalUnitCd)

           ) ON (dbo_Population.LocalUnitCd = 
                  dbo_LocalUnit.LocalUnitCd) 

            AND (dbo_Population.LocalUnitType = 
                  dbo_LocalUnit.LocalUnitType) 

            AND (dbo_Population.CountyCd = 
                  dbo_LocalUnit.CountyCd)

WHERE (((dbo_Population.CensusYear)="2000"));

Ah, I see.


Could you clarify what you mean by

...
the previous records [nextauditdue] against the current records [auditdue] to determine if that current records status.
...

Do you mean that a particular Local Unit has two rows in the LocalUnitAudit table? And that you wish to calculate some new value based on data values stored in two different rows?
In other words I am asking what you mean by current record and previous record? Are you using these terms in a database sense or in the context of the business? Records from a database table or records from your audit business?

 

[goslincm]

Rac2, you are correct in that I mean that a particular local unit has more than one row in the LocalUnitAudit table.

The table contains or can, several years worth of audit information for a unit.

And yes, in some cases (those that do not have to file annual audits, but rather can file biennial-which are those with population < 4000)I need to compare the two rows. I need to know if they are not filing this year, its because they filed the previous year. But like I say, those that aren't required to file annually, sometimes choose to do so anyhow.

Hope that helps clarify
Cg

 

[rac2]

OK. That means that you must find and combine the two rows into a single row. This can be tricky using SQL queries, sometimes impossible. But lets not give up yet.

I think the data for one audit unit may consist of rows for all of the audits they have submitted. But we are only interested in their latest audit. Is it late or not? So we only need to look at one row.


The latest audit received then is

SELECT LocalUnitCd,
       MAX(auditreceiveddate) AS "LastAuditDate"
FROM dbo_localUnitAudit
GROUP BY LocalUnitCd

Save this query and call it LatestAudit. I am assuming LocalUnitCd uniquely identifies the units.


This query with one row per unit can be JOINed with the LocalUnit table and the Population table to obtain the audit due date.

So maybe that is what you need to do, get rid of the extraneous rows by using a GROUP BY query to obtain the one row which needs to be examined.

 

[goslincm]

Thanks for not giving up on me yet!

I did the above query and in the process of doing the JOIN now with LocalUnit Table and Population table. However, without the FiscalYear from the dbo_localUnitaudit table, I'm not sure how I am creating my auditdue date.

Its nicely showing me when the last audit was received for a given unit, but I'm not sure how I can format my auditdue date now.

 

[rac2]

Does dbo_localUnitaudit.FiscalYear tell what year the audit was for? Is it different for each row for a given local unit? If so, then you can include it in the LatestAudit query this way.

SELECT LocalUnitCd,
       MAX(auditreceiveddate) AS "LastAuditDate",
       MAX(FiscalYear) AS "LastFiscalYear",
FROM dbo_localUnitAudit
GROUP BY LocalUnitCd

In other words I am assuming that the latest FiscalYear value is in the same row as the latest auditreceiveddate value. If that is not the way the data works then we need to do something else. Let me know.

 

[goslincm]

That helps, right now I think we are VERY close to nailing it. Far as I can see, my last step here is now to define my Status.

I don't know how to say: If [population]<4000 and IF the DateDifference between [lastauditdate],[lastfiscalyear] >730, "Late", "Timely".

If [population]>=4000 and IF the date difference between [lastauditdate],[lastfiscalyear] >365, "late", "Timely

 

[rac2]

Now that you have all of the relevant facts on one row you can use nested IIf() functions to obtain those status labels.

The syntax of an IIf() function is IIf(boolean_expression, value_if_true, value_otherwise).

SELECT
IIf( [population] < 4000, 
     IIf(
     DateDiff("d", [lastfiscalyear],[lastauditdate]) > 730,
     , "Late", "Timely"
     ),
     IIf(
     DateDiff("d", [lastfiscalyear],[lastauditdate]) > 365,
     , "Late", "Timely"
     )
   ) AS "Status"

FROM ...

So if pop is small then calculate status on 730 days otherwise calculate status on 365 days.

Calculating status is
if the number of days from lastfiscalyear to lastauditdate is more than X days then "Late" otherwise "Timely".

Assuming [lastauditdate] follows [lastfiscalyear] and
that [lastfiscalyear] is a date, such as Sep 30, 2006.

Other conditions will be needed to handle those not submitted, but you get the idea.

See my first post where I show four levels of nested IIf().

 

[goslincm]

Well, here I go downhill again. In looking closely at the fiscal year value, when I query based on MAX value in that field, I'm getting all 2007 as it appears everyone has a record established for each year. So, even though someone's last audit filed might be for fiscal year 2005, its giving me 2007.

I'm almost bald at this end by now ;-)

 

[rac2]

OK, well it is pretty hard to help beyond this point because the issues seem to be in the nature of when and how meaningful data is stored and used in your application. That is just too difficult to communicate about in a forum like this.

Sorry, but I must bow out of this thread.

 

[PHV]

Replace this:
SELECT LocalUnitCd,
MAX(auditreceiveddate) AS "LastAuditDate",
MAX(FiscalYear) AS "LastFiscalYear",
FROM dbo_localUnitAudit
GROUP BY LocalUnitCd
with this:
SELECT A.LocalUnitCd, L.LastAuditDate, A.FiscalYear AS LastFiscalYear
FROM dbo_localUnitAudit AS A INNER JOIN (
SELECT LocalUnitCd, MAX(auditreceiveddate) AS LastAuditDate
FROM dbo_localUnitAudit GROUP BY LocalUnitCd
) AS L ON A.LocalUnitCd = L.LocalUnitCd AND A.auditreceiveddate = L.LastAuditDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[goslincm]

Rac2, I understand and thank you for all your help you provided.

 

[goslincm]

PHV, thanks so much for coming on board where Rac2 left off. What you suggested took care of that issue.

May I ask one other question of you?

This query now tells me all the late and timely audits that have been filed, how would I go about finding out who has not filed?

 

[goslincm]

Good morning, hoping someone might be able to tell me how to or if I can work with this to retrieve now those units who have not filed an audit:

SELECT A.LocalUnitCd, L.LastAuditDate, A.FiscalYear AS LastFiscalYear

FROM dbo_localUnitAudit AS A INNER JOIN (

SELECT LocalUnitCd, MAX(auditreceiveddate) AS LastAuditDate

FROM dbo_localUnitAudit GROUP BY LocalUnitCd
) AS L ON A.LocalUnitCd = L.LocalUnitCd AND A.auditreceiveddate = L.LastAuditDate

 

[PHV]

Have you even tried to replace INNER JOIN with LEFT JOIN ?

 

[goslincm]

No, I haven't.

You know this has been a major task for me at work and I've only just taken my first SQL class in an attempt to alleviate some of this pain I'm feeling. So this stuff has been way over my head. I know I don't need to tell anyone out here that though ;-)