Avatar of Jeff S
Jeff S
Flag 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

Microsoft SQL Server 2008Microsoft SQL Server 2005Microsoft SQL Server

Avatar of undefined
Last Comment
Jeff S

8/22/2022 - Mon
nishant joshi

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

Thanks..
jogos

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
jogos

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jeff S

ASKER
Thanks, I went another route altogether, but thanks for looking.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes