[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;

Prosedure MySQL pertamaku.

fasilitas ini sebenernya fasilitas kuno yang tersedia di mysql. cuman kali ini aja kayaknya butuh prosedur untuk menginput datanya. so, belajar dulu. dan alhamdulillah berhasil sesuai dengan yang diinginkan. 🙂

 

ini dia prosedure buatan saya:

DELIMITER $$

DROP PROCEDURE IF EXISTS `save_state`$$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `kang_aep`.`save_state`(id_line VARCHAR(10),
fg_qty INT,
ng_qty INT,
er_qty INT)
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT ‘string’*/
BEGIN
DECLARE last_id INT(11) DEFAULT 0;
DECLARE last_fg INT(11) DEFAULT 0;
DECLARE last_ng INT(11) DEFAULT 0;
DECLARE last_er INT(11) DEFAULT 0;
DECLARE newNG BOOL DEFAULT FALSE;
SELECT MAX(t_counting_id)
INTO last_id
FROM t_counting
WHERE t_counting.`line_id` = id_line;

SELECT t_counting_id,
COALESCE( fg_quantity,0),
COALESCE(ng_quantity,0),
COALESCE(e_quantity,0)
INTO last_id, last_fg, last_ng, last_er
FROM t_counting
WHERE t_counting.`line_id` = id_line
AND t_counting_id = last_id;

WHILE last_fg < fg_qty DO
SET last_fg := last_fg + 1;
INSERT INTO t_counting(line_id,ng_quantity, fg_quantity,e_quantity, TYPE)
VALUE(id_line, last_ng, last_fg, last_er, “FG”);
END WHILE;

WHILE last_ng < ng_qty DO
SET last_ng := last_ng + 1;
INSERT INTO t_counting(line_id,ng_quantity, fg_quantity,e_quantity, TYPE)
VALUE(id_line, last_ng, last_fg, last_er, “NG”);
SET newNG := TRUE;
END WHILE;

WHILE last_er < er_qty DO
SET last_er := last_er + 1;
INSERT INTO t_counting(line_id,ng_quantity, fg_quantity,e_quantity, TYPE)
VALUE(id_line, last_ng, last_fg, last_er, “ER”);
END WHILE;
IF newNG THEN
INSERT INTO `t_message`(`number`, `message`)
(SELECT `telp`, CONCAT(“Telah terdeteksi metal di line (“, `linename` ,”), plan (“, `planname`, “) sejumlah “, `ng_quantity`, “pcs dengan klaim error sejumlah “, `e_quantity`, “pcs.”) message
FROM `v_pre_sms` WHERE `v_pre_sms`.`line_id` = id_line);
INSERT INTO outbox(`DestinationNumber`, `TextDecoded`)
(SELECT `telp`, CONCAT(“Telah terdeteksi metal di line (“, `linename` ,”), plan (“, `planname`, “) sejumlah “, `ng_quantity`, “pcs dengan klaim error sejumlah “, `e_quantity`, “pcs.”) message
FROM `v_pre_sms` WHERE `v_pre_sms`.`line_id` = id_line);
END IF;
END$$

DELIMITER ;

alhamdulillah berjalan dengan baik sekali. 🙂

QT5, Mengatasi “QMYSQL driver not loaded”

  1. cd /opt/Qt5.6.0/5.6/gcc_64/plugins/sqldrivers
  2. ldd libqsqlmysql.so
  3. jika ktemu “libmysqlclient_r.so.16 => not found“, pindah ”
    cd /usr/lib/x86_64-linux-gnu"
  4. cp libmysqlclient_r.so.18 libmysqlclient_r.so.16
  5. ulangi langkah 1 hingga 2, jika ketemu “libssl.so.10 => not found”, lanjutkan ke langkah 3. lalu, “sudo ln -s libssl.so.1.0.0 libssl.so.10”.
  6. ulangi langkah 1-2, jika ketemu “libcrypto.so.10 => not found”, lanjutkan ke langkah 3, lalu, “sudo ln -s libcrypto.so.1.0.0 libcrypto.so.10”.

sekian terima kasih. semoga membantu. 🙂

[MySQL] Perhitungan saldo compatible dengan Jasperreport

biasanya saya menggunakan user defined variable untuk menyimpan nilai (kolom) saldo. tapi berhabung kata rekan kerja saya, dia menginginkan querynya tetep kompatible dengan jasperreport, maka ada sedikit modifikasi untuk inisialisasi user defined variable di mysqlnya.

berikut samplenya querynya:

SELECT     iddinamic_variable,
(if(col_int>=0,col_int,”)) as masuk,
(if(col_int<0,col_int,”)) as keluar,
(@count := @count + col_int) as saldo
FROM tester.dinamic_variable
left join (select @count:=0) as a on 1=1;

[MySQL] join bercabang, conditional join.

SELECT m_inout.m_inout_id,
c_order.document_no so,
remark,
partner_name
FROM m_inout
LEFT JOIN c_order
on m_inout.c_order_id = c_order.c_order_id
LEFT JOIN c_bpartner
on if(isnull(m_inout.c_order_id),
c_bpartner.c_bpartner_id = m_inout.c_bpartner_id,
c_bpartner.c_bpartner_id = c_order.c_bpartner_id)
WHERE m_inout.m_transaction_type_id = 3
AND m_inout.app_org_id = ‘5’;