Query Technique Needed

[Michael]

Hi Folks - Here's my scenario:

I have a query with the following fields/data:

CaseID StatusDate Status
1 1/11/2005 Open
1 2/10/2005 Closed
2 1/13/2005 Open
3 1/17/2005 Open
3 2/19/2005 Closed
4 1/21/2005 Open
4 4/11/2005 Closed
5 4/24/2005 Open

I need to produce a query/report that displays all open cases. Seems pretty
straightforward until this ... If a case is opened AND closed in the
reporting period (quarterly), the case should NOT be considered open.

So, in the above example, there would be 2 OPEN cases for the period
1/1/2005 - 3/31/2005; CaseID 2 and 4. Make sense? Any ideas?

Michael

 

[[MVP] S.Clark]

The technique choices are many.

1. Overcomplicated Correlated subquery that is difficult to debug, but might
get it done in one query.
2. A series of queries strung together to get the result.
3. Append/Delete data to/from a temp table as groups of rows are
qualified/disqualified.

I don't like 1, so I generally do a combination of 2 & 3.

Like:
a. Find all Cases Opened during the time range. Use this query as a basis
for other queries.
b. From the OpenedCases query, write those records to a table that do NOT
have a close record, based on the desired time span.
c. From the OpenedCases query, write those records to a table that DO have a
close record, but that the close date falls outside of the desired time
span.

 

[Adam Turner via AccessMonster.com]

Hi Folks - Here's my scenario:

I have a query with the following fields/data:

CaseID StatusDate Status
1 1/11/2005 Open
1 2/10/2005 Closed
2 1/13/2005 Open
3 1/17/2005 Open
3 2/19/2005 Closed
4 1/21/2005 Open
4 4/11/2005 Closed
5 4/24/2005 Open

I need to produce a query/report that displays all open cases. Seems pretty
straightforward until this ... If a case is opened AND closed in the
reporting period (quarterly), the case should NOT be considered open.

So, in the above example, there would be 2 OPEN cases for the period
1/1/2005 - 3/31/2005; CaseID 2 and 4. Make sense? Any ideas?

Michael

Here's my stab at it:

Select CaseID, Status
FROM MyTable mt1(NOLOCK)
JOIN MyTable mt2(NOLOCK) ON mt1.CaseID = mt2.CaseID
WHERE DateDiff(“d”, mt1.StatusDate, mt2.StatusDate) <= 90
AND mt1.Status = "Open"
AND mt2.Status <> "Closed"

Join the table to itself on CaseID, then check the differences in dates
through the aliases.

 

[Guest]

This looks a bit ugly (at least to me), but paste it in Access and view it
from the query grid and it make make a bit more sense......

SELECT Table1.CaseID,
IIf([statusdate]<=#3/31/2005#,"Q1",(IIf([statusdate]<=#6/30/2005#,"Q2",(IIf([statusdate]<=#9/30/2005#,"Q3","Q4"))))) AS Quarter
FROM Table1
GROUP BY Table1.CaseID,
IIf([statusdate]<=#3/31/2005#,"Q1",(IIf([statusdate]<=#6/30/2005#,"Q2",(IIf([statusdate]<=#9/30/2005#,"Q3","Q4")))))
HAVING (((Count(Table1.CaseID))=1) AND ((First(Table1.Status))<>"Closed"));


HTH
Hafeez Esmail

 

[Tom Ellison]

Or, for those who do like the correlated subquery:

SELECT CaseID, StatusDate
FROM YourTable T
WHERE Status = "Open"
AND NOT EXISTS(SELECT * FROM YourTable T1
WHERE T1.CaseID = T.CaseID AND T1.Status = "Closed")

Tom Ellison