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 Thursday, December 09, 2010 8:31 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:53 AM
Points: 1,659, Visits: 6,008
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.
Post #1032486
Posted Thursday, December 09, 2010 9:37 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, March 28, 2014 11:01 AM
Points: 3,047, Visits: 1,229
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?



Post #1032545
Posted Thursday, December 09, 2010 10:10 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, September 30, 2013 9:43 AM
Points: 710, Visits: 664
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'
Post #1032568
Posted Thursday, December 09, 2010 10:26 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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.
Post #1032578
Posted Thursday, December 09, 2010 11:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:48 PM
Points: 5,801, Visits: 8,019
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?


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
Post #1032633
Posted Thursday, December 09, 2010 11:55 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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?


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.
Post #1032643
Posted Thursday, December 09, 2010 12:23 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:35 AM
Points: 1,832, Visits: 3,402
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
Post #1032657
Posted Thursday, December 09, 2010 1:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 23, 2012 6:47 AM
Points: 15, 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;

Post #1032709
Posted Thursday, December 09, 2010 1:41 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
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
Post #1032714
Posted Thursday, December 09, 2010 2:01 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
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?

My apologies for responding Off Topic to an already Off topic post...
Post #1032728
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse