得到适当数量的供应商 [英] get due amount of suppliers

问题描述

hi

我有2张桌子...

首先...
tbl_Purchase

hi

i have 2 table...

first...
tbl_Purchase

Purchase_id	bigint	
Location_Id	bigint	
PU_Ref	nvarchar(50)	
InvoiceDate	datetime	
Original_Amt	decimal(18, 2)	
Supplier_ID	bigint	
Status	int

第二
tbl_pur_Child

and second
tbl_pur_Child

s_N	bigint	
Purchase_ID	bigint	
Paid_amt	decimal(18, 2)	
Location_Id	bigint	
InvoiceDate	datetime	

我要通过供应商ID时要
在存储过程中

所以
它返回
PU_Ref，Original_Amt，(颗粒供应商ID的Original_Amt -sum(Paid_amt)...

1个供应商可以拥有1个以上的发票....

我写了这个存储过程...

i want when i pass supplier id
in store procedure

so
it return
PU_Ref , Original_Amt , ( Original_Amt -sum(Paid_amt ) of particule supplier id...

1 supplier may can have more then 1 invoices....

i write this store procedure...

ALTER procedure [dbo].[SP_Chk_Purchase_Bal_By_SupID]
 @supid int
as begin
 
select PU_Ref ,Original_Amt, (Original_Amt-
(select sum(rf.Paid_amt) from tbl_pur_Child as rf join
 
tbl_Purchase as pu  on
rf.Purchase_ID=pu.Purchase_id ))
from tbl_Purchase where Supplier_ID=@supid
 
end

但是它没有给出正确的结果……

but it not giving right result...............

推荐答案

您可以尝试以下方法:

you can try something like:

ALTER procedure [dbo].[SP_Chk_Purchase_Bal_By_SupID]
 @supid int
as begin

 declare @amt_paid float


select @amt_paid = sum(rf.Paid_amt) from tbl_pur_Child as rf join
tbl_Purchase as pu  on
rf.Purchase_ID=pu.Purchase_id where Supplier_ID=@supid

select
    PU_Ref ,
    Original_Amt,
    (Original_Amt- @amt_paid)
from tbl_Purchase where Supplier_ID=@supid

end

这篇关于得到适当数量的供应商的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！