Do a calculation on a series of dates linked to a case

  • Thread starter Thread starter Pat
  • Start date Start date
P

Pat

I have an Access 2000 database and I would like to work out how many days a
'case' is open for. This can either mean a calculation between 2 dates -
i.e. a new case date and then a closed date (which I can do)- but it may
also mean that a case can be open and closed several times - and I just want
to add up the 'open' periods.

As a start off, I have joined the 2 tables in question Case and
CaseDateStatus and have the SQL as shown below

SELECT Case.CaseID, CaseDateStatus.CDate, CaseDateStatus.Type
FROM [Case] INNER JOIN CaseDateStatus ON Case.CaseID = CaseDateStatus.CaseID
WHERE (((Case.currentcasestatus)=3));

The WHERE clause is just selecting those cases that are currently closed,
as I only want to calculate against those. Not sure if this is the correct
start point though.

When a case is open or live - the Type in in CaseDateStatus is either a 1, 4
or 5 - if it's closed it's a 3. This means we start off with an 'open'
Type, then the case is 'closed' then it's open again and so on. I assume I
need to start from the Max Cdate against the case (which will always be
closed) and work backwards? I want this type of calculation done against
each Case and the result to be a number of days; I can then average them
etc. Just not sure about the way to write this with more than 2 dates
involved.

Any help would be much appreciated.
TIA

Pat
 
What you are wanting will be very difficult if not
impossible with a query. You will probably want to handle
cases where there is a stop date with no matching start
date and vice versa. One good approach would be to create
a temporary table and fill it with data using a VBA
procedure.
If you would like help with this, contact me on
chris
at
mercury-projects
dot
co
dot
nz
-----Original Message-----
I have an Access 2000 database and I would like to work out how many days a
'case' is open for. This can either mean a calculation between 2 dates -
i.e. a new case date and then a closed date (which I can do)- but it may
also mean that a case can be open and closed several times - and I just want
to add up the 'open' periods.

As a start off, I have joined the 2 tables in question Case and
CaseDateStatus and have the SQL as shown below

SELECT Case.CaseID, CaseDateStatus.CDate, CaseDateStatus.Type
FROM [Case] INNER JOIN CaseDateStatus ON Case.CaseID = CaseDateStatus.CaseID
WHERE (((Case.currentcasestatus)=3));

The WHERE clause is just selecting those cases that are currently closed,
as I only want to calculate against those. Not sure if this is the correct
start point though.

When a case is open or live - the Type in in
Click to expand...
CaseDateStatus is either a 1, 4
 
When a case is open or live - the Type in in CaseDateStatus is either a 1, 4
or 5 - if it's closed it's a 3. This means we start off with an 'open'
Type, then the case is 'closed' then it's open again and so on. I assume I
need to start from the Max Cdate against the case (which will always be
closed) and work backwards? I want this type of calculation done against
each Case and the result to be a number of days; I can then average them
etc. Just not sure about the way to write this with more than 2 dates
involved.
Click to expand...

I think a Self Join query will help here: create a query adding this
table to the query grid *twice*, joining by the CaseID. In the first
instance use

=3

to select the Close event; in the second instance use

IN (1, 4, 5) as a criterion on CaseStatus, and

=DMax("[CDate]", "[tablename]", "[CaseID] = " & [CaseID] & " AND
[Cdate] < #" & [CDate] & "# AND CaseStatus IN (1,4,5)")

This should give you pairs of records, with the Close in the first
table's fields and the most recent prior Open in the other. You can
then use DateDiff to calculate the elapsed time, change the query to a
Totals query and sum it up, grouping by CaseID.
 
Thanks very much indeed. I'll try that.

Regards
Pat

John Vinson said:
When a case is open or live - the Type in in CaseDateStatus is either a 1, 4
or 5 - if it's closed it's a 3. This means we start off with an 'open'
Type, then the case is 'closed' then it's open again and so on. I assume I
need to start from the Max Cdate against the case (which will always be
closed) and work backwards? I want this type of calculation done against
each Case and the result to be a number of days; I can then average them
etc. Just not sure about the way to write this with more than 2 dates
involved.
Click to expand...

I think a Self Join query will help here: create a query adding this
table to the query grid *twice*, joining by the CaseID. In the first
instance use

=3

to select the Close event; in the second instance use

IN (1, 4, 5) as a criterion on CaseStatus, and

=DMax("[CDate]", "[tablename]", "[CaseID] = " & [CaseID] & " AND
[Cdate] < #" & [CDate] & "# AND CaseStatus IN (1,4,5)")

This should give you pairs of records, with the Close in the first
table's fields and the most recent prior Open in the other. You can
then use DateDiff to calculate the elapsed time, change the query to a
Totals query and sum it up, grouping by CaseID.
Click to expand...
 
Back
Top