Cursor not ordering data correctly?

Question

Post reply

Cursor not ordering data correctly?

Scott Malinowski-283583

SSC Rookie

Points: 42
More actions
June 12, 2006 at 1:07 pm

#168565

Greetings!
In SQL-server 2000, I am attempting to create a cursor and then scan through the cursor sequentially from the first to last record. However, when I run the stored procedure this code is part of the order of the records does NOT match the ORDER BY clause.
Any caveats or issues that I am not aware with Cursor ORDER BY statements?
Here's my code:
DECLARE _LOACursor CURSOR FOR
SELECT DISTINCT
   [ParentId], [Date]
FROM
  [vLeaveOfAbsence]
WHERE
  ObjectDomainID = @MetadataHash
ORDER BY
  [ParentId], [Date]
OPEN _LOACursor
FETCH NEXT FROM _LOACursor INTO @PersonId, @Date
THANKS IN ADVANCE!!!!!!!!!!
Scott

Viewing 8 posts - 1 through 7 (of 7 total)

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

Do you have examples ?

Is ParentID a numeric, or is it char/varchar data ? Id Date a true date/time column, or is it a char/varchar ?

Scott Malinowski-283583 SSC Rookie Points: 42 More actions · Answer 2

The cursor is actually ordered correctly. The problem is in the Fetching...it seems out of place. Here's the entire SP code:

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS OFF

GO

-- EXEC SmartReport_LeaveOfAbsence 489492, '5/1/2006', '6/6/2007', '', 0, 0

ALTER PROCEDURE [dbo].[SmartReport_LeaveOfAbsence]

@MetadataHash int,

@StartDate datetime,

@EndDate datetime,

@WhereClause varchar(4000),

@OnlyIncludeActiveStudents bit = 0,

@StudentId int = 0

AS

-- Declare Locals.

DECLARE @PersonId int

DECLARE @GroupId int

DECLARE @PrevID int

DECLARE @LOAFetchStatus int

DECLARE @PrevDate datetime

DECLARE @Date datetime

DECLARE @LoaStartDate datetime

DECLARE @LoaEndDate datetime

DECLARE @WeFoundData bit

DECLARE @Sql nvarchar(2000)

SET @WhereClause = LTRIM(RTRIM(@WhereClause))

IF ( @WhereClause = '' ) SET @WhereClause = NULL

SET NOCOUNT ON

--Table used to group the loa ranges by person, LOA instance

CREATE TABLE #LoaGroupsByPersonDate

(

[Group] int NULL,

[ObjectId] int NULL,

[LoaStartDate] datetime NULL,

[LoaEndDate] datetime NULL

)

DECLARE _LOACursor CURSOR FOR

SELECT DISTINCT

[ParentId], [Date]

FROM

[vLeaveOfAbsence]

WHERE

ObjectDomainID = @MetadataHash

ORDER BY

[ParentId], [Date]

OPEN _LOACursor

FETCH NEXT FROM _LOACursor INTO @PersonId, @Date

SET @LOAFetchStatus = @@FETCH_STATUS

SET @GroupId = 0

SET @PrevID = @PersonId

SET @LoaStartDate = @Date

SET @LoaEndDate = @Date

SET @PrevDate = @Date

SET @WeFoundData = 0

--Loops through each record in the loa range checking for a change in person

-- or a gap in the dates being processed

WHILE @LOAFetchStatus <> -1

BEGIN

IF @LOAFetchStatus = 0

SET @WeFoundData = 1

BEGIN

-- PRINT 'PersonID test/PersonID/Date/PrevID/PrevDate/LOAStartDate/LOAEndDate'

-- PRINT @PersonID

-- PRINT @Date

-- PRINT @PrevID

-- PRINT @PrevDate

-- PRINT @LoaStartDate

-- PRINT @LoaEndDate

-- Write a group record if the student is different

IF (@PersonID <> @PrevID)

BEGIN

SET @GroupId = @GroupId + 1

