分类帐报表逻辑在存储过程中

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.

我有一个叫做“病人分类帐报告”的存储过程，我需要显示每天的交易细节和病人的余额。我从下面的代码中提供了一个抽样数据如何将数据插入到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.

3 个解决方案

#1

I have tried like below it may help you

我已经试过下面的方法来帮助你。

SELECT Patient_nbr,
       billno,
       billamount,
       PAID_AMOUNT,
       CASE
         WHEN RNO > 1 THEN Sum(billamount - PAID_AMOUNT)
                             OVER(
                               PARTITION BY Patient_nbr
                               ORDER BY RNO)
         ELSE Iif(( billamount - PAID_AMOUNT ) < 0, 0, billamount - PAID_AMOUNT)
       END
FROM   (SELECT *,
               Row_number()
                 OVER(
                   PARTITION BY Patient_nbr
                   ORDER BY Patient_nbr) AS RNO
        FROM   #Patient_ledger) A

#2

If you are able to put there also order discriminator, it could seems like this: (I consider also fact that there can be more DUE/DEPOSITS for one PATIENT_NBR)

如果你也可以在这里放置一个order discriminator，它看起来可能是这样的:(我还考虑到，一个病人可以有更多的到期/存款)

IF OBJECT_ID('tempdb..#Patient_ledger') IS NOT NULL DROP TABLE #Patient_ledger
    CREATE TABLE #Patient_ledger 
    (ID INT IDENTITY, 
     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


;WITH CTE AS (
SELECT PATIENT_NBR, 
       BILLNO,
       PAID_AMOUNT,
       BILLAMOUNT,
       BILLAMOUNT-PAID_AMOUNT AS BALANCE, 
       ROW_NUMBER() OVER (PARTITION BY PATIENT_NBR ORDER BY ID) AS RN
FROM #Patient_ledger)

SELECT a.PATIENT_NBR,
       a.BILLNO,
       a.BILLAMOUNT,
       a.PAID_AMOUNT,
       CASE WHEN ISNULL(LAG(a.BALANCE + ISNULL(x.ADDS,0)) OVER (PARTITION BY a.PATIENT_NBR ORDER BY a.RN),0) + a.BILLAMOUNT - a.PAID_AMOUNT < 0 
            THEN 0
            ELSE a.BALANCE + ISNULL(x.ADDS,0)
       END AS FINAL_BALANCE

FROM CTE a
CROSS APPLY (SELECT SUM(BALANCE) AS ADDS 
                FROM CTE f 
                    WHERE f.PATIENT_NBR = a.PATIENT_NBR AND f.RN < a.RN) x

#3

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

#1