Read 244 times | Created 2013-10-09 04:17:25 | Updated 2013-10-09 04:23:27 | | |

 

-- ------
use test;

DROP TABLE IF EXISTS `tbl_bongkar`; 
CREATE TABLE IF NOT EXISTS `tbl_bongkar` ( 
  `jenis` VARCHAR(25)NOT NULL, 
  `tgl` DATE NOT NULL,
  `jumlah` INT(11) NOT NULL
) ENGINE=MyISAM  DEFAULT CHARSET=utf8; 

INSERT INTO tbl_bongkar
VALUES
('Tepung terigu','2013-10-01',20),
('Tepung kanji','2013-10-01',30),
('Tepung maizena','2013-10-02',40),
('Tepung maizena','2013-10-03',25),
('Tepung kanji','2013-10-04',20);

SELECT * FROM tbl_bongkar;

DROP TABLE IF EXISTS `tbl_kemas`; 
CREATE TABLE IF NOT EXISTS `tbl_kemas` ( 
  `jenis` VARCHAR(25)NOT NULL, 
  `tgl` DATE NOT NULL,
  `jumlah` INT(11) NOT NULL
) ENGINE=MyISAM  DEFAULT CHARSET=utf8; 

INSERT INTO tbl_kemas
VALUES
('Tepung terigu','2013-10-01',10),
('Tepung kanji','2013-10-01',20),
('Tepung kanji','2013-10-03',10),
('Tepung maizena','2013-10-03',10);

SELECT * FROM tbl_kemas;

DROP TABLE IF EXISTS `tbl_muat`; 
CREATE TABLE IF NOT EXISTS `tbl_muat` ( 
  `jenis` VARCHAR(25)NOT NULL, 
  `tgl` DATE NOT NULL,
  `jumlah` INT(11) NOT NULL
) ENGINE=MyISAM  DEFAULT CHARSET=utf8; 

INSERT INTO tbl_muat
VALUES
('Tepung kanji','2013-10-02',10),
('Tepung terigu','2013-10-02',10),
('Tepung maizena','2013-10-04',5);

SELECT * FROM tbl_muat;

 SELECT 
  a.jenis,
  SUM(a.jumlah) AS jml_bongkar
 FROM 
  tbl_bongkar a
 WHERE 
  a.tgl BETWEEN '2013-10-01' AND '2013-10-03'
 GROUP BY
  a.jenis;

 SELECT 
  b.jenis,
  SUM(b.jumlah) AS jml_kemas
 FROM 
  tbl_kemas b
 WHERE 
  b.tgl BETWEEN '2013-10-01' AND '2013-10-03'
 GROUP BY
  b.jenis;
  
 SELECT 
  c.jenis,
  SUM(c.jumlah) AS jml_muat
 FROM 
  tbl_muat c
 WHERE 
  c.tgl BETWEEN '2013-10-01' AND '2013-10-03'
 GROUP BY
  c.jenis;
  
 
SELECT
  a.jenis,
  a.jml_bongkar,
  b.jml_kemas,
  IF(c.jml_muat IS NULL,'',c.jml_muat) AS jml_muat
FROM
 (SELECT 
    jenis,
    SUM(jumlah) AS jml_bongkar 
  FROM tbl_bongkar 
  WHERE 
    tgl BETWEEN '2013-10-01' AND '2013-10-03' 
  GROUP BY jenis) a 
 LEFT JOIN (
  SELECT 
    jenis,
    SUM(jumlah) AS jml_kemas 
  FROM tbl_kemas 
  WHERE 
    tgl BETWEEN '2013-10-01' AND '2013-10-03' 
  GROUP BY jenis) b  USING(jenis)
 LEFT JOIN (
  SELECT 
    jenis,
    SUM(jumlah) AS jml_muat 
  FROM tbl_muat 
  WHERE
    tgl BETWEEN '2013-10-01' AND '2013-10-03' 
  GROUP BY jenis) c  USING(jenis);

