Ledger Report Logic in Stored Procedure

问题:

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.

回答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)

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