Berikut ini cara menghitung Stock Awal dan Stock Akhir per Periode Tanggal tertentu. Contoh pada kasus kali ini adalah menghitung Stock Awal dan Stock Akhir Per Tanggal 1 Juni 2020 dengan Table Transaksi seperti berikut ini :
QUERY :
Create Table Trans(
id int identity(1,1) primary key ,
DN varchar(20) not null,
PN varchar(20) not null,
transdate date,
qtyin int not null default 0,
qtyout int not null default 0,
)
Insert Into Trans (DN,PN,transdate,qtyin,qtyout) values
('DN001','PN001','2020-06-01',10,0),
('DN002','PN001','2020-06-02',0,5),
('DN003','PN001','2020-06-03',10,0),
('DN001','PN002','2020-06-01',20,0),
('DN002','PN002','2020-06-02',0,10),
('DN003','PN002','2020-06-03',0,5),
('DN001','PN003','2020-06-01',10,0),
('DN002','PN003','2020-06-02',10,0),
('DN003','PN003','2020-06-03',10,0),
('DN004','PN003','2020-06-04',10,0)
Select A.PN,ISNULL(sum(A.total),0) as StockAwal, ISNULL(sum(B.qtyin),0) as qtyin,ISNULL(sum(B.qtyout),0) as qtyout, ISNULL(sum(A.total),0) + ISNULL(sum(B.qtyin),0)-ISNULL(sum(B.qtyout),0) as StockAkhir
from
(
select PN,SUM(qtyin) as qtyin,SUM(qtyout) as qtyout, SUM(qtyin)-SUM(qtyout) as total from Trans Where transdate<='2020-06-01'
Group by PN
) as A
LEFT join
(
select PN,SUM(qtyin) as qtyin,SUM(qtyout) as qtyout, SUM(qtyin)-SUM(qtyout) as total from Trans Where transdate>='2020-06-02' and transdate<='2020-06-04'
Group by PN
) as B ON B.PN=A.PN
group by A.PN
Hasilnya :
EmoticonEmoticon