SQL puzzles 4

[SQLDenis]

What if we come up with some SQL puzzles/problems
One of us posts one every Friday and we have by Monday to give answers. The best answer(s) gets stars
This is a good way to improve SQL skills (and learn new tricks) and get away from the TV during those log winter nights

Just an idea let me know what you think



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[pwilson]

Changing the +6 to +5 then maving the +1 to the outside seems to do it

declare @WhichDay tinyint       -- US English datefirst assumed (1 - Sunday)
declare @StartDate datetime
set @StartDate = '20060111'
set @WhichDay = 4-- next wed 1-18

//1-18
SET DATEFIRST 7
SELECT DATEADD(dd,DATEDIFF(dd,'1/1/1900',@StartDate) + (@@DATEFIRST + 5+ @WhichDay + DATEPART(dw,@StartDate)) % 7 +1,'1/1/1900')

//1-18
SET DATEFIRST 4
SELECT DATEADD(dd,DATEDIFF(dd,'1/1/1900',@StartDate) + (@@DATEFIRST + 5+ @WhichDay + DATEPART(dw,@StartDate)) % 7 + 1,'1/1/1900')

//1-18
SET DATEFIRST 2
SELECT DATEADD(dd,DATEDIFF(dd,'1/1/1900',@StartDate) + (@@DATEFIRST + 5+ @WhichDay + DATEPART(dw,@StartDate)) % 7 + 1,'1/1/1900')

 

[vongrunt]

In that case, result for StartDate='20060115' and @WhichDay=1 should be... ?

And btw. all that DATEADD/DATEDIFF stuff has only one indirect purpose: to cut off time fraction. If that's not necessary you can simplify last expression to:

SELECT @StartDate + 1 + (@@DATEFIRST + 5+ @WhichDay + DATEPART(dw,@StartDate)) % 7

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[earthandfire]

If my understanding of DateFirst is correct, and based on the example in BOL I've put together the following table - which seems to confirm my modified solution:

[b]          DateFirst            
Weekday   1  2  3  4  5  6  7
=============================
Sunday[/b]    7  6  5  4  3  2  1
[b]Monday[/b]    1  7  6  5  4  3  2
[b]Tuesday[/b]   2  1  7  6  5  4  3
[b]Wednesday[/b] 3  2  1  7  6  5  4
[b]Thursday[/b]  4  3  2  1  7  6  5
[b]Friday[/b]    5  4  3  2  1  7  6
[b]Saturday[/b]  6  5  4  3  2  1  7

 

[vongrunt]

> If my understanding of DateFirst is correct, and based on the example in BOL I've put together the following table - which seems to confirm my modified solution:

Now I am confused... which one? With 6 or 7?
(new thread would be nice... I think).

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[earthandfire]

PRINT (6 + (@WhichDay + DATEPART(dw, @StartDate))) % 7 + @StartDate + 1

 

[hneal98]

Just a stupid question, but some of us are not DBA's and don't have access to all the functions and sp's that sql server has to offer. We just create stored procedures, and views.

I would like to see puzzles for SQL it self. Perhaps another forum could be started for DBA puzzles and one for SQL puzzles?

Any thoughts? (By the way, all of you are way beyond me in skill. I really enjoy reading the posts by many of you when you are helping others and when you have helped me.)

 

[vongrunt]

We are mixing one thing...

I assumed "hard-coded" @WhichDay semantics - always Sunday first - and code behaviour unaffected by current @@DATEFIRST.

Your code interpretes @WhichDay according to current @@DATEFIRST. Is that true?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[earthandfire]

Yes, @WhichDay is determined by @@DATEFIRST as opposed to always being Sunday or any other specific day. That was why I did the detailed test example (albeit with 7 as opposed to 6) so that I could verify every combination.

 

[earthandfire]

hneal98, vongrunt's date puzzle doesn't require anything other than standard sql and a very sore head , but I do agree with you with regard to some of the other puzzles.

 

[hneal98]

I didn't see that. This post has gotten very long and they started out with some other functions that someone like me would never use.

most here are very experienced and I could see this being a great learning tool.

 

[Walid]

I agree this thread, or the idea in general, is great learning tool and many thanks to the originator and all the contributors. But I hate to see the post getting too long to the point that extracting info from it will be tedious at best.

I suggest starting each puzzle in a new thread and connect all of them by a naming conversion like SQL-PUZZLE <the subject goes here>. We may also start a new FAQ that contain the URL (the thread #) for each new puzzle and a brief description about he subject. FAQs are editable so we can keep them up to date.

What do you guys think?

Walid Magd (MCP)

The primary challenge of every software development team is to engineer the illusion of simplicity in the face of essential complexity.
-Grady Booch

 

[vongrunt]

Yup...

I posted this date puzzle simply because it's solution becomes very handy. And there are at least 4-5 different ways to do that... solid hardcore interviewing question. Unfortunately, entire thread bloated for various reasons (me included).

Next time we'll do one puzzle per thread... and I am not great fan of DBA/BOL trivia questions either, though some of those questions are quite interesting.

For earthandfire: Btw. let's continue . With SET DATEFIRST 7, @StartDate='20060111' and @WhichDay=4... what you expect/get for result?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[earthandfire]

With @@DATEFIRST 7, 4 is Wednesday therefore (since today is Wednesday), next Wednesday 18th Jan, 2006

DECLARE @StartDate datetime
DECLARE @WhichDay int

SET DATEFIRST 7
SET @WhichDay = 4
SET @StartDate = '20060111'
PRINT (6 + (@WhichDay - DATEPART(dw, @StartDate))) % 7 + 1 + @StartDate

 

[vongrunt]

Ya know, one + instead of - makes big difference .

This one looks fine IMHO.

To solve another variation of that problem - interpretation of @WhichDate hard-coded, result unaffected by SET DATEFIRST - anyone interested may take a look at faq183-5074 (note: example from there works with Monday=1 rule).

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)