INSERT INTO #LoaGroupsByPersonDate

VALUES (@GroupId,

@PrevId,

@LoaStartDate,

@LoaEndDate)

SET @PrevID = @PersonId

SET @LoaStartDate = @Date

END

-- ELSE

-- Write a group record if there is a gap in dates

IF ( DateDiff(d,@Date,@PrevDate) <> 1 )

BEGIN

SET @GroupId = @GroupId + 1

INSERT INTO #LoaGroupsByPersonDate

VALUES

(@GroupId,

@PrevId,

@LoaStartDate,

@LoaEndDate)

SET @LoaStartDate = @Date

END

SET @LoaEndDate = @Date

SET @PrevDate = @Date

FETCH NEXT FROM _LOACursor INTO @PersonId, @Date

SET @LOAFetchStatus = @@FETCH_STATUS

END

IF ( @WeFoundData = 1 )

BEGIN

SET @GroupId = @GroupId + 1

INSERT INTO #LoaGroupsByPersonDate

VALUES

(@GroupId,

@PrevId,

@LoaStartDate,

@LoaEndDate)

END

CLOSE _LOACursor

DEALLOCATE _LOACursor

-- Return LOA info.

SET @Sql = 'SELECT vStudentCLH.ObjectDomainID,

vStudentCLH.ObjectId,

IsCurrentLoa = dbo.fnIsStudentLOA(vStudentCLH.ObjectId, GETDATE()),

vStudentCLH.AMA,

vStudentCLH.AttendanceHrs,

vStudentCLH.CourseDescription,

vStudentCLH.FullName,

vStudentCLH.GPA,

vStudentCLH.HomePhone,

vStudentCLH.IsActive,

vStudentCLH.PercentComplete,

vStudentCLH.SSN,

vStudentCLH.DisplayStudentSSN,

vStudentCLH.StartDate,

vStudentCLH.StudentId ,

StartLOADate = LOA.LoaStartDate,

EndLOADate = LOA.LoaEndDate

FROM #LoaGroupsByPersonDate LOA WITH (NOLOCK)

INNER JOIN vStudentCLH WITH (NOLOCK)

ON LOA.ObjectId = vStudentCLH.ObjectId

WHERE ( (LOA.LoaStartDate BETWEEN ''' + CAST(@StartDate as varchar(30)) + ''' AND ''' + CAST(@EndDate as varchar(30)) + ''')

OR (LOA.LoaEndDate BETWEEN ''' + CAST(@StartDate as varchar(30)) + ''' AND ''' + CAST(@EndDate as varchar(30)) + ''')

OR (LOA.LoaStartDate < ''' + CAST(@StartDate as varchar(30)) + ''' AND LOA.LoaEndDate > ''' + CAST(@EndDate as varchar(30)) + ''' ) )

<<##IS_ACTIVE_TOKEN##>>

<<##WHERE_CLAUSE_TOKEN##>>

ORDER BY LOA.LoaStartDate DESC'

IF(@OnlyIncludeActiveStudents = 1) BEGIN

SET @Sql = REPLACE(@Sql, '<<##IS_ACTIVE_TOKEN##>>', ' AND vStudentCLH.IsActive = 1 ')

END ELSE BEGIN

SET @Sql = REPLACE(@Sql, '<<##IS_ACTIVE_TOKEN##>>', '')

END

IF(@WhereClause IS NOT NULL) BEGIN

SET @Sql = REPLACE(@Sql, '<<##WHERE_CLAUSE_TOKEN##>>', ' AND ' + @WhereClause + ' ')

END ELSE BEGIN

SET @Sql = REPLACE(@Sql, '<<##WHERE_CLAUSE_TOKEN##>>', '')

END

print @Sql

EXEC sp_executesql @Sql

-- Clean up.

DROP TABLE #LoaGroupsByPersonDate

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

Thanks!

Scott

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

Why would you want to have a cursor looping through all records when it almost certainly can be done set-based?

When reading your code, it seems to me that you want to isolate the island in a sequential table.

