存储过程中的账本报表逻辑 [英] Ledger Report Logic in Stored Procedure

问题描述

我有一个名为患者分类帐报告"的存储过程，我需要在其中显示患者的日常交易详细信息和余额.我从下面的代码中为您提供了一个样本数据，这些数据是如何插入到我的临时数据中的我的 sp. 中的表.

i have a Stored Procedure called "Patient Ledger Report" where i need to show the day to day transaction details and balance amount of the patients.i was providing you one sampled data from below code how the data was inserting into my temporary table in my 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

我希望如何在报告中显示数据.

How i want to show the data in my report.

 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.

推荐答案

试试这个，告诉我它是否也适用于其他示例数据.

Try this and tell me if it work with other sample data too.

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,40
UNION ALL
select 'XYZ',2,'DEPOSIT_BILL_XYZ',0,70
UNION ALL
select 'XYZ',2,'DUE_BILL_XYZ_1',100,30


SELECT PATIENT_NBR PATIENT_NUMBER
    ,BILLNO
    ,BILLAMOUNT
    ,PAID_AMOUNT
    ,CASE 
        WHEN billamount = 0
            AND lag((BILLAMOUNT - PAID_AMOUNT), 1, 0) OVER (
                PARTITION BY PATIENT_NBR ORDER BY PATIENT_NBR
                ) = 0
            THEN 0
        ELSE SUM((BILLAMOUNT - PAID_AMOUNT)) OVER (
                PARTITION BY PATIENT_NBR ORDER BY PATIENT_NBR ROWS UNBOUNDED PRECEDING
                )
        END Balance
FROM #Patient_ledger

Drop table #Patient_ledger

这篇关于存储过程中的账本报表逻辑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！