Generating Missing Dates and Numbers
-
Hello,
Here is another simple SQL way to find gaps in a numeric column,
that is: the lowest number for whom its successor (+1) does not exists... (regarding Jacob Sebastian's case):
SELECT min(t.CoordinatorID) FROM Coordinators t where not exists
(SELECT 1 FROM Coordinators where CoordinatorID = t.CoordinatorID +1);
regards,
Moshe
-
Dennis D. Allen (2/6/2008)
What do folks think about adding directly to a datetime and allowing implicit conversions to take place?CREATE FUNCTION dbo.DateRange( @start DATETIME, @length INT )RETURNS @range TABLE (
dateOf datetime PRIMARY KEY
)
AS
BEGIN
-- Populate the output table of dates using a number table
INSERT INTO @range ( dateOf )
SELECT
@start + n AS dateOf
FROM (
SELECT (n1.n +n10.n +n100.n +n1000.n) AS n
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS n1
CROSS JOIN (SELECT 0 AS n UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40 UNION SELECT 50 UNION SELECT 60 UNION SELECT 70 UNION SELECT 80 UNION SELECT 90) AS n10
CROSS JOIN (SELECT 0 AS n UNION SELECT 100 UNION SELECT 200 UNION SELECT 300 UNION SELECT 400 UNION SELECT 500 UNION SELECT 600 UNION SELECT 700 UNION SELECT 800 UNION SELECT 900) AS n100
CROSS JOIN (SELECT 0 AS n UNION SELECT 1000 UNION SELECT 2000 UNION SELECT 3000 UNION SELECT 4000 UNION SELECT 5000 UNION SELECT 6000 UNION SELECT 7000 UNION SELECT 8000 UNION SELECT 9000) AS n1000
WHERE (n1.n +n10.n +n100.n +n1000.n) BETWEEN 0 AND @length - 1
) AS numbers
ORDER BY
n ASC;
RETURN;
END
Nicely done is what I think...
The only "catch" I see is exactly what GSquared has been talking about... you might not think an extra 200 milliseconds is much, but if you have to do it a million times, things add up in a hurry. Case in point (both examples generate 30 years of dates for, say, a mortgage)...
SET STATISTICS TIME ONDECLARE @Bitbucket DATETIME
DECLARE @StartDate DATETIME
SET @StartDate = '01/01/2000'
PRINT REPLICATE('=',20)+'DateRange Function'+REPLICATE('=',20)
SELECT @Bitbucket = DateOf FROM dbo.DateRange('01/01/2000',11000)
PRINT REPLICATE('=',20)+'Tally Table Method'+REPLICATE('=',20)
SELECT @Bitbucket = (@StartDate +N -1 )
FROM dbo.Tally WITH (NOLOCK)
WHERE N <= 11000
PRINT REPLICATE('=',20)+'End'+REPLICATE('=',20)
SET STATISTICS TIME OFF
... produces the following results... the important parts are highlighted...
[font="Courier New"]
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
====================DateRange Function====================
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 203 ms, elapsed time = 196 ms.
====================Tally Table Method====================
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 10 ms.
====================End====================
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
[/font]
Summary... like GSquared has said, the Tally (or Numbers) table method absolutely smokes other methods when it comes to sheer performance.
--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) -
it's your code which gives me sintax error:
;WITH T1 AS
( SELECT CAST('2007-01-01' AS DATETIME) + Number - 1 as MYDATE
FROM dbo.GetNumbers(1, datediff(dd, '20070101','20071231')+1)
)
SELECT * from T1
The version i'm using is:
Microsoft SQL Server Management Studio 9.00.3042.00
Herramientas cliente de Microsoft Analysis Services 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer7.0.5730.11
Microsoft .NET Framework2.0.50727.832
Sistema operativo5.1.2600
-
Heh... maybe the 3rd time will be the charm...
PLEASE copy and paste the exact error you're getting... I get no errors when I run the code you posted in the presence of the function (which also needs to be created, just in case you missed that 😉 )
--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) -
The error message is:
Mens. 102, Nivel 15, Estado 1, Línea 3
Sintaxis incorrecta cerca de 'datediff'.
Translating to English it sounds:
Message 102, Level 15, State 1, Line 3
Sintax error near 'datediff'
Consider that the following code works fine:
;declare @i as integer
;set @i = 365
;WITH T1 AS
( SELECT CAST('2007-01-01' AS DATETIME) + Number - 1 as MYDATE
FROM dbo.GetNumbers(1, @i)
)
SELECT * from T1
-
Dunno what to say... like I said, the following code which you posted, works fine on my machine... no errors of any sort...
[font="Courier New"];WITH T1 AS
( SELECT CAST('2007-01-01' AS DATETIME) + Number - 1 as MYDATE
FROM dbo.GetNumbers(1, datediff(dd, '20070101','20071231')+1)
)
SELECT * from T1[/font]
I'm running SQL Server 2005 sp2 Developer's Edition which is the same as the Enterprise Edition for all practical purposes.
Perhaps the problem is the "language"... maybe the datepart of "dd" needs to be changed... you'd have to look that up in your version of Books Online. Curious, what do you get when you run just the following?
[font="Courier New"]Select datediff(dd, '20070101','20071231')+1[/font]
--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) -
The instruction:
Select datediff(dd, '20070101','20071231')+1
correctly returns 365.
It looks like if it didn't accept nested functions, but that's obliously not true... so I really don't know...
-
I get the same error with an English install of 2005 Standard.
I have not been able to find anything on the problem...
-
cgrigolini (2/11/2008)
The error message is:Mens. 102, Nivel 15, Estado 1, Línea 3
Sintaxis incorrecta cerca de 'datediff'.
Translating to English it sounds:
Message 102, Level 15, State 1, Line 3
Sintax error near 'datediff'
Consider that the following code works fine:
;declare @i as integer
;set @i = 365
;WITH T1 AS
( SELECT CAST('2007-01-01' AS DATETIME) + Number - 1 as MYDATE
FROM dbo.GetNumbers(1, @i)
)
SELECT * from T1
SQL Server 2000 gives that error (CTEs aren't supported).
Please check the version you are running.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
What are CTEs?
How can I check the version I'm using?
What I get from SQLSERVER2005 console is:
Microsoft SQL Server Management Studio 9.00.3042.00
Herramientas cliente de Microsoft Analysis Services 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.832
Sistema operativo 5.1.2600
-
cgrigolini (2/11/2008)
What are CTEs?How can I check the version I'm using?
What I get from SQLSERVER2005 console is:
Microsoft SQL Server Management Studio 9.00.3042.00
Herramientas cliente de Microsoft Analysis Services 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.832
Sistema operativo 5.1.2600
SELECT @@VERSION
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
-
Really don't know. It works on the all of the 2005 machines I've tried and fails on the 2000 machine with the exact same error.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
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.
-
Yes, thank you
the instruction
sp_dbcmptlevel 'MYDBNAME', 90
has solved the problem
Viewing 15 posts - 31 through 45 (of 46 total)
You must be logged in to reply to this topic. Login to reply