我有一个名为“患者分类帐报告”的存储过程,我需要在其中显示患者的日常交易详细信息和余额金额。我正在为您提供以下代码中的一个采样数据,这些数据是如何插入到我的临时表中的sp。
create table #Patient_ledger (PATIENT_NAME varchar(250),PATIENT_NBR bigint,BILLNO varchar(250),BILLAMOUNT bigint,
PAID_AMOUNT bigint)
Insert into #Patient_ledger (Patient_name ,Patient_nbr ,billno ,billamount ,
paid_amount )
select 'ABC',1,'DUE_BILL_ABC_1',100,50
union all
select 'ABC',1,'DUE_BILL_ABC_2',160,90
UNION ALL
select 'ABC',1,'DEPOSIT_BILL_ABC',0,60
UNION ALL
select 'XYZ',2,'DEPOSIT_BILL_XYZ',0,70
UNION ALL
select 'XYZ',2,'DUE_BILL_XYZ_1',100,30
SELECT * FROM #Patient_ledger
Drop table #Patient_ledger
我想如何在报告中显示数据。
PATIENT_NUMBER BILLNO BILLAMOUNT PAID_AMOUNT BALANCE
1 DUE_BILL_ABC_1 100 50 50 --(100-50)
1 DUE_BILL_ABC_2 160 90 120 --(160-90 +50(Here 50 is prev balance amount of same patient))
1 DEPOSIT_BILL_ABC 0 40 80 ---( 120-40=80)
2 DEPOSIT_BILL_XYZ 0 70 0
2 DUE_BILL_XYZ_1 100 30 0 --Here Balance is zero because patient has deposited some
--amount before bill (70-100+30=0)
Note: Balance amount should deduct when deposits are paid by that particual patient.