Generating Missing Dates and Numbers

  • 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

  • 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

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

    where id = 1 and colid < 11 ) as b

    cross join

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

    where id = 1 and colid < 11 ) as c

    cross join

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

    where id = 1 and colid < 11 ) as d

    cross join

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

    where id = 1 and colid < 11 ) as e

    cross join

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

    where id = 1 and colid < 11 ) as f

    ) as nums

    SET STATISTICS TIME OFF

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 1871 ms, elapsed time = 1871 ms.

    its not as fast but it works

    Conan

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

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