Question

Locked

SQL SERVER 2005 TSQL Cursors

By andrew ·
I am using Access against a SQL 2005 database.
I have 3 Tables
1 Has a list of dates
2 Has a list of courses with start and end dates + attendees
3 Is required to get a list of attendees on each date

For example
1 tblDates might have dates for 10 11 12 and 13 Nov
e.g.
Date
09/11/06
10/11/06
11/11/06
12/11/06

2 tblCourses Has a list of courses

Course Start End Attendees
C1 09/11/06 12/11/06 8
C2 11/11/06 14/11/06 6
C3 09/11/06 11/11/06 5

List 3 tblDailyAttendance needs a total for each date in 1

Date Attendees
09/11/06 13
10/11/06 13
11/11/06 19
12/11/06 14

etc

I have a solution using VBA that loops recordsets to build the attendance list that works ok, but really I am after 1 of 2 things that could be run
ideally on the SQL SERVER rather that on the linked access database, because eventually the database will move to a project

Can anyone think of a query/queries that would solve this problem without the need to result to VBA Recordsets
OR can anyone pont me to a solution using TSQL CURSORS

I had a go at this on the server, and everything compiled OK, but it only returned ONE row
I obviously got some bit of logic wrong.
Maybe I cannot Nest the Cursor Loops as I have done (Coming from a programming background it seemed logical to me)

For interest or help, the basic TSQL procedure I tried is below

USE training
GO

-- Declare the variables to store the values returned by FETCH.
DECLARE @Date smalldatetime,@Start smalldatetime, @End SmallDatetime
DECLARE @Places int
DECLARE @ADate smalldatetime
DECLARE @DayFetch int, @CourseFetch int

-- Set Up Courses Summary Cursor
DECLARE coursecursor CURSOR FOR
SELECT crs_CourseDate,crs_FinishDate,Delegates FROM dbo.vue_CourseSummary

-- Set Up Dates Table Cursor
DECLARE daycursor CURSOR FOR
SELECT cat_Date FROM tbl_Dates

OPEN daycursor

FETCH NEXT FROM dayCursor INTO @Date

SET @DayFetch=@@FETCH_STATUS

WHILE @DayFetch = 0
BEGIN
-- Open the courses cursor each day and close at the end
OPEN coursecursor
-- Get Data from First Record in Cursor
FETCH NEXT FROM coursecursor INTO @Start, @End, @Places
SET @CourseFetch=@@FETCH_STATUS
WHILE @CourseFetch = 0
BEGIN
-- Check for Dates inside Range
IF @Start >= @Date AND @End <= @Date
BEGIN
-- Write the Data into the Daily table
INSERT INTO tbl_DailyAttendance
(cdt_Date,cdt_Places)
SELECT @Date,@Places
END
-- Get Next Course to Check, loop until No more records
FETCH NEXT FROM coursecursor INTO @Start, @End, @Places
SET @CourseFetch=@@FETCH_STATUS
END
-- Get Next Day from date list
FETCH NEXT FROM dayCursor INTO @Date
SET @DayFetch=@@FETCH_STATUS
-- Close Courses Cursor at end of the courses loop ready for next day
CLOSE coursecursor
END

DEALLOCATE coursecursor
CLOSE daycursor
DEALLOCATE daycursor


Any help or pointers to where I might find a solution would be gratefully accepted

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Unless I've misunderstood a simple bit of SQL should do it

by Tony Hopkinson In reply to SQL SERVER 2005 TSQL Curs ...

select T1.CourseDate,Sum(T2.attendees) as Attendees
From T1,T2
Where T1.CourseDate >= T2.StartDate and T1.CourseDate <= T2.EndDate

PS you should stay away from cursors in SQL Server if you can (especially on large volumes), it's not optimised for them and performance will be abysmal.

Collapse -

Nesting

by ben In reply to SQL SERVER 2005 TSQL Curs ...

I would put the nested Fetch inside a stored procedure.

Back to Networks Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums