# sql to calculate date differences in same column
# SELECT B.*
# FROM
# (SELECT ordernum, agent, MIN([datetime]) AS mindatetime
# FROM YourTable
# GROUP BY ordernum, agent) AS A
# JOIN YourTable AS B
# ON A.ordernum = B.ordernum
# AND A.agent = B.agent
# AND (A.mindatetime = B.[datetime] OR DATEADD(minute, 120, A.mindatetime) <= B.[datetime])

##</code><code>##

#!/usr/bin/perl 
#query to get the difference between dates and things in the same columns... 
use strict;
use DBI;
use Time::Local;
##################### mysql definitions 
#password and access
         my $db       = "new"         ;
         my $host     = "192.168.1.20" ;
         my $user     = "user"         ;
         my $pass     = "pass"         ;  
	my  $dbh  = DBI->connect ("DBI:mysql:database=$db:host=$host",$user,$pass)or die "Can't connect to database: $DBI::errstr\n";

############################ mysql connection definitions 
#the key thing is to do a select query that results in the items being in order in the resultset...

my $sql_query ="SELECT brand, date, weight FROM test2 ORDER BY brand, date ASC";
my $sth_query = $dbh->prepare($sql_query);
$sth_query->execute();
#######################################################
my $brand="";
my $date_old=timelocal(0,0,0,10,10,1980); #just set it to something
my $date;
my $date_diff;
my $weight;
my $weight_old;
my %samples;
my %dates;
my @row;
while(@row = $sth_query->fetchrow_array()){

		$brand	  	= $row[0];
		$date		= $row[1];
		$weight 	= $row[2];
		$samples{$brand}++; #increment the number of times this brand has appeared 

if($samples{$brand} ==1){ #if it is the first time then make old = new so that no big step changes in weight etc recorded
	$date_old	= $date;
	$weight_old	= $weight;
}

#difference in two datestrings calculation - convert to seconds - I'm sure there are better ways to do this..
$date =~ m|(\d{4}).(\d{2}).(\d{2})|;
my $date_new1= timelocal(0,0,0,$3, $2-1, $1); #the '-1' cos locatime(0,0,0,dd,mm,yyyy) months count from zero
$date_old =~ m|(\d{4}).(\d{2}).(\d{2})|;
my $date_old1= timelocal(0,0,0,$3, $2-1, $1);

$date_diff = $date_new1- $date_old1; # and subtract to get difference in seconds
$date_diff = $date_diff / 60 / 60 / 24; # then divide by number of seconds in a day to get days
		# voila!
print "$brand,\t$samples{$brand}\t$date\t$date_old\t$date_diff,\t$weight,\t$weight_old,\t".($weight-$weight_old)."\n";
		$date_old	= $date;
		$weight_old	= $weight;
}
$sth_query->finish;
$dbh->disconnect;

##</code><code>##

CREATE TABLE  `new`.`test2` (
  `id` int(11) NOT NULL auto_increment,
  `brand` varchar(32) NOT NULL,
  `date` date NOT NULL,
  `weight` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

INSERT INTO `new`.`test2` VALUES  (1,'C','2008-02-06',15),
 (2,'C','2008-02-05',20),
 (3,'A','2008-02-04',140),
 (4,'C','2008-02-03',10),
 (5,'B','2008-02-01',190),
 (6,'A','2008-02-01',150),
 (7,'B','2008-01-03',200),
 (8,'A','2008-01-01',110),
 (9,'A','2007-01-01',100);

##</code><code>##

A,      1       2007-01-01      2007-01-01      0,      100,    100,    0
A,      2       2008-01-01      2007-01-01      365,    110,    100,    -10
A,      3       2008-02-01      2008-01-01      31,     150,    110,    -40
A,      4       2008-02-04      2008-02-01      3,      140,    150,    10
B,      1       2008-01-03      2008-01-03      0,      200,    200,    0
B,      2       2008-02-01      2008-01-03      29,     190,    200,    10
C,      1       2008-02-03      2008-02-03      0,      10,     10,     0
C,      2       2008-02-05      2008-02-03      2,      20,     10,     -10
C,      3       2008-02-06      2008-02-05      1,      15,     20,     5