Build date table

Question

Build date table

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

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

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.

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 2

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 😎

Paul Peeters SSC Enthusiast Points: 127 More actions · Answer 3

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;

SanDroid SSChampion Points: 10068 More actions · Answer 4

Toreador (12/9/2010)
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 😎

SanDroid SSChampion Points: 10068 More actions · Answer 5

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... 😎

OCTom SSChampion Points: 11785 More actions · Answer 6

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 :-)).

WayneS SSC Guru Points: 95460 More actions · Answer 7

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!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Kevin SSCarpal Tunnel Points: 4962 More actions · Answer 8

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. 😀

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 9

Good Question.good discussions

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

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

TomThomson SSC Guru Points: 104773 More actions · Answer 11

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

Dhruvesh Shah SSCrazy Points: 2303 More actions · Answer 12

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

tejaswini.patil SSCommitted Points: 1812 More actions · Answer 13

Good question followed by even better discussion.

Good learning for me.

Thanks Guys. 🙂