It's a bit hard to describe what I intend. So I will just show what I want to achieve and you can tell me if it's possible and how.
This is for a stock transaction log.
DATA:
t_type productid t_date t_stock t_stock_after
1 2 2013-11-06 16:52:30 1 80
1 3 2013-11-06 15:50:40 1 60
1 2 2013-11-06 13:52:30 1 81
1 2 2013-11-06 13:48:30 1 82
1 2 2013-11-05 13:52:30 1 83
1 2 2013-11-04 14:56:30 1 84
1 2 2013-11-04 13:55:30 2 85
1 2 2013-11-04 13:54:30 1 87
2 2 2013-11-04 13:53:30 10 88
1 2 2013-11-04 13:52:30 1 78
OBJECTIVE:
t_type productid t_date t_stock t_stock_after
1 2 2013-11-06 3 80
1 2 2013-11-05 1 83
1 2 2013-11-04 4 84
2 2 2013-11-04 10 88
1 2 2013-11-04 1 78
So what I wish to do is group the * (asterisk) ones only because they share the same day and type and they are sequential.
t_type productid t_date t_stock t_stock_after
* 1 2 2013-11-06 16:52:30 1 80
* 1 2 2013-11-06 13:52:30 1 81
* 1 2 2013-11-06 13:48:30 1 82
1 2 2013-11-05 13:52:30 1 83
* 1 2 2013-11-04 14:56:30 1 84
* 1 2 2013-11-04 13:55:30 2 85
* 1 2 2013-11-04 13:54:30 1 87
2 2 2013-11-04 13:53:30 10 88
1 2 2013-11-04 13:52:30 1 78
UPDATE:
I also realized something, t_stock_after does not SUM. It only shows the last t_stock_after. Sample data has been corrected to simulate real life data and help get to an answer.
In order to help as much as possible:
This is the table:
id (int and auto increment), productid, userid, t_type, t_date, t_stock, t_stock_after, t_reason
UPDATE 2 - WORKING CODE
And as I said, here is the code I eventually managed to create =)
SQL:
if(isset($_GET['mindate']) && $_GET['mindate']!="" && isset($_GET['maxdate']) && $_GET['maxdate']!="")
$stocklog = $dataserver->query("SELECT t_type, productid, t_date, SUM(t_stock) AS stock, SUM(t_stock_after) AS stockafter FROM store_log_transactions WHERE productid = ".$_GET['id']." AND (DATE(t_date) BETWEEN DATE_SUB('".$_GET['mindate']."', INTERVAL 1 MONTH) AND '".$_GET['maxdate']."') GROUP BY t_date, t_type");
else
$stocklog = $dataserver->query("SELECT t_type, productid, t_date, SUM(t_stock) AS stock, SUM(t_stock_after) AS stockafter FROM store_log_transactions WHERE productid = ".$_GET['id']." AND (t_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND NOW()) GROUP BY t_date, t_type");
PHP:
$id=1;
$temp_date = "";
$temp_type="";
$temp_stock=0;
$temp_stock_after=9999999999;
while($log = $stocklog->fetch_object())
{
$current_date = date("Y-m-d",strtotime($log->t_date));
$current_type = $log->t_type;
$current_stock = $log->stock;
$current_after = $log->stockafter;
if($current_date==$temp_date || $id==1)
{
if($current_type==$temp_type || $id==1)
{
$temp_stock+=$current_stock;
if($temp_stock_after>$current_after);
$temp_stock_after=$current_after;
$temp_date=$current_date;
$temp_type=$current_type;
}
else
{
$transactiontype;
switch($temp_type)
{
case 1: $transactiontype='net sales'; break;
case 2: $transactiontype='net return'; break;
case 3: $transactiontype='transfer in'; break;
case 4: $transactiontype='trasnfer out'; break;
}
echo '<div class="listitem"><div>'.$temp_stock.'</div><div>'.$temp_stock_after.'</div><div>'.$transactiontype.'</div><div>'.$temp_date.'</div></div>';
$temp_date=$current_date;
$temp_type=$current_type;
$temp_stock_after=$current_after;
$temp_stock=$current_stock;
}
}
else
{
$transactiontype;
switch($temp_type)
{
case 1: $transactiontype='net sales'; break;
case 2: $transactiontype='net return'; break;
case 3: $transactiontype='transfer in'; break;
case 4: $transactiontype='trasnfer out'; break;
}
echo '<div class="listitem"><div>'.$temp_stock.'</div><div>'.$temp_stock_after.'</div><div>'.$transactiontype.'</div><div>'.$temp_date.'</div></div>';
$temp_date=$current_date;
$temp_type=$current_type;
$temp_stock_after=$current_after;
$temp_stock=$current_stock;
}
$id++;
if($id>$stocklog->num_rows) {
$transactiontype;
switch($temp_type)
{
case 1: $transactiontype='net sales'; break;
case 2: $transactiontype='net return'; break;
case 3: $transactiontype='transfer in'; break;
case 4: $transactiontype='trasnfer out'; break;
}
echo '<div class="listitem"><div>'.$temp_stock.'</div><div>'.$temp_stock_after.'</div><div>'.$transactiontype.'</div><div>'.$temp_date.'</div></div>';
}
}
You are looking for this ...
GROUP BY t_type, productid, DATE(t_date)
If I understand correctly you are going to want to group the dates by only date, and each other field by itself, then sum the t_stock_after. If that is correct this would be pretty easy and would be
SELECT t_type, procuctid, DATE(t_date) as t_date, t_stock, SUM(t_stock_after) as t_stock_after
FROM table
GROUP BY t_type, product_id, DATE(t_date);
So whatever else you want, first off, you want the rows returned by this query, right?...
SELECT x.*
FROM
( SELECT a.*
, COUNT(*) rank
FROM stock_transaction_log a
JOIN stock_transaction_log b
ON b.id <= a.id
GROUP
BY id
) x
LEFT
JOIN
( SELECT a.*
, COUNT(*) rank
FROM stock_transaction_log a
JOIN stock_transaction_log b
ON b.id <= a.id
GROUP
BY id
) y
ON y.t_type = x.t_type
AND y.productid = x.productid
AND DATE(y.t_date) = DATE(x.t_date)
AND y.rank = x.rank - 1
WHERE y.id IS NULL;
Edited to allow for the possibility that id is not an unbroken sequence.