N 56°04'39.16"
E 12°55'05.25"

Scott Malinowski-283583 SSC Rookie Points: 42 More actions · Answer 4

I'm a newbie to SQL Server. So, I don't really understand what you mean by Set-based?

However, I believe I need to process each cursor record sequentially because of a poor database design. Leave Of Absence records really need to be grouped by Person and Group of Dates Instance. But, the database table design has an LOA record that has one record for each Person/Date. A person could be on Leave of Absence for 30 days which would constitute ONE leave of absence. The code I am trying to debug was written to generate a group record showing the starting and ending dates of that one LOA instance encapsulating the 30 days of LOA. Does that make sense?

Thanks!

Scott

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

It made some sense. But to be really clear, do you have an example of table design and about 20 rows of test data?

Example:

TABLE LoA

----------------------

PersonID INT,

Date DATETIME,

Days TINYINT

And then some test data for the LoA table

-------------------------

13 6/1/2006 15

147 3/3/2006 2

28 5/1/2006 300

And preferably the layout you want for the output, such as

-----------------------------------------------------------

13 6/1/2006 6/16/2006

147 3/3/2006 3/5/2006

28 5/1/2006 3/14/2007

To this layout for the output, you could also write down some rules that apply, such as rolling 30 days check or something...

We are a bunch of clever people here who almost certainly can help you, if you help us with a better understanding of your problem.

N 56°04'39.16"
E 12°55'05.25"

Ken McKelvey SSCoach Points: 18842 More actions · Answer 6

Try something set based like the following. The functions will need to be altered to take into account weekends, public holidays etc. Obviously this has not been tested.

create function dbo.PrevValidLeaveDate
(
 @LeaveDate datetime
)
returns datetime
as
begin
 return dateadd(day, -1, @LeaveDate)
end
go
create function dbo.NextValidLeaveDate
(
 @LeaveDate datetime
)
returns datetime
as
begin
 return dateadd(day, 1, @LeaveDate)
end
go

create table #LoaGroupsByPersonDate  
(
 ObjectId int not null
 ,LoaStartDate datetime not null
 ,LoaEndDate datetime not null
)

insert into #LoaGroupsByPersonDate
select D1.ParentID
 ,D1.StartDate
 ,D2.EndDate
from (
  select L1.ParentId
   ,L1.[Date] as StartDate
  from vLeaveOfAbsence L1
  where L1.ObjectDomainID = @MetadataHash
   and not exists (select *
     from vLeaveOfAbsence L2
     where L2.ObjectDomainID = @MetadataHash
      and L2.ParentId = L1.ParentID
      and L2.[Date] = dbo.PrevValidLeaveDate(L1.[Date]))
&nbsp D1
 join
 (
  select L3.ParentId
   ,L3.[Date] as EndDate
  from vLeaveOfAbsence L3
  where L3.ObjectDomainID = @MetadataHash
   and not exists (select *
     from vLeaveOfAbsence L4
     where L4.ObjectDomainID = @MetadataHash
      and L4.ParentId = L3.ParentID
      and L4.[Date] = dbo.NextValidLeaveDate(L3.[Date]))
&nbsp D2 on D1.ParentId = D2.ParentID

delete #LoaGroupsByPersonDate
where LoaEndDate <> (select min(L2.LoaEndDate)
   from #LoaGroupsByPersonDate L2
   where L2.ObjectId = #LoaGroupsByPersonDate.ObjectID
    and L2.LoaEndDate >= #LoaGroupsByPersonDate.LoaStartDate)

Scott Malinowski-283583 SSC Rookie Points: 42 More actions · Answer 7

Gentlemen,

Thank you SO much for your replies and your ideas.

I did manage to get it to work fixing several bugs: Create Cursor, operator misqueue and two closely spelled variables all got me into trouble.

Your feedback and especially the part about what information I need to provide to this forum will definitely help me next time I have a SQL Server problem (and I will I'm sure <g&gt.

Thanks again!

Scott