Link to home
Get AccessLog in
Avatar of Jeff S
Jeff SFlag for United States of America

asked on

Help with a SQL Query - DELETE from result set

I am trying to delete out Guarantors that have made a payment to the Medical Practice within the last 90 days, so collection letters are not sent on these individuals. In my coding below, I have the logic, however my tired eyes have not found where I missed something. In my demo db, I posted a payment for today on one of my accounts and when I ran my query, I still got my Guarantor.

Ultimately, if they paid in last 90 days they don't get a letter, else they do and they need to return to this result set. Any help is appreciated.

SET NOCOUNT ON

CREATE TABLE #ppl
    (
      GuarantorId INT ,
      GuarantorName VARCHAR(90) ,
      PatientBalance MONEY ,
      TotalAmount MONEY ,
      MonthlyPayment MONEY ,
      PlanStart DATETIME ,
      PlanAgreementSigned DATETIME ,
      Address1 VARCHAR(50) ,
      Address2 VARCHAR(50) ,
      City VARCHAR(30) ,
      State VARCHAR(3) ,
      Zip VARCHAR(10) ,
      GuarantorPhone1 VARCHAR(25) ,
      GuarantorPhone2 VARCHAR(25) ,
      BillCode VARCHAR(60)
    )
    
CREATE TABLE #GPAll
    (
      GuarantorId INT NULL ,
      PaymentDate DATETIME NOT NULL ,
      Amount MONEY NULL
    ) 
	
CREATE TABLE #C
    (
      GuarantorId INT NULL ,
      LastPaymentDate DATETIME NULL ,
      PaymentAge INT NULL ,
      Amount MONEY NULL
    )   
    
INSERT  #GPAll
        SELECT
            px.GuarantorId ,
            px.PaymentDate AS PaymentDate ,
            SUM(px.Amount) AS Amount
        FROM
            ( SELECT
                gr.GuarantorId ,
                b.Entry AS PaymentDate ,
                pm.Amount
              FROM
                PaymentMethod pm
                INNER JOIN Batch b ON pm.BatchId = b.BatchId
                INNER JOIN patientprofile pp ON pm.PayerId = pp.PatientProfileId
                INNER JOIN Guarantor gr ON pp.GuarantorId = gr.GuarantorId
              WHERE
                pm.PayerType = 'Patient'
                AND pm.Amount <> 0
              UNION ALL
              SELECT
                gr.GuarantorId ,
                b.Entry AS PaymentDate ,
                pm.Amount
              FROM
                PaymentMethod pm
                INNER JOIN Batch b ON pm.BatchId = b.BatchId
                INNER JOIN Guarantor gr ON pm.PayerId = gr.GuarantorId
              WHERE
                pm.PayerType = 'Guarantor'
                AND pm.Amount <> 0 ) AS px
        GROUP BY
            GuarantorId ,
            PaymentDate
        ORDER BY
            GuarantorId

INSERT  #C
        SELECT
            gp.GuarantorId ,
            gp.PaymentDate AS LastPaymentDate ,
            DATEDIFF(day , gp.PaymentDate , GETDATE()) AS PaymentAge ,
            gp.Amount
        FROM
            #GPAll gp
        WHERE
            gp.PaymentDate = ( SELECT MAX (PaymentDate) FROM #GPAll gpm WHERE gp.GuarantorId = gpm.GuarantorId )
        ORDER BY
            GuarantorId

-- Delete any guarantor where payment made in last 90 days.
IF 1 = 1 
    BEGIN
        DECLARE
            @pGuarantorID INT ,
            @tGuarantorID INT

        DECLARE x CURSOR
        FOR
            SELECT DISTINCT
                GuarantorID
            FROM
                #C
            UNION
            SELECT DISTINCT
                GuarantorID
            FROM
                #GPAll

        OPEN x
        FETCH NEXT FROM x INTO @pGuarantorID

        WHILE @@FETCH_STATUS = 0 
            BEGIN

                SELECT
                    @tGuarantorID = NULL

                SELECT TOP 1
                    @tGuarantorID = pp.GuarantorID
                FROM
                    VisitTransactions vt
                    JOIN PaymentMethod pm ON vt.PaymentMethodID = pm.PaymentMethodID
                    JOIN PatientVisit pv ON vt.PatientVisitID = pv.PatientVisitID
                    JOIN PatientProfile pp ON pv.PatientProfileID = pp.PatientProfileID
                WHERE
                    pm.Source = 1
                    AND DATEDIFF(dd , pm.DateofEntry , GETDATE()) < 90
                    AND GuarantorID = @pGuarantorID

                IF @tGuarantorID IS NOT NULL 
                    BEGIN
                        DELETE FROM
                            #C
                        WHERE
                            GuarantorID = @pGuarantorID
                        DELETE FROM
                            #GPAll
                        WHERE
                            GuarantorID = @pGuarantorID
                    END

                FETCH NEXT FROM x INTO @pGuarantorID
            END
        CLOSE x
        DEALLOCATE x
    END

-- End of Last Payment Deletion    

CREATE TABLE #Bill ( GuarantorId INT )
	
INSERT  #Bill
        SELECT DISTINCT
            GuarantorID
        FROM
            PaymentPlan pp
        WHERE
            ISNULL(pp.Active , 0) = 1
	
DECLARE guarantors CURSOR
FOR
    SELECT DISTINCT
        g.GuarantorID
    FROM
        Guarantor g
        INNER JOIN #Bill b ON g.GuarantorId = b.GuarantorId
        INNER JOIN ( SELECT
                        GuarantorId ,
                        SUM(ppa.PatBalance) Balance
                     FROM
                        PatientProfile pp
                        INNER JOIN PatientProfileAgg ppa ON pp.PatientProfileId = ppa.PatientProfileId
                     WHERE
                        Balance > 0
                     GROUP BY
                        GuarantorId ) gp ON g.GuarantorId = gp.GuarantorId
	
DECLARE @guarantorId INTEGER
DECLARE @patBalance MONEY

OPEN guarantors

FETCH NEXT FROM guarantors INTO @guarantorId
WHILE @@FETCH_STATUS = 0 
    BEGIN
	-- retrieve the current outstanding patient balance for the guarantor
        SELECT
            @patBalance = SUM(ppa.PatBalance)
        FROM
            PatientProfileAgg ppa
            INNER JOIN PatientProfile pp ON pp.PatientProfileId = ppa.PatientProfileId
        WHERE
            pp.GuarantorId = @guarantorId	
            
        INSERT  INTO #ppl
                (
                  GuarantorId ,
                  GuarantorName ,
                  PatientBalance ,
                  TotalAmount ,
                  MonthlyPayment ,
                  PlanStart ,
                  PlanAgreementSigned ,
                  Address1 ,
                  Address2 ,
                  City ,
                  State ,
                  Zip ,
                  GuarantorPhone1 ,
                  GuarantorPhone2 ,
                  BillCode 
                )
                SELECT
                    g.GuarantorId ,
                    dbo.FormatName(g.Prefix , g.First , g.Middle , g.Last , g.Suffix) AS GuarantorName ,
                    @patBalance PatientBalance ,
                    ppn.TotalAmount ,
                    ppn.MonthlyPayment ,
                    CONVERT(VARCHAR(10) , ppn.PlanStart , 101) AS PlanStart ,
                    CONVERT(VARCHAR(10) , ppn.DateSigned , 101) AS PlanAgreementSigned ,
                    g.Address1 ,
                    g.Address2 ,
                    g.City ,
                    g.State ,
                    g.Zip ,
                    dbo.FormatPhone(g.Phone1 , 1) GuarantorPhone1 ,
                    dbo.FormatPhone(g.Phone2 , 1) GuarantorPhone2 ,
                    bcml.Description AS BillCode
                FROM
                    Guarantor g
                    INNER JOIN #Bill b ON g.GuarantorId = b.GuarantorId
                    INNER JOIN PaymentPlan ppn ON b.GuarantorId = ppn.GuarantorId
                    LEFT JOIN MedLists bcml ON g.BillCodeMId = bcml.MedListsId
                WHERE
                    g.GuarantorId = @guarantorId
                    AND ISNULL(ppn.Active , 0) = 1
	
        FETCH NEXT FROM guarantors INTO @guarantorId
    END
CLOSE Guarantors
DEALLOCATE Guarantors	

SELECT
    GuarantorId ,
    GuarantorName ,
    PatientBalance ,
    MonthlyPayment ,
    ROUND(PatientBalance / MonthlyPayment , 0) AS PaymentsRemaining ,
    ISNULL(CONVERT(VARCHAR(20) , PlanStart , 101) , '') AS PlanStart ,
    ISNULL(CONVERT(VARCHAR(20) , PlanAgreementSigned , 101) , '') AS PlanAgreementSigned ,
    Address1 ,
    Address2 ,
    City ,
    State ,
    Zip ,
    GuarantorPhone1 ,
    GuarantorPhone2 ,
    BillCode
FROM
    #ppl
ORDER BY
    2
	
DROP TABLE #Bill
DROP TABLE #ppl 
DROP TABLE #GPAll
DROP TABLE #C

Open in new window

Avatar of nishant joshi
nishant joshi
Flag of India image

might problem with your define of cursor you have to union then and use distinct after that,
dont use distinct before union.

Thanks..
Did not look into the cause of your problem. First view of code let me think that's a lot of usage of #tables,  cursors, sort in insert statements that all will have bad influence on performance.

That first cursor is that producing anything that is used later on?
ASKER CERTIFIED SOLUTION
Avatar of jogos
jogos
Flag of Belgium image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Avatar of Jeff S

ASKER

Thanks, I went another route altogether, but thanks for looking.