Jeff S
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.
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
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?
That first cursor is that producing anything that is used later on?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, I went another route altogether, but thanks for looking.
dont use distinct before union.
Thanks..