-- ***************************************************
mysql> use test;
Database changed
mysql> DROP TABLE IF EXISTS `tbl_bongkar`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `tbl_bongkar` (
    ->   `jenis` VARCHAR(25)NOT NULL,
    ->   `tgl` DATE NOT NULL,
    ->   `jumlah` INT(11) NOT NULL
    -> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO tbl_bongkar
    -> VALUES
    -> ('Tepung terigu','2013-10-01',20),
    -> ('Tepung kanji','2013-10-01',30),
    -> ('Tepung maizena','2013-10-02',40),
    -> ('Tepung maizena','2013-10-03',25),
    -> ('Tepung kanji','2013-10-04',20);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tbl_bongkar;
+----------------+------------+--------+
| jenis          | tgl        | jumlah |
+----------------+------------+--------+
| Tepung terigu  | 2013-10-01 |     20 |
| Tepung kanji   | 2013-10-01 |     30 |
| Tepung maizena | 2013-10-02 |     40 |
| Tepung maizena | 2013-10-03 |     25 |
| Tepung kanji   | 2013-10-04 |     20 |
+----------------+------------+--------+
5 rows in set (0.00 sec)

mysql> DROP TABLE IF EXISTS `tbl_kemas`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `tbl_kemas` (
    ->   `jenis` VARCHAR(25)NOT NULL,
    ->   `tgl` DATE NOT NULL,
    ->   `jumlah` INT(11) NOT NULL
    -> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO tbl_kemas
    -> VALUES
    -> ('Tepung terigu','2013-10-01',10),
    -> ('Tepung kanji','2013-10-01',20),
    -> ('Tepung kanji','2013-10-03',10),
    -> ('Tepung maizena','2013-10-03',10);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tbl_kemas;
+----------------+------------+--------+
| jenis          | tgl        | jumlah |
+----------------+------------+--------+
| Tepung terigu  | 2013-10-01 |     10 |
| Tepung kanji   | 2013-10-01 |     20 |
| Tepung kanji   | 2013-10-03 |     10 |
| Tepung maizena | 2013-10-03 |     10 |
+----------------+------------+--------+
4 rows in set (0.00 sec)

mysql> DROP TABLE IF EXISTS `tbl_muat`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `tbl_muat` (
    ->   `jenis` VARCHAR(25)NOT NULL,
    ->   `tgl` DATE NOT NULL,
    ->   `jumlah` INT(11) NOT NULL
    -> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> INSERT INTO tbl_muat
    -> VALUES
    -> ('Tepung kanji','2013-10-02',10),
    -> ('Tepung terigu','2013-10-02',10),
    -> ('Tepung maizena','2013-10-04',5);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tbl_muat;
+----------------+------------+--------+
| jenis          | tgl        | jumlah |
+----------------+------------+--------+
| Tepung kanji   | 2013-10-02 |     10 |
| Tepung terigu  | 2013-10-02 |     10 |
| Tepung maizena | 2013-10-04 |      5 |
+----------------+------------+--------+
3 rows in set (0.00 sec)

mysql> SELECT
    ->   a.jenis,
    ->   SUM(a.jumlah) AS jml_bongkar
    -> FROM
    ->   tbl_bongkar a
    -> WHERE
    ->   a.tgl BETWEEN '2013-10-01' AND '2013-10-03'
    -> GROUP BY
    ->   a.jenis;
+----------------+-------------+
| jenis          | jml_bongkar |
+----------------+-------------+
| Tepung kanji   |          30 |
| Tepung maizena |          65 |
| Tepung terigu  |          20 |
+----------------+-------------+
3 rows in set (0.00 sec)

mysql> SELECT
    ->    b.jenis,
    ->    SUM(b.jumlah) AS jml_kemas
    ->  FROM
    ->    tbl_kemas b
    ->  WHERE
    ->    b.tgl BETWEEN '2013-10-01' AND '2013-10-03'
    ->  GROUP BY
    ->    b.jenis;
+----------------+-----------+
| jenis          | jml_kemas |
+----------------+-----------+
| Tepung kanji   |        30 |
| Tepung maizena |        10 |
| Tepung terigu  |        10 |
+----------------+-----------+
3 rows in set (0.00 sec)

mysql> SELECT
    ->   c.jenis,
    ->   SUM(c.jumlah) AS jml_muat
    -> FROM
    ->   tbl_muat c
    -> WHERE
    ->   c.tgl BETWEEN '2013-10-01' AND '2013-10-03'
    -> GROUP BY
    ->   c.jenis
    -> ;
+---------------+----------+
| jenis         | jml_muat |
+---------------+----------+
| Tepung kanji  |       10 |
| Tepung terigu |       10 |
+---------------+----------+
2 rows in set (0.00 sec)

mysql> SELECT
    ->   a.jenis,
    ->   a.jml_bongkar,
    ->   b.jml_kemas,
    ->   IF(c.jml_muat IS NULL,'',c.jml_muat) AS jml_muat
    -> FROM
    ->  (SELECT
    ->     jenis,
    ->     SUM(jumlah) AS jml_bongkar
    ->   FROM tbl_bongkar
    ->   WHERE
    ->     tgl BETWEEN '2013-10-01' AND '2013-10-03'
    ->   GROUP BY jenis) a
    ->  LEFT JOIN (
    ->   SELECT
    ->     jenis,
    ->     SUM(jumlah) AS jml_kemas
    ->   FROM tbl_kemas
    ->   WHERE
    ->     tgl BETWEEN '2013-10-01' AND '2013-10-03'
    ->   GROUP BY jenis) b  USING(jenis)
    ->  LEFT JOIN (
    ->   SELECT
    ->     jenis,
    ->     SUM(jumlah) AS jml_muat
    ->   FROM tbl_muat
    ->   WHERE
    ->     tgl BETWEEN '2013-10-01' AND '2013-10-03'
    ->   GROUP BY jenis) c  USING(jenis);
+----------------+-------------+-----------+----------+
| jenis          | jml_bongkar | jml_kemas | jml_muat |
+----------------+-------------+-----------+----------+
| Tepung kanji   |          30 |        30 | 10       |
| Tepung maizena |          65 |        10 |          |
| Tepung terigu  |          20 |        10 | 10       |
+----------------+-------------+-----------+----------+
3 rows in set (0.00 sec)