vendor_evelation.sql

工厂采购系统,DELPHI+SQL SERVER,三层技术

select a.pono,a.item,max(a.deli_day) as deliv_dt,
sum(a.quantity) as Ord_Qty,
sum(a.Received_qty) as Rcv_Qty,
sum(a.Balance_qty) as Bal_Qty,
max(b.in_date) as GR_date
 into #Test from ord_item a,gtr_item b 
where a.pono = b.pono and a.item = b.item
group by a.pono,a.item
go

select a.id,b.item,convert(int,(b.GR_Date-b.deliv_dt)) as Delay_day,
b.deliv_dt,
b.GR_Date,b.Ord_Qty,b.Rcv_Qty,b.Bal_Qty
into #test_01 
from  ord_mst a, #test  b where a.pono = b.pono
and (convert(int,(b.GR_Date-b.deliv_dt))>0) and (ord_qty=rcv_qty)
go


select a.id,b.item,convert(int,(b.GR_Date-b.deliv_dt)) as Delay_day,
b.deliv_dt,
b.GR_Date,b.Ord_Qty,b.Rcv_Qty,b.Bal_Qty
into #test_02 from  ord_mst a, #test  b where a.pono = b.pono
and (convert(int,(b.GR_Date-b.deliv_dt))<=0) and (ord_qty>rcv_qty)
go

select a.id,sum(a.Ord_Qty) as Order_Qty,sum(b.Bal_Qty) as Outstd_Qty,
delay_rate =convert(varchar(50),isnull(sum(b.bal_Qty)/sum(a.ord_qty),0)*100)+' %'
from #test_01 a, #test_02 b
where a.id = b.id and a.item = b.item  
group by a.id