Solved

Microsoft, SQL, SQL 2005 - Query Assistance

Posted on 2007-11-15
5
193 Views
Last Modified: 2010-03-19
I cant find where I went wrong. I get the following error message:

Msg 16916, Level 16, State 1, Line 175
A cursor with the name 'c' does not exist.
/* Guarantor Collections report 

Revised 2/1/2007

Revised 2/13/2007

Revised 3/20/2007*/

SET NOCOUNT ON
 

IF ( SELECT OBJECT_ID('tempdb..#A','U') ) IS NOT NULL

 DROP TABLE #A

IF ( SELECT OBJECT_ID('tempdb..#B','U') ) IS NOT NULL

 DROP TABLE #B

IF ( SELECT OBJECT_ID('tempdb..#C','U') ) IS NOT NULL

 DROP TABLE #C

IF ( SELECT OBJECT_ID('tempdb..#GPAll','U') ) IS NOT NULL

 DROP TABLE #GPAll
 

CREATE TABLE #A

(GuarantorId int NOT NULL,

 TotalInsBalance money NULL,

 TotalPatBalance money NULL, 

 TotalBalance money NULL,

)

CREATE TABLE #B

(GuarantorId int NOT NULL,

 PatientProfileId int NOT NULL,

 InsBalance money NULL,

 PatBalance money NULL,

 Balance money NULL,

 Description varchar(255) NULL,

 PatientVisitId int NULL,

 VisitInsPayment money NULL,

 VisitPatPayment money NULL,

 VisitInsBalance money NULL,

 VisitPatBalance money NULL,

 CollectionsStatus varchar(255) NULL

)

CREATE TABLE #GPAll

( GuarantorId int NOT NULL,

	PaymentDate datetime NOT NULL,

	Amount money NULL

)

CREATE TABLE #C

(GuarantorId int NULL,

 LastPaymentDate datetime NULL,

 PaymentAge int NULL,

 Amount money NULL,

)
 

INSERT #A

SELECT gr.GuarantorId, 

	SUM(ppa.InsBalance) AS TotalInsBalance, 

	SUM(ppa.PatBalance) AS TotalPatBalance, 

	SUM(ppa.Balance) AS TotalBalance

FROM PatientProfile pp 

	INNER JOIN Guarantor gr ON pp.GuarantorId = gr.GuarantorId 

	INNER JOIN PatientProfileAgg ppa ON pp.PatientProfileId = ppa.PatientProfileId

WHERE

( 

 ('1267' IS NOT NULL AND gr.GuarantorId IN(1267)) 

 OR '1267' IS NULL 

)

GROUP BY gr.GuarantorId

ORDER BY gr.GuarantorId
 

INSERT #B

SELECT gr.GuarantorId AS GuarantorId, 

	pp.PatientProfileId AS PatientProfileId, 

	ppa.InsBalance AS InsBalance, 

	ppa.PatBalance AS PatBalance, 

	ppa.Balance AS Balance, 

	pc.Description AS Description, 

	pv.PatientVisitId AS PatientVisitId, 

	pva.InsPayment AS VisitInsPayment, 

	pva.PatPayment AS VisitPatPayment, 

	pva.InsBalance AS VisitInsBalance, 

	pva.PatBalance AS VisitPatBalance,

	cs.Description AS CollectionsStatus

FROM PatientProfile pp 

	INNER JOIN Guarantor gr ON pp.GuarantorId = gr.GuarantorId 

	INNER JOIN PatientProfileAgg ppa ON pp.PatientProfileId = ppa.PatientProfileId 

	INNER JOIN PatientCorrespondence pc ON pp.PatientProfileId = pc.PatientProfileId 

	INNER JOIN PatientVisit pv ON pp.PatientProfileId = pv.PatientProfileId 

				AND pc.PatientVisitId = pv.PatientVisitId 

	INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId 

	INNER JOIN MedLists cs ON pv.CollectionsStatusMId = cs.MedListsId

WHERE

( 

 ('1267' IS NOT NULL AND gr.GuarantorId IN(1267)) 

 OR '1267' IS NULL 

)

AND

( 

 (NULL IS NOT NULL AND pv.CollectionsStatusMId IN(NULL)) 

 OR NULL IS NULL 

)

AND pv.Visit >= ISNULL(NULL,'1/1/1900') AND pv.Visit  < dateadd(day,1,ISNULL(NULL,'1/1/3000'))

AND pv.CollectionsNextContactDate >= ISNULL(NULL,'1/1/1900') AND pv.CollectionsNextContactDate  < dateadd(day,1,ISNULL(NULL,'1/1/3000'))

GROUP BY gr.GuarantorId,

	pp.PatientProfileId,

	ppa.InsBalance, 

	ppa.PatBalance, 

	ppa.Balance, 

	pc.Description, 

	pv.PatientVisitId, 

	pva.InsPayment, 

	pva.PatPayment, 

	pva.InsBalance, 

	pva.PatBalance,

	pv.BillStatus,

	cs.Description

HAVING (pv.BillStatus = 13)
 

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

		AND

		( 

		 ('1267' IS NOT NULL AND gr.GuarantorId IN(1267)) 

		 OR '1267' IS NULL 

		)

	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

		AND

		( 

		 ('1267' IS NOT NULL AND gr.GuarantorId IN(1267)) 

		 OR '1267' IS NULL 

		)

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

UNION

SELECT DISTINCT GuarantorID FROM #B

UNION

SELECT DISTINCT GuarantorID FROM #C

UNION

SELECT DISTINCT GuarantorID FROM #GPAll
 

OPEN x

FETCH NEXT FROM c 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()) < 45 AND GuarantorID = @pGuarantorID
 

	IF @tGuarantorID IS NOT NULL

		BEGIN

		DELETE FROM #A WHERE GuarantorID = @pGuarantorID

		DELETE FROM #B WHERE GuarantorID = @pGuarantorID

		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
 

SELECT

	dbo.FormatName(gr.Prefix, gr.First, gr.Middle, gr.Last, gr.Suffix) AS GuarantorName,

	LEFT(gr.SSN, 3) + '-' + SUBSTRING(gr.SSN, 4, 2) + '-' + RIGHT(gr.SSN, 4) AS SSN, 

	'(' + LEFT(gr.Phone1, 3) + ') ' + SUBSTRING(gr.Phone1, 4, 3) + '-' + SUBSTRING(gr.Phone1, 7, 4) AS Phone, 

	'(' + LEFT(gr.Phone2, 3) + ') ' + SUBSTRING(gr.Phone2, 4, 3) + '-' + SUBSTRING(gr.Phone2, 7, 4) AS Phone2,

	RefA.TotalInsBalance AS GuarantorInsBalance, 

	RefA.TotalPatBalance AS GuarantorPatBalance, 

	RefA.TotalBalance AS GuarantorBalance, 

	dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix) AS PatientName,

	RefB.InsBalance, 

	RefB.PatBalance,

	RefC.LastPaymentDate, 

	RefC.Amount,

	RefB.Balance, 

	pv.Visit,

	RefB.Description, 

	pv.TicketNumber,

	RefB.VisitInsPayment, 

	RefB.VisitPatPayment, 

	RefB.VisitInsBalance, 

	RefB.VisitPatBalance, 

	RefB.CollectionsStatus
 

FROM #A as RefA 

	INNER JOIN #B as RefB ON RefB.GuarantorId = RefA.GuarantorId 

	LEFT OUTER JOIN #C as RefC ON RefA.GuarantorId = RefC.GuarantorId

	INNER JOIN Guarantor gr ON gr.GuarantorId = RefA.GuarantorId 

	INNER JOIN PatientProfile pp ON pp.PatientProfileId = RefB.PatientProfileId

	INNER JOIN PatientVisit pv ON pv.PatientVisitId = RefB.PatientVisitId 
 

WHERE ( pv.Visit >= ISNULL(NULL,'1/1/1900') 

	AND pv.Visit < DATEADD(d, 1, ISNULL(NULL,'1/1/3000'))

)

	AND 

	(

		1 < 2 OR 1 > 3

		OR (CASE WHEN 1 = 2 THEN ABS(RefB.InsBalance) END > NULL)

		OR (CASE WHEN 1 = 2 THEN ABS(RefB.PatBalance) END > NULL) 

		OR (CASE WHEN 1 = 3 THEN ABS(RefB.InsBalance) END < NULL)

		OR (CASE WHEN 1 = 3 THEN ABS(RefB.PatBalance) END < NULL)

	)

	AND

	( 

	(NULL IS NOT NULL AND pv.VisitOwnerMID IN(NULL)) 

	 OR NULL IS NULL 

	)
 

GROUP BY

	dbo.FormatName(gr.Prefix, gr.First, gr.Middle, gr.Last, gr.Suffix),

	LEFT(gr.SSN, 3) + '-' + SUBSTRING(gr.SSN, 4, 2) + '-' + RIGHT(gr.SSN, 4), 

	'(' + LEFT(gr.Phone1, 3) + ') ' + SUBSTRING(gr.Phone1, 4, 3) + '-' + SUBSTRING(gr.Phone1, 7, 4), 

	'(' + LEFT(gr.Phone2, 3) + ') ' + SUBSTRING(gr.Phone2, 4, 3) + '-' + SUBSTRING(gr.Phone2, 7, 4),

	RefA.TotalInsBalance, 

	RefA.TotalPatBalance, 

	RefA.TotalBalance,

	dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix),

	RefB.InsBalance, 

	RefB.PatBalance, 

	RefC.LastPaymentDate, 

	RefC.Amount,

	RefB.Balance, 

	pv.Visit,

	RefB.Description, 

	pv.TicketNumber,

	RefB.VisitInsPayment, 

	RefB.VisitPatPayment, 

	RefB.VisitInsBalance, 

	RefB.VisitPatBalance, 

	RefB.CollectionsStatus

ORDER BY GuarantorName, PatientName
 

DROP TABLE #A

DROP TABLE #B

DROP TABLE #C

DROP TABLE #GPAll

Open in new window

0
Comment
Question by:Jeff S
  • 3
5 Comments
 
LVL 18

Accepted Solution

by:
Sham Haque earned 500 total points
ID: 20290508
FETCH NEXT FROM c INTO @pGuarantorID

should be

FETCH NEXT FROM x INTO @pGuarantorID
0
 
LVL 18

Expert Comment

by:Sham Haque
ID: 20290515
your cursor was declared and opened as 'x'....
0
 
LVL 8

Expert Comment

by:digital_thoughts
ID: 20290518
From the looks of it, line 176 is incorrect:

FETCH NEXT FROM c INTO @pGuarantorID

Should be:

FETCH NEXT FROM x INTO @pGuarantorID
0
 
LVL 7

Author Comment

by:Jeff S
ID: 20290558
DOH -- I need more coffee this morning! Thanks all for the very fast responses!
0
 
LVL 18

Expert Comment

by:Sham Haque
ID: 20290571
hey no probs. i'm about ready for home if i can spot errors that quick ;-)
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now