Help with a SQL Query - DELETE from result set

Jeff S
Jeff S used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
nishant joshiTechnology Development Consultant

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

Thanks..

Commented:
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?
Commented:
Think that this gives about same result as all of your code. And even this can be simplified
- without the #ppl
- don't know if the (ISNULL(ppn.Active , 0) = 1) makes a difference in your result if it's done on the all results of only for the exists()

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

       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
-- Replace #Bill with what its origin included the sum-balance
                    INNER JOIN ( SELECT
                        GuarantorId ,
                        SUM(ppa.PatBalance) Balance
                     FROM
                        PatientProfile pp
                        INNER JOIN PatientProfileAgg ppa ON pp.PatientProfileId = ppa.PatientProfileId
                     WHERE
                        Balance > 0
                       AND  exists (select 1 from patientProfile pp1 
                                    where pp1.GuarantorId = pp.GuarantorId  
                                     AND ISNULL(pp.Active , 0) = 1
                     GROUP BY
                        GuarantorId )  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 ISNULL(ppn.Active , 0) = 1

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

Open in new window


And for the fun compare read/writes/cpu and duration for the 2 solutions.

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial