Need Help with a Cursor

Question

Post reply

Need Help with a Cursor

Lori Winkler

Valued Member

Points: 67
More actions
July 25, 2006 at 9:32 am

#153106

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
&nbsp
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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 1

you can t write this (as you saw allready)

IF #lmreport.terrid='T' <------- error here

if you want to test if there is any terrid equal to T do

IF EXISTS ( SELECT * FROM #Imreport WHERE terrid='T')

it will be better for you to post the DDL + DATA SAMPLE and DESIRED result

Kindest Regards,

Vasc