# 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.mindatet
+ime) <= B.[datetime])
[download]

#!/usr/bin/perl 
#query to get the difference between dates and things in the same colu
+mns... 
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",$use
+r,$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 sec
+onds in a day to get days
        # voila!
print "$brand,\t$samples{$brand}\t$date\t$date_old\t$date_diff,\t$weig
+ht,\t$weight_old,\t".($weight-$weight_old)."\n";
        $date_old    = $date;
        $weight_old    = $weight;
}
$sth_query->finish;
$dbh->disconnect;
[download]

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

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