Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Build date table


Build date table

Author
Message
Paul Peeters
Paul Peeters
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 134
Comments posted to this topic are about the item Build date table
UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2280 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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1693 Visits: 1808
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
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3494 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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1198 Visits: 1226
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16455 Visits: 13207
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?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6235 Visits: 10403
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
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8323 Visits: 11579
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ø
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2013 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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1176 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