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/

 

[vongrunt]

Chaos?

------
"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)

 

[SQLDenis]

You have to answer both parts

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Hm... chaos is read uncommitted crippled for held update locks and complex transactions (no rollback). It presents no danger only if transactions don't matter and updates cannot interfere with anything else, including other processes from the same user - or even parallelism in exec plan.

So... it should be harmless for complete read-only, and single-user with some precautions mentioned above.

But... I am not sure SQL Server engine supports chaos level. You can eventually simulate it with READ UNCOMMITTED and no BEGIN TRAN in code.

------
"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)

 

[SQLDenis]

vongrunt,

keep digging you will find it
another hint...you can select it from a dropdown

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

What? DTS?

------
"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)

 

[SQLDenis]

yep right click in package
package propertes -->advanced go to the transactions part and you will see chaos is the first one in the dropdown (but not the default one)
star for effort

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Awright... next puzzle/problem:

For specified date, calculate next occurence of weekday. For example, next Friday starting (but not including!) from today.

------
"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)

 

[r937]

you should stick to SQL puzzles and start a separate thread for DBA puzzles

some of us who know SQL couldn't admin our way out of a paper bag

i never heard of all those @foo things you guys are talking about

r937.com | rudy.ca

 

[vongrunt]

OK, that "next weekday" thing is simple SQL puzzle... for change.

Side note: get-it-done anwsers - with WHILE loop or buncha CASE/WHEN statements - automatically translate into . We can do better than that.

------
"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)

 

[SQLDenis]

vongrunt assuming you have a number table this is very easy

--build number table
-- create table numbers (id int primary key)
-- declare @id int
-- select @id =1
-- while @id <= 10
-- begin
-- insert into numbers
-- select @id
-- set @id = @id +1
-- end

declare @dtmdate datetime
select @dtmdate = getdate() -- change this to any day

select min(dateadd(d,id,@dtmdate))
from numbers
where datepart(dw,dateadd(d,id,@dtmdate) ) between 2 and 6

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[earthandfire]

Without a number table then ...

declare @d as datetime
declare @dow as int

--set @dow to 1 = Sunday .... 7 = Saturday
set @dow = 1
--replace getdate() with any chosen date
set @d = 
		(getdate() - (1 - @dow)) + 
		(7 * case when (getdate() - (1 - @dow))= getdate() then 1 else 0 end)

print @d

... seems to work, with datefirst set to anything between 1 and 7

 

[earthandfire]

On reflection:


(7 * case when (getdate() - (1 - @dow))= getdate() then 1 else 0 end)


would be better as:


(case when (getdate() - (1 - @dow))= getdate() then 7 else 0 end)

 

[vongrunt]

... assuming you have a number table this is very easy

True. But IMHO that problem is not worth using calendar table - even if you have one handy.

Without a number table then ...

Yup. Now, try it without conditional statements (CASE/WHEN)... (hint: % ).

------
"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)

 

[r937]

hint: %

oh, man, did you have to? so soon? hints should not appear before wednesday or thursday for a weekly friday puzzle

r937.com | rudy.ca

 

[yelworcm]

No one has gotten the answer to my question yet? I am almost disappointed.

 

[SQLDenis]

well time for a hint then

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLBill]

yelworcm,

You got a few answers, but you never responded as to whether they were correct or not. If you are going to post a question, you have to let responders know if they are right or not. Not knowing, they moved on to another question.

-SQLBill

Posting advice: FAQ481-4875

 

[yelworcm]

Sorry. I thought the rules were to give a question on Friday, then reveal the answer on Monday. I will still leave you in suspense for a little while longer (so long as I do not get buried by other problems here).

 

[vongrunt]

Hm... is there any function doing something like:

select datediff(ss, login_time, last_batch)
from master..sysprocesses 
where spid = @@spid

?

------
"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)

 

[hmckillop]

Ok I have a question or two.

Show an example of when you can have more than 1 UPDLOCK on the same row.
(its easy and has been described as a "feature" by MS)



"I'm living so far beyond my income that we may almost be said to be living apart