# Build date table

• Hugo Kornelis (12/9/2010)

kevin.l.williams (12/9/2010)

By the way, how many of you read the forum comments and stop after you get to Hugo's? 🙂

:laugh:

If that number is significantly large, I'll have to consider postponing my comments, so that other valuable comments get read as well. 🙂

Thanks for the kind words!

Well I'm one of the many who scroll fast untill I see Hugo, but then again I usually read everything.

• I'd like the link that tells us we can't run the t-sql before answering. I can't remember ever seeing that post anywhere.

That's cheating in my world 😎

• I want to thank everyone for the valuable comments. Using those comments and a little googling (Itzik Ben-Gan's number generator at http://www.projectdmx.com/tsql/tblnumbers.aspx#Row and Shannon Severance's answer on a continuous dates question http://efreedom.com/Question/1-1395367/Return-Temp-Table-Continuous-Dates?showall=true#additionalAnswersMarker), I constructed a far better solution.

Anyway, I have learned by posting this little puzzle and I hope some others have too. 😉

`DECLARE @dtFrom DATETIME;`

`DECLARE @dtTo DATETIME;`

`DECLARE @dtStart DATETIME;`

`SELECT`

` @dtFrom = '2010-01-01T00:00:00.000',`

` @dtTo = '2010-12-31T00:00:00.000';`

`SET @dtStart = GETDATE();`

`CREATE TABLE #Dates`

`(`

`[Date] DATETIME NOT NULL PRIMARY KEY,`

`[SomeValueForDate] INT NOT NULL`

`);`

`WITH`

`n2 (n) AS (SELECT 1 UNION ALL SELECT 0), -- 2 entries`

`n4 (n) AS (SELECT 1 FROM n2 x CROSS JOIN n2 y), -- 4 entries`

`n16 (n) AS (SELECT 1 FROM n4 x CROSS JOIN n4 y), -- 16 entries`

`n256 (n) AS (SELECT 1 FROM n16 x CROSS JOIN n16 y), -- 256 entries`

`n65536 (n) AS (SELECT 1 FROM n256 x CROSS JOIN n256 y), -- 65536 entries`

`nmax (n) AS (SELECT 1 FROM n65536 x CROSS JOIN n256 y), -- 16777216 entries (would never be reached as max date = 9999-12-31T00:00:00 = 2958463)`

`num (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY n) FROM nmax)`

`INSERT INTO`

`[#Dates]`

`SELECT`

`DATEADD(DAY, n-1, @dtFrom),`

`n`

`FROM`

`num`

`WHERE`

`n <= DATEDIFF(DAY, @dtFrom, @dtTo) + 1;`

`SELECT DATEDIFF(MS, @dtStart, GETDATE()) AS [Runtime in msec];`

`SELECT * FROM [#Dates] ORDER BY 1;`

`DROP TABLE #Dates;`

I got it right by immediately eliminating the 1 and 365 options, then I couldn't spot any syntax errors, so went for the one that was left.

I took the same route and then ran the code anyway to be sure.

I can see where this would have made an interesting SCRIPTS submission, but does not seem to be a question that teaches anything.

It did reinforce something I learned in high school.

You HAVE TO RUN CODE to know what it will do, otherwise you are just guessing.

C code

C code run

run code run 😎

• Nils Gustav Stråbø (12/9/2010)

I'd like the link that tells us we can't run the t-sql before answering. I can't remember ever seeing that post anywhere.

That's cheating in my world 😎

In my world this is testing a theory, trying to turn a guess into a fact.

In my world this reduces mistakes, increases learning, decreases frustration. 😉

In your world I have cheated every day for the last 30 years. Even the day I took my SATs.

Do guesses instantly turn into tested fact in your world?

Can I come live there so all my Lotto tickets are winners? :w00t:

My apologies for responding Off Topic to an already Off topic post... 😎

• Nils Gustav Stråbø (12/9/2010)

I'd like the link that tells us we can't run the t-sql before answering. I can't remember ever seeing that post anywhere.

That's cheating in my world 😎

I suppose it would be cheating if this was for something meaningful such as money, a job, or a degree. But to collect points for fun is not something I'd worry about cheating for.

The important thing is to learn something; whether you run the code first, last, or not all shouldn't matter (IMO :-)).

• kevin.l.williams (12/9/2010)

Nils Gustav Stråbø (12/9/2010)

64% got it right. Yeah, right!! How many cheated and ran the T-SQL code?

I cheated and got it right!

By the way, how many of you read the forum comments and stop after you get to Hugo's? 🙂

Isn't that the way this works here?

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!
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

• Hugo Kornelis (12/9/2010)

kevin.l.williams (12/9/2010)

By the way, how many of you read the forum comments and stop after you get to Hugo's? 🙂

:laugh:

If that number is significantly large, I'll have to consider postponing my comments, so that other valuable comments get read as well. 🙂

Thanks for the kind words!

HaHa, You will propbably never know the true number since my comment was after yours. 😀

• Good Question.good discussions

Malleswarareddy
I.T.Analyst
MCITP(70-451)

• Thanks for the question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

• Nils Gustav Stråbø (12/9/2010)

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.

No, you don't need to do that.

You can see that 512 is greater than 365, so the last rows added have SomeNumberForDate = 256. Before that the last row added; and number of rows adsded before then was clearly 256 (it's all in poowers of two, obviously). There are 365 rows in all and and 256 with SomeNumberForDate 128 or less, and that leaves just 109 that have SomeNumberForDate = 256.

All you have to see is that the number of rows added each time is a power of 2, that the number of rows in teh table after each set is added is a power of 2, that there are 365 days in the year 2010, and that 512 is the smallest power of two greater than 365. No manually writing down meaningless numbers required.

I guess that learning to thing mathematically is a bit like learning to think set-oriented: is you don't learn it you end up doing things the hard way.

If it had been a question designed to check people's ability to do simple maths, it would have been a good question; but as it was apparently meant to teach something about SQL, it didn't achieve its objective. But there's some good SQL in the comments, so it was at least a useful question.

Tom

• Too big code i just ran the code. but yeah i did learned bit about date and then got lost...

• Good question followed by even better discussion.

Good learning for me.

Thanks Guys. 🙂

Viewing 13 posts - 16 through 27 (of 27 total)