cara menampilkan transaksi berdasarkan bulan

51 views
Skip to first unread message

indigo carmine

unread,
Jan 20, 2013, 1:19:34 AM1/20/13
to belaj...@googlegroups.com
permisi admin n member,saya ingin bertanya bagaimana menampilkan jumlah transaksi berdasarkan bulan pada database phi_minimart yang kebetulan disana transaksinya hanya dibulan januari,bagaimana jika ada bulan lainnya,jadi output yang saya inginkan nantinya,,

============================
|| Bulan               || Jumlah transaksi ||
============================
|| Januari             ||           20            ||
|| Februari            ||            1            ||
|| Maret                ||            0           ||
|| April                 ||           32           ||
===========================

dst,,,
mohon bantuan querynya,,jika tidak bisa mohon diberikan solusi yang mendekati output yg saya minta :)
terima kasi,,

bubu neko

unread,
Dec 6, 2018, 10:24:54 PM12/6/18
to Belajar SQL
select d.kode_barang,d.kode_jenis_barang,d.edisi   ,sum(a.jumlah_pengambilan) as total,
sum(case when date(a.user_date_create) between '2018-01-01' and '2018-01-31' and c.kode_masa_ba=20181 then a.jumlah_pengambilan else 0 end) as bulan_01,
sum(case when date(a.user_date_create) between '2018-02-01' and '2018-02-31' and c.kode_masa_ba=20181 then a.jumlah_pengambilan else 0 end) as bulan_02,
sum(case when date(a.user_date_create) between '2018-03-01' and '2018-03-31' and c.kode_masa_ba=20181 then a.jumlah_pengambilan else 0 end) as bulan_03,
sum(case when date(a.user_date_create) between '2018-04-01' and '2018-04-31' and c.kode_masa_ba=20181 then a.jumlah_pengambilan else 0 end) as bulan_04,
sum(case when date(a.user_date_create) between '2018-05-01' and '2018-05-31' and c.kode_masa_ba=20181 then a.jumlah_pengambilan else 0 end) as bulan_05,
sum(case when date(a.user_date_create) between '2018-06-01' and '2018-06-31' and c.kode_masa_ba=20181 then a.jumlah_pengambilan else 0 end) as bulan_06,
sum(case when date(a.user_date_create) between '2018-07-01' and '2018-07-31' and c.kode_masa_ba=20182 then a.jumlah_pengambilan else 0 end) as bulan_07,
sum(case when date(a.user_date_create) between '2018-08-01' and '2018-08-31' and c.kode_masa_ba=20182 then a.jumlah_pengambilan else 0 end) as bulan_08,
sum(case when date(a.user_date_create) between '2018-09-01' and '2018-09-31' and c.kode_masa_ba=20182 then a.jumlah_pengambilan else 0 end) as bulan_09,
sum(case when date(a.user_date_create) between '2018-10-01' and '2018-10-31' and c.kode_masa_ba=20182 then a.jumlah_pengambilan else 0 end) as bulan_10,
sum(case when date(a.user_date_create) between '2018-11-01' and '2018-11-31' and c.kode_masa_ba=20182 then a.jumlah_pengambilan else 0 end) as bulan_11,
sum(case when date(a.user_date_create) between '2018-12-01' and '2018-12-31' and c.kode_masa_ba=20182 then a.jumlah_pengambilan else 0 end) as bulan_12
from t_mutasi_do a 
join t_do_detail b on a.nomor_do=b.nomor_do and a.kode_masa_ba=b.kode_masa_ba and a.kode_paket=b.kode_paket
join t_do_header c on b.nomor_do=c.nomor_do and b.nomor_pemesanan=c.nomor_pemesanan and b.masa_pemesanan=c.masa_pemesanan
join t_paket_detail d on b.kode_paket=d.kode_paket
where a.kode_masa_ba=20181 or a.kode_masa_ba=20182  
group by d.kode_barang,d.kode_jenis_barang,d.edisi
Reply all
Reply to author
Forward
0 new messages