Tanaman

SELECT
  a.nama_tanaman,
  a.stok
    +SUM(
      IF(
        DATE_FORMAT(c.tgl_tanaman_masuk,'%Y%m')<=DATE_FORMAT($bulanlalu,'%Y%m'),
        jumlah_tanaman_masuk,
        0
      )
    )
    -SUM(
      IF(
        DATE_FORMAT(d.tgl_pengurangan_tanaman,'%Y%m')<=DATE_FORMAT($bulanlalu,'%Y%m'),
        jumlah_pengurangan_tanaman,
        0
      )
    ) AS bulanlalu,
  c.tgl_tanaman_masuk,
  c.asal,
  SUM(
    IF(
      DATE_FORMAT(c.tgl_tanaman_masuk,'%Y%m')=DATE_FORMAT($bulanini,'%Y%m'),
      jumlah_tanaman_masuk,
      0
    )
  ) AS jumlah,
  SUM(
    IF(
        (
          DATE_FORMAT(d.tgl_pengurangan_tanaman,'%Y%m')=DATE_FORMAT($bulanini,'%Y%m')
          AND d.jenis_pengurangan='mati'
        ),
        jumlah_pengurangan_tanaman,
        0
       )
     ) AS mati,
  SUM(
    IF(
        (
          DATE_FORMAT(d.tgl_pengurangan_tanaman,'%Y%m')=DATE_FORMAT($bulanini,'%Y%m')
          AND d.jenis_pengurangan='distribusi'
        ),
        jumlah_pengurangan_tanaman,
        0
       )
     ) AS distribusi,
  a.stok
    +SUM(
      IF(
        DATE_FORMAT(c.tgl_tanaman_masuk,'%Y%m')=DATE_FORMAT($bulanini,'%Y%m'),
        jumlah_tanaman_masuk,
        0
      )
    )
    -SUM(
      IF(
        DATE_FORMAT(d.tgl_pengurangan_tanaman,'%Y%m')=DATE_FORMAT($bulanini,'%Y%m'),
        jumlah_pengurangan_tanaman,
        0
      )
    )AS total_bulanini,
FROM tanaman a
  JOIN jenis__tanaman b USING(id_jenis_tanaman)
  JOIN tanaman_masuk c USING(id_tanaman)
  JOIN pengurangan_tanaman d USING(id_tanaman)
GROUP BY a.id_tanaman