Scribd
Upload
31 views2 pages

SQL Query Script

The document contains SQL queries for various reports related to sales orders, delivery orders, and invoices. It includes calculations of employee performance against targets, amounts for delivery orders, aging of debts, product quantities sold, and vendor amounts. Each query is structured to extract specific data from multiple joined tables in a database.

Uploaded by

RadifanTaufikAce
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
31 views2 pages

SQL Query Script

The document contains SQL queries for various reports related to sales orders, delivery orders, and invoices. It includes calculations of employee performance against targets, amounts for delivery orders, aging of debts, product quantities sold, and vendor amounts. Each query is structured to extract specific data from multiple joined tables in a database.

Uploaded by

RadifanTaufikAce
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

-- NOMOR 1

select mp.nama_pegawai as "Employee",


count(no_so) as "Jumlah SO",
case
when count(no_so) >= [Link] then 'Tidak'
when count(no_so) < [Link] then 'Ya'
end "kurang dari target?"
from tr_so
left join master_pegawai mp
on mp.kode_pegawai = tr_so.kode_sales
group by nama_pegawai, kode_sales, [Link]
order by kode_sales

-- NOMOR 2
select a.no_do, a.tgl_do,[Link],
([Link]+[Link]+a.ongkos_kirim) as amount
from (
select no_do, tgl_do,([Link]*[Link]) as qty,
([Link]*[Link]*mpr.harga_satuan) as harga,
([Link]*[Link]*mpr.harga_satuan*10/100) as ppn,
mc.ongkos_kirim as ongkos_kirim
from tr_do as td
left join tr_so as ts
on ts.no_entry_so = td.no_entry_so
left join master_konversi as mk
on [Link]=[Link]
left join master_product as mpr
on mpr.kode_produk=ts.kode_barang
left join master_customer as mc
on mc.kode_customer=ts.kode_customer
) as a

-- NOMOR 3
select mc.nama_customer , td.no_do , td.tgl_do ,([Link]*[Link]) as qty,
(('2018/02/01'::date) - (td.tgl_do::date)) as "umur hutang"
from tr_inv ti
right join tr_do td
on td.no_entry_do = ti.no_entry_do
left join tr_so ts
on ts.no_entry_so = td.no_entry_so
left join master_konversi mk
on [Link] = [Link]
left join master_product mp
on mp.kode_produk = ts.kode_barang
left join master_customer mc
on mc.kode_customer = ts.kode_customer
where ti.no_entry_do isnull

-- NOMOR 4
select a.nama_product,
sum(a.qty_converted) as quantity
from (
select mp.kode_produk , mp.nama_product, [Link], [Link] as satuan_qty,
([Link]*[Link]) as qty_converted
from tr_so ts
left join master_product mp
on mp.kode_produk = ts.kode_barang
left join master_konversi mk
on [Link] = [Link]
) as a
group by a.nama_product
order by quantity desc limit 3;

-- NOMOR 5
select [Link] as "Nama Vendor", sum([Link]) as "Amount"
from (
select [Link],
([Link]*[Link]*mp.harga_satuan) as amount
from tr_inv ti
left join tr_do td
on td.no_entry_do = ti.no_entry_do
left join tr_so ts
on ts.no_entry_so = td.no_entry_so
left join master_product mp
on mp.kode_produk = ts.kode_barang
left join master_konversi mk
on [Link] = [Link]
left join master_vendor mv
on mv.kode_vendor = mp.kode_vendor
) as a
group by [Link]
order by "Amount" desc limit 3

You might also like