Calculating # days in a record was in a particular status 2

[Pack10]

I have a routine where users will be adding records with a status value and transaction date. Management wants to track how long (# days) the record was in that status.

ENTITY ENTITY NAME ANALYST STATUS DATE
TNY TNY Enterprises Jerry In Process 10/11/10
TNY TNY Enterprises Jerry With Mgmt 10/13/10

So,in this case, it was In Process for two days. How do I do this in a query or a report. The two dates are not in the same record. Is there a better way to design the table to make this report simpler.

 

[Golom]

You could

Select A.Entity, [blue]... Other fields ...[/Blue], A.Status, 
       DateDiff("d", A.[Date], B.[Date]) As [Days In Status]

From myTable As A INNER JOIN myTable As B
     ON A.Entity = B.Entity

Where B.[Date] = (Select MIN(X.[Date]) From myTable As X
                  Where X.[Date] > A.[Date]
                    AND X.Entity = A.Entity)

UNION

Select A.Entity, [blue]... Other fields ...[/Blue], A.Status, 
       DateDiff("d", A.[Date], Date()) As [Days In Status]

From myTable As A 

Where A.[Date] = (Select MAX(X.[Date]) From myTable As X
                  Where X.Entity = A.Entity)

ORDER BY Entity, [Date]

The bit after the UNION just picks up the current status which won't appear in the part of the query before the UNION.

 

[Pack10]

WOW! That worked perfectly. If you have a moment, could you explain briefly how this works. I have never seen a query like this. It could be a great learning tool. SUPER, great. Thank you sooooo much.

 

[Golom]

I'm not sure I can add much beyond what just reading the statement will tell you.

[red]Before the UNION[/red]

You need two records from the table to do the calculation. Specifically you need the current record and the next record following it. We therefore need two copies of the table joined together and I have assigned aliases "A" and "B" to those two copies. As we proceed through the records in our first copy of the table (called "A") the next record is the record for the same entity whose [Date] field is the smallest (i.e. MIN) date field that is greater than the date field of the record we are on in "A". That record becomes the record we select in the copy of the table that we are calling "B". After that a simple DateDiff call computes the number of days.

[red]After the UNION[/red]

Takes care of those records that do not have a "next" record (i.e. the ones that are the most recent ones for a given entity). The number of days that it computes are from the record date to the current date.

As you can see, the SQL is much more concise than this rather wordy description.

 

[Pack10]

That's simply awesome use of SQL. I was going to use a temp table and DAO recordsets to code this. This is so much cleaner. I need to kick my SQL skills to a higher level.
Thank you again for the help.

 

[PHV]

Another way without union nor correlated subquery:

SELECT A.Entity, A.[Entity Name], A.Analyst, A.Status, A.Date, Nz(MIN(B.Date),Date())-A.Date AS [Days In Status]
FROM tblEntity A LEFT JOIN tblEntity B ON A.Entity = B.Entity AND A.Date < B.Date
GROUP BY A.Entity, A.[Entity Name], A.Analyst, A.Status, A.Date
ORDER BY 1, 5

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886