Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Build date table Expand / Collapse
Author
Message
Posted Wednesday, December 8, 2010 8:58 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 23, 2012 6:47 AM
Points: 15, Visits: 134
Comments posted to this topic are about the item Build date table
Post #1032267
Posted Wednesday, December 8, 2010 9:09 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
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.)
Post #1032271
Posted Wednesday, December 8, 2010 9:15 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 9:12 AM
Points: 1,676, Visits: 1,755
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
Post #1032273
Posted Thursday, December 9, 2010 1:21 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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

Post #1032320
Posted Thursday, December 9, 2010 1:29 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 2:02 AM
Points: 947, Visits: 887
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

Post #1032323
Posted Thursday, December 9, 2010 2:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 13,639, Visits: 10,534
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1032342
Posted Thursday, December 9, 2010 4:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:04 PM
Points: 6,594, Visits: 8,882
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
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
Post #1032370
Posted Thursday, December 9, 2010 5:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:28 PM
Points: 5,984, Visits: 8,242
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
Post #1032384
Posted Thursday, December 9, 2010 5:39 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 4:51 AM
Points: 1,854, Visits: 3,451
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?

Post #1032388
Posted Thursday, December 9, 2010 6:36 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 24, 2014 4:28 AM
Points: 1,100, Visits: 4,898
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!
Post #1032424
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse