Generating Missing Dates and Numbers

Question

Generating Missing Dates and Numbers

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

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

Jeff Moden SSC Guru Points: 1000363 More actions · Answer 1

John Beggs (2/11/2008)
I don't know why I didn't think of this last week, but the problem (in my case) was in the database compatability level. I was working in an old testing DB that I had moved over from a 2000 server some time back and never changed it to 9.0 compatability level.
@cgrigolini -- only you will know if it's safe to change the compatability level in your DB. It's my guess that is the cause of the Syntax Error.

Perfect, John... Dunno why I didn't think of that... maybe it's 'cause I quit smokin' and can't see past the thumb I'm sucking now 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Conan-605016 Old Hand Points: 328 More actions · Answer 2

For earlier versions of sql that don't have row_number or cte's you can use the following :-

SET STATISTICS TIME ON

DECLARE @DUMMYVAR INT

SELECT @DUMMYVAR = seq

from

(select f.col * 100000 + e.col * 10000 + d.col * 1000 + c.col * 100 + b.col * 10 + a.col as Seq

from

(

select colid -1 as col from master.dbo.syscolumns

where id = 1 and colid < 11 ) as a

cross join