To Chris : Problem on Loan Period calculation
Hi, We are trying to design a stat report module for Koha here in France with Paul. And we have a problem on issues stats reporting. In issues table, timestamp is modified as soon as the table is touched and returndate stores the date of return. The issue date is never stored in this table anywhere else. Nevertheless, it is stored in statistics. And returns are also stored there. But then, there is no way to know how they are related to one another. A selection on itemnumber and borrower card will tell us it was issued and return, but not Which returns answers Which issue. Suppose One borrows an item more than once and we are lost. If one has a convenient code to know the average Loan period, that is a way to calculate the average time between issue and return date of an item for a borrower, we would be grateful. Many Thanks -- Henri-Damien LAURENT
On Wed, Mar 02, 2005 at 06:46:23PM +0100, Henri-Damien LAURENT said:
Hi, We are trying to design a stat report module for Koha here in France with Paul. And we have a problem on issues stats reporting.
In issues table, timestamp is modified as soon as the table is touched and returndate stores the date of return. The issue date is never stored in this table anywhere else.
Nevertheless, it is stored in statistics. And returns are also stored there.
But then, there is no way to know how they are related to one another. A selection on itemnumber and borrower card will tell us it was issued and return, but not Which returns answers Which issue. Suppose One borrows an item more than once and we are lost.
If one has a convenient code to know the average Loan period, that is a way to calculate the average time between issue and return date of an item for a borrower, we would be grateful.
You can work out the issue date by looking at the date_due field in issues. And subtracting the loan period from it. Checking the renewals column also, as if this is 1 say, the item has been renewed once .. so the original issue date will be date_due - ( 2 * loan period) using the Date::Manip module you can do this quite easily. code snippet off the top of my head (beware probably full of syntax errors) use Date::Manip; my $query = "SELECT * FROM issues WHERE <some condition here>"; my $sth = $dbh->prepare($query); $sth->execute; my $total; my $number_of_issues=0; while (my $data = $sth->fetchrow_hashref()){ my $due_date=ParseDate($data->{'date_due'}); my $renewals=$data->{'renewals'}; # probably need to do a select on the itemnumber to find what itemtype it # is and thus what loan period, ill pretend they are all the same for now my $loanperiod=21; $renewals++; # if null now equals 1 my $loanlength=$loanperiod * $renewals; $issuedate = DateCalc($due_date,"- $loanlength days ago"); my $returndate=ParseDate($data->{'retundate'}); $delta = DateCalc($returndate,$issuedate,\$err); # => 0:0:WK:DD:HH:MM:SS the weeks, days, hours, minutes, # and seconds between the two $number_of_issues++; # do something to the delta to just get the days say $total += $delta; } $sth->finish; my $average = $total/$num_of_issues; Something like that anyway :) Hope this is some help Chris -- Chris Cormack Programmer 027 4500 789 Katipo Communications Ltd chris@katipo.co.nz www.katipo.co.nz
Chris' solution still won't give you an accurate issue date, since he presumes that people renew items on the date_due -- and we all know that borrowers are not that dependable (damn!). Adding the issue_date to the issues table seems to be the only way to get an accurate statistic. I'm curious, Henri-Damien -- are you trying to do a daily statistics report? Is that why you need the issue date? Stephen Henri-Damien LAURENT said:
Hi, We are trying to design a stat report module for Koha here in France with Paul. And we have a problem on issues stats reporting.
In issues table, timestamp is modified as soon as the table is touched and returndate stores the date of return. The issue date is never stored in this table anywhere else.
Nevertheless, it is stored in statistics. And returns are also stored there.
But then, there is no way to know how they are related to one another. A selection on itemnumber and borrower card will tell us it was issued and return, but not Which returns answers Which issue. Suppose One borrows an item more than once and we are lost.
If one has a convenient code to know the average Loan period, that is a way to calculate the average time between issue and return date of an item for a borrower, we would be grateful.
Many Thanks
-- Henri-Damien LAURENT
-- Stephen Hedges Skemotah Solutions, USA www.skemotah.com -- shedges@skemotah.com
On Mon, Mar 07, 2005 at 07:18:05PM -0500, Stephen Hedges said:
Chris' solution still won't give you an accurate issue date, since he presumes that people renew items on the date_due -- and we all know that borrowers are not that dependable (damn!). Adding the issue_date to the issues table seems to be the only way to get an accurate statistic.
Yep it makes that assumption it also makes the assumption that none of the due_dates have been overriden (which you can do in circulation). I think it may be the only way to get an approximation of the average loan length with the data as it stands. Going forward we should add an issue_date column to the table, if that is useful data to know.
I'm curious, Henri-Damien -- are you trying to do a daily statistics report? Is that why you need the issue date?
I think he is trying to work out average loan length, which would be an interesting statistic to know. Daily stats you can figure out from the statistics table. Chris -- Chris Cormack Programmer 027 4500 789 Katipo Communications Ltd chris@katipo.co.nz www.katipo.co.nz
Chris Cormack a écrit :
On Mon, Mar 07, 2005 at 07:18:05PM -0500, Stephen Hedges said:
Chris' solution still won't give you an accurate issue date, since he presumes that people renew items on the date_due -- and we all know that borrowers are not that dependable (damn!). Adding the issue_date to the issues table seems to be the only way to get an accurate statistic.
Yep it makes that assumption it also makes the assumption that none of the due_dates have been overriden (which you can do in circulation). I think it may be the only way to get an approximation of the average loan length with the data as it stands.
Going forward we should add an issue_date column to the table, if that is useful data to know.
I'm curious, Henri-Damien -- are you trying to do a daily statistics report? Is that why you need the issue date?
I think he is trying to work out average loan length, which would be an interesting statistic to know. Daily stats you can figure out from the statistics table.
Chris
Utterly right. I was asked to work on statistic reports, more precisely on issues stats to provide the operator with average loan length over a period. Daily statistics has already been coped with. Have a nice day. -- Henri-Damien LAURENT
participants (3)
-
Chris Cormack -
Henri-Damien LAURENT -
Stephen Hedges