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
[download]

In other words,

open -> anything    = add
closed -> anything  = ignore
hold -> hold        = ignore
hold -> open/closed = sub
[download]

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;
[download]

___
-DA

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.