Need to return a record for each month 1

[pabowen]

Hello everyone, and Thanks in Advance.

I have a query that has me stuck, unless I use a cursor and I believe there has to be a better way than that.

I have a table that holds records of classes, I need to run a query that returns one class for each month after a given date.

So if my 1st table contains:
CourseCode CourseName
Math101 Pre-Algebra
Math201 Algebra

And I need to return 2 months of classes, determined by the statment:
select datediff(month, '2008-09-01', getdate())

Which returns a value of '2'.

Then I need the following result set:

CourseCode CourseName StartDate
Math101 Pre-Algebra 2008-09-01
Math101 Pre-Algebra 2008-10-01
Math201 Algebra 2008-09-01
Math201 Algebra 2008-10-01

I figured I could do a cartesian join off a temp table that contains all the dates, but I can't figure out how to create that table without using a cursor.

By the way, this will be an infrequent query, run rarely. So performance isn't a major consideration. But I don't want to create bad code either.

Any great thoughts on this one?

Thanks!
Patrick

 

[r937]

could you explain this part a bit more...

And I need to return 2 months of classes, determined by the statment:
select datediff(month, '2008-09-01', getdate())

Which returns a value of '2'

is it always 2? will you feed in any other dates besides 2008-09-01 to make it 2? or is 2008-09-01 going to stay fixed and thus the DATEDIFF will yield 3, then 4, then...


r937.com | rudy.ca

 

[pabowen]

The number of months will change depending on the current date, as provided by getdate(). So while it is currently 2, in December it will be 3, in January 4, etc.

The point is, I need to create a class for each month from September 2008 until whatever the current date is. Whether that be 2 months, or 200. The statment:

select datediff(month, '2008-09-01', getdate())

provides the number of months necessary.

Thanks, Patrick

 

[RiverGuy]

You don't NEED to use a cursor, but a looping construct nonetheless. Try the following code, you were on to the right track, however, it might come in handy to create a permanent date table for your databases.

DECLARE @ProvidedDate DATETIME
SELECT @ProvidedDate = '20080901'

DECLARE @StartDate DATETIME
SELECT @StartDate = @ProvidedDate

DECLARE @EndDate DATETIME
SELECT @EndDate = GETDATE()

DECLARE @Dates TABLE (TheDate DATETIME)

WHILE @StartDate <= DATEADD(month, -1, @EndDate)
BEGIN
	INSERT INTO @Dates SELECT @StartDate
	SELECT @StartDate = DATEADD(month, 1, @StartDate)
END



SELECT d.*, t.*
FROM @Dates d
CROSS JOIN YourTable t

 

[pabowen]

RiverGuy, Thank you very much. I had just developed a similar answer right before you responded.

By code is as follows:

DECLARE @Months TABLE (Date datetime)
DECLARE @i int
DECLARE @MaxiD int
SELECT @MaxId = datediff(month, '2008-09-01', getdate()) 

SET @i = 0 
WHILE @i <= @MaxId
    BEGIN
       INSERT INTO @Months VALUES (dateadd(month, @i, '2008-09-01'))
       SET @i = @i + 1
    END

select * from table
cross join @months

Thanks again!
Patrick