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


Build date table


Build date table

Author
Message
Toreador
Toreador
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2758 Visits: 8083
Nils Gustav Stråbø (12/9/2010)
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.


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.
kevin.l.williams
kevin.l.williams
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3413 Visits: 1323
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? :-)



Dan Guzman - Not the MVP
Dan Guzman - Not the MVP
SSC Eights!
SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)

Group: General Forum Members
Points: 904 Visits: 740
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? :-)


Kevin gets a 'like'
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28743 Visits: 9671
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.

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




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.
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10846 Visits: 11970
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!


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28743 Visits: 9671
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ø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2429 Visits: 3575
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 Cool
Paul Peeters
Paul Peeters
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 134
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
SanDroid
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1574 Visits: 1046
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 Cool
SanDroid
SanDroid
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1574 Visits: 1046
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 Cool


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