SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Build date table


Build date table

Author
Message
Paul Peeters
Paul Peeters
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 134
Comments posted to this topic are about the item Build date table
UMG Developer
UMG Developer
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4062 Visits: 2204
Interesting question, I got it right by process of elimination. (Too lazy to do the math tonight.)

However, I probably wouldn't use that method to populate a date table. The numbers table or a cross join are probably faster. (And I think easier.)
Oleg Netchaev
Oleg Netchaev
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2215 Visits: 1817
This example shows how to quickly populate a table with successive dates? Not really, it shows a pretty convoluted way of doing it, not quick. Any time there is a while loop in T-SQL script, such a script is probably not efficient because SELECT is already a loop, and any attempts to use the slow while loop instead of a native select typically lead to performance issues. How about using a simple select, i.e.

declare @dtFrom datetime;
declare @dtTo datetime;
declare @diff int;

select
@dtFrom = '2010-01-01T00:00:00.000',
@dtTo = '2010-12-31T00:00:00.000',
@diff = datediff(day, @dtFrom, @dtTo) + 1;

create table #Dates
(
[Date] datetime not null primary key
);

-- this happily inserts dates exactly between first and last date, no more and no less
insert into #Dates
select top (@diff)
dateadd(day, row_number() over (order by [object_id]) - 1, @dtFrom)
from sys.all_columns;

select * from #Dates;

drop table #Dates;



Oleg
vk-kirov
vk-kirov
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4342 Visits: 4408
Very interesting technique.

But inserting extra data and then deleting them is not the best solution, because delete operations are expensive. It's better to add a condition in order to insert only necessary data:
INSERT INTO #Dates 
(
[Date],
[SomeValueForDate]
)
SELECT DATEADD(DAY, @n, [Date]) AS [Date],
@n AS [SomeValueForDate]
FROM #Dates
WHERE DATEADD(DAY, @n, [Date]) <= @dtTo -- No need to delete data after that


Iulian -207023
Iulian -207023
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2483 Visits: 1248
Nice question, thanks also Oleg's script looks great. Here is something that has the same result:

DECLARE @dtFrom DATETIME
DECLARE @dtTo DATETIME
SET @dtFrom = '2010-01-01T00:00:00.000'
SET @dtTo = '2010-12-31T00:00:00.000'

DECLARE @Diff INT
SET @Diff = DATEDIFF(DAY, @dtFrom, @dtTo)

CREATE TABLE
#Dates
(
[Date] DATETIME NOT NULL PRIMARY KEY,
[SomeValueForDate] INT NOT NULL
)

CREATE TABLE #Digits(digit INT NOT NULL PRIMARY KEY);

INSERT INTO #Digits(digit)
VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

SELECT DATEADD( DAY, D3.digit * 100 + D2.digit * 10 + D1.digit , @dtFrom) AS n
FROM
#Digits AS D1
CROSS JOIN #Digits AS D2
CROSS JOIN #Digits AS D3
WHERE
D3.digit * 100 + D2.digit * 10 + D1.digit <= @Diff
ORDER BY n;

DROP TABLE #Digits
DROP TABLE #Dates


Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62934 Visits: 13298
Good question, but an even better follow-up discussion.
Thanks for the code examples!


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
WayneS
WayneS
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21744 Visits: 10652
While reading through this, I kept asking myself "What is the author trying to teach?". Unfortunately, I never did get a good answer to my question...

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18881 Visits: 12426
WayneS (12/9/2010)
While reading through this, I kept asking myself "What is the author trying to teach?". Unfortunately, I never did get a good answer to my question...

He did tell us in the expanation: "This example shows how to quickly populate a table with successive dates."

Unfortunately, this is not the best method.
Fortunately, several better alternatives have been included in the discussion, so the objective of the author is achieved anyway ;-)


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Nils Gustav Stråbø
Nils Gustav Stråbø
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3661 Visits: 3575
I don't like questions like this, and not because I got it wrong.
It teaches me nothing about Sql Server or T-SQL (except debugging peculiar T-SQL), and to be able to answer it correctly, without cheating, you will have to manually write down how many rows are inserted in each run of the loop in order to keep track of the number in the meaningless "SomeNumberForDate" column.

64% got it right. Yeah, right!! How many cheated and ran the T-SQL code?
jts2013
jts2013
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1526 Visits: 5009
I think as long as people learn does it matter if they ran the code? :-)

This WHILE was very convoluted so I ran the code to see if I could understand what it was doing and looked at the values in #Dates before the DELETE.

So I hope I learnt that a) there are ways of populating test data quickly and b) not to use complicated WHILE loops!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search