A few questions:
1) have you tried 'print' statements to see what's going on inside the loop?
2) With your code, if your database contains more than one ID, you will compare two different IDs at the boundry between them, which is probably not what you want.
3) what are the proper behaviors at each state transition? If I understood your description, to get the open time minus the hold time, they should be:
open -> open = add
open -> hold = add
open -> closed = add
hold -> open = sub
hold -> hold = ignore
hold -> closed = sub
closed -> open = ignore
closed -> hold = ignore
closed -> closed = ignore
In other words,
open -> anything = add
closed -> anything = ignore
hold -> hold = ignore
hold -> open/closed = sub
It's clear that your code isn't doing that, but I'm not sure if my understanding of the problem is wrong. BEFORE you start coding, it needs to be clear what the problem is...
Here's a stab at what I think you're looking for, based on the above state diagram:
my $dbh = DBI->connect("dbi:Sybase:server=CASTOR",$USER, $PASS);
my $select = "select entrydate,status, id from database
order by id, entrydate";
my $sth=$dbh->prepare($select);
$sth->execute;
my ($entrydate, $statusid, $objectid) = $sth->fetchrow;
my ($nentrydate, $nstatusid, $nobjectid);
while ( my $row = $sth->fetchrow_arrayref) {
($nentrydate, $nstatusid, $nobjectid) =
($entrydate, $statusid, $objectid);
($entrydate, $statusid, $objectid) = @$row;
# "closed" status can be ignored
next if ($nstatusid eq "closed");
if ($nobjectid eq $objectid) {
# object ids can be compared
if ($nstatusid eq "start") {
$CUSTHOLD += parse_date($entrydate) -
parse_date($nentrydate);
} else {
# $nstatusid eq "hold"
if ($statusid ne "hold") {
$CUSTHOLD -= parse_date($entrydate) -
parse_date($nentrydate);
}
}
} else {
# object ids cannot be compared
$NOW = time; # time in same format as returned by parse_date
if ($nstatusid eq "start") {
$CUSTHOLD += $NOW - parse_date($nentrydate);
} else { # $nstatusid eq "hold"
if ($statusid ne "hold") {
$CUSTHOLD -= $NOW - parse_date($nentrydate);
}
}
}
}
return $CUSTHOLD;
___
-DA