Solved

Microsoft, SQL, SQL 2005 - Query Assistance

Posted on 2007-11-15
5
190 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

708 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

19 Experts available now in Live!

Get 1:1 Help Now