Need Help with a Cursor

[aradia926]

I've got a SQL Server 2000 stored procedure that returns data to be used in a crystal report in Visual Studio 2005. Most of the stored procedure works well, but there is a point where I need to calculate an average number of days been a group of date pairs.

I'm not familiar with cursors, but I think that I will need to use one to achieve the result I am looking for so I came up with the code below which is a snippet from my stored procedure. In this part of the code, the sp looks at the temporary table #lmreport (which holds all of the data that is returned at the end to crystal) and for every row in the table where the terrid is 'T' (the territory is domestic), it selects all of those territories from the territory table and loops through them to determine the date averages (by calling a nested stored procedure, also included below) for each territory and then updates #lmreport with that data.

When I try to run the stored procedure, I get "The column prefix '#lmreport' does not match with a table name or alias name used in the query." on the line indicated.

Does anyone have any idea what might be wrong or if this will even work the way I need it to?

Thank you in advance.


FROM #lmreport

IF #lmreport.terrid='T' <------- error here
BEGIN
DECLARE terrQuery CURSOR FOR

SELECT DISTINCT Territory
FROM tblTerritory tt
WHERE tt.Territory <> 'FOREIGN'
FOR READ ONLY

DECLARE @myid INT
OPEN terrQuery

FETCH NEXT FROM terrQuery INTO @myid

WHILE (@@fetch_status <> -1)

BEGIN
DECLARE @RetVal DECIMAL
EXEC Get_Avg_Days_Territory @datepassed = @datePassed, @territory = @myid
UPDATE #lmreport
SET avgdaysmo=@RetVal
WHERE #lmreport.territoryname = @myid
FETCH NEXT FROM terrQuery INTO @myid
END

CLOSE terrQuery

DEALLOCATE terrQuery
END

ELSE

BEGIN
DECLARE countryQuery CURSOR FOR

SELECT DISTINCT Country
FROM tblCountry tctry
WHERE tctry.Country <> 'United States of America'
FOR READ ONLY

DECLARE @myid2 INT
OPEN countryQuery

FETCH NEXT FROM countryQuery INTO @myid2

WHILE (@@fetch_status <> -1)

BEGIN
DECLARE @RetVal2 DECIMAL
EXEC Get_Avg_Days_Country @datepassed = @datePassed, @country = @myid2
UPDATE #lmreport
SET avgdaysmo=@RetVal2
WHERE #lmreport.territoryname = @myid2

FETCH NEXT FROM countryQuery INTO @myid2
END

CLOSE countryQuery

DEALLOCATE countryQuery
END

---------------NESTED PROCEDURE----------------
CREATE PROCEDURE [dbo].[Get_Avg_Days_Territory]
(
@datePassed DATETIME,
@territory NVARCHAR(50),
@AvgDays DECIMAL OUTPUT
)
AS
BEGIN

CREATE TABLE #Avgs
(
ContactID int,
StartDate datetime,
EndDate datetime,
NumOfDays decimal
)
INSERT INTO #Avgs
(ContactID)
(SELECT DISTINCT tc.ContactID
FROM tblContact tc
INNER JOIN tblCompany tco ON tc.CompanyID = tco.CompanyID
INNER JOIN tblTerritory tt ON tco.TerritoryID = tt.TerritoryID
INNER JOIN tblLActivityTrack tlat ON tc.ContactID = tlat.ContactID
WHERE tt.Territory = @territory
AND tlat.UpdateTypeID = '36'
AND MONTH(tlat.ActivityDate) = MONTH(@datePassed)
AND YEAR(tlat.ActivityDate) = YEAR(@datePassed))
UPDATE #Avgs
SET EndDate = (SELECT MAX(tlat.ActivityDate)
FROM tblContact tc
INNER JOIN tblCompany tco ON tc.CompanyID = tco.CompanyID
INNER JOIN tblTerritory tt ON tco.TerritoryID = tt.TerritoryID
INNER JOIN tblLActivityTrack tlat ON tc.ContactID = tlat.ContactID
WHERE tt.Territory = @territory
AND #Avgs.ContactID = tc.ContactID
AND tlat.UpdateTypeID = '36'
AND MONTH(tlat.ActivityDate) = MONTH(@datePassed)
AND YEAR(tlat.ActivityDate) = YEAR(@datePassed))
FROM #Avgs
UPDATE #Avgs
SET StartDate = (SELECT MAX(tlat.ActivityDate)
FROM tblContact tc
INNER JOIN tblCompany tco ON tc.CompanyID = tco.CompanyID
INNER JOIN tblTerritory tt ON tco.TerritoryID = tt.TerritoryID
INNER JOIN tblLActivityTrack tlat ON tc.ContactID = tlat.ContactID
WHERE (tt.Territory = @territory
AND #Avgs.ContactID = tc.ContactID
AND tlat.UpdateTypeID = '37'
AND tlat.ActivityDate <= #Avgs.EndDate))

FROM #Avgs
UPDATE #Avgs
SET NumOfDays = (SELECT DATEDIFF(DAY, #Avgs.StartDate, #Avgs.EndDate))

SELECT @AvgDays = (
SELECT SUM(NumOfDays)/(SELECT COUNT(DISTINCT tc.ContactID)
FROM tblContact tc
INNER JOIN tblCompany tco ON tc.CompanyID = tco.CompanyID
INNER JOIN tblTerritory tt ON tco.TerritoryID = tt.TerritoryID
INNER JOIN tblLActivityTrack tlat ON tc.ContactID = tlat.ContactID
WHERE @territory = tt.Territory
AND tlat.UpdateTypeID = '36'
AND MONTH(tlat.ActivityDate) = MONTH(@datePassed)
AND YEAR(tlat.ActivityDate) = YEAR(@datePassed))
FROM #Avgs)
DROP TABLE #Avgs
END

 

[SQLDenis]

>>IF #lmreport.terrid='T'
This is not valid



declare @v varchar(50)
select @v = terrid from #lmreport where SomeColumn = SomeValue

IF @v ='T'
.....

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/