[Sql Query] Menemukan selisih dan mengroupingkannya. :)

SELECT r.*,
IF(@lastcell=cell_id,@diff:=SECOND(TIMEDIFF(datetime_production, @interv)),0) selisih,
IF(@diff<11, @count, @count:=@count+1),
@lastcell:=cell_id,
@interv:=datetime_production
FROM
(SELECT * FROM not_goods WHERE date_production = CURDATE() AND (MINUTE(datetime_production)>54 OR MINUTE(datetime_production)<11) ORDER BY cell_id, id, counters) AS r
CROSS JOIN
(SELECT @interv := ‘0000-00-00 00:00:00’, @first:= 0, @lastcell:=NULL, @count :=0, @diff:=0) AS vars
ORDER BY cell_id,counters;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s