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/

 

[gmmastros]

Vongrunt,

According to the question...

>> What function returns 0, if called first thing after connecting to SQL Server, but never returns 0 after that initial call.

If your query were run twice within the same second, it could return 0 more than once.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

hmckillop ,

Is this what you are talking about?

UPDATE table
WITH (HOLDLOCK,ROWLOCK)
SET StatusCode = 'sent'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[yelworcm]

I would imagine that you can lock the index "row" as well as the table row. I have seen deadlocks oon that very issue, in fact.

 

[druer]

Select @@ROWCOUNT will return 0 only if called as the first thing when a new connection is made, and something greater than 0 from then on. (Same answer as I posted in other thread I read, didn't realize this was where it started.)

 

[SQLDenis]

I replied in the other thread but here it is again

select @@ROWCOUNT

select * from authors where 1 = 0

select @@ROWCOUNT

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

OK, lets clean up noise...

There are two questions/problems/puzzles opened:

- How to calculate next weekday (by me)
- Show an example of when you can have more than 1 UPDLOCK on the same row (by hmckillop)

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

 

[maswien]

I have a suggestion: for each SQL puzzle, it's better to have a separate thread for it, that way it's easy to trace the question and answers.

 

[hmckillop]

In answer - yelworcm is correct, effectively you can have two UPDLOCKs on the same row as it holds the lock on the index if found by the index.

example being this -

/**
--index level locking
This example below shows that locking hints dont necessarily work the way you think.
Summary of whats going on:
Proc 1 accesses the data required via index 1 taking out an updlock
Proc 2 Accesses the same data via index 2 taking out an updlock
Proc 1 then tries to update all the rows in the data (including the field involved in index 2)
Proc 2 then tries to update all the rows in the data (including the field involved in index 1) --deadlock occurs.
Basically when you take an update lock out on the row the clustered index row isnt necessarily locked
**/
CREATE TABLE tab_hmk_IndexLockingExample
(
   PrimaryID Int ,
	FirstName Varchar(20),
	LastName Varchar(20)
)

CREATE UNIQUE CLUSTERED INDEX idx_PrimaryID ON tab_hmk_IndexLockingExample(PrimaryID)
GO
CREATE NONCLUSTERED INDEX idx_NonClustered_FirstName ON tab_hmk_IndexLockingExample(FirstName)
GO
CREATE NONCLUSTERED INDEX idx_NonClustered_LastName ON tab_hmk_IndexLockingExample(LastName)
GO
Insert into tab_hmk_IndexLockingExample(PrimaryID, FirstName, LastName) Values(1, 'Joe', 'Bloggs')
Insert into tab_hmk_IndexLockingExample(PrimaryID, FirstName, LastName) Values(2, 'Mike', 'Smith')
Insert into tab_hmk_IndexLockingExample(PrimaryID, FirstName, LastName) Values(3, 'Wayne', 'Rooney')


CREATE PROC hmk_GetDataOnFirstIndex
AS
BEGIN
	--start the transaction
	BEGIN TRANSACTION
	--select the data doing an updlock on the row - should prevent further updates to the row NOTE - accesses data on the first Index
	SELECT FirstName 
	FROM tab_hmk_IndexLockingExample WITH (UPDLOCK) 
	WHERE FirstName = 'Joe'

	WAITFOR DELAY '00:00:30'


	--Try Updating the data, including the lastname (involved in the second index)
	Update tab_hmk_IndexLockingExample
	SET FirstName = FirstName, LastName= LastName
	Where FirstName = 'Joe'
	
	--Commit Transaction
	--rollback Transaction

END

--run this proc from a seperate window
CREATE PROC hmk_GetDataOnSecondIndex
AS
BEGIN
	--start the transaction
	BEGIN TRANSACTION

	--select the data doing an updlock on the row - should prevent further updates to the row, NOTE on using different INDEX
	SELECT LastName 
	FROM tab_hmk_IndexLockingExample WITH (UPDLOCK) 
	WHERE LastName = 'Bloggs'

	WAITFOR DELAY '00:00:30'


	--Now try and update the row including the data involved in the first index
	--Update the data 
	Update tab_hmk_IndexLockingExample
	SET FirstName = FirstName, LastName = LastName
	Where Lastname = 'Bloggs'

	commit tran
END

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

 

[SQLBill]

To add to maswien's suggestions, make the subject line for puzzles something like this:


PUZZLE: What function returns 0 - sometimes.

Then the body of the post will have the full question.

-SQLBill

Posting advice: FAQ481-4875

 

[pwilson]

DECLARE @nextday int
SET @nextday = 7
SELECT DATEADD(dd,DATEDIFF(dd,'1/1/1900',getdate()) + (@nextday + DATEPART(dw,Getdate())) % 7 +1,'1/1/1900')

 

[vongrunt]

Also very close... works, general idea is OK but result depends on @@DATEFIRST. There is one simple addition left that removes that "problem".

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

 

[pwilson]

ok then

DECLARE @nextday int
SET @nextday = 7
SET DATEFIRST 1
SELECT DATEADD(dd,DATEDIFF(dd,'1/1/1900',getdate()) + (@@DateFirst + @nextday + DATEPART(dw,Getdate())) % 7 +1,'1/1/1900')

SET DATEFIRST 3
SELECT DATEADD(dd,DATEDIFF(dd,'1/1/1900',getdate()) + (@@DateFirst + @nextday + DATEPART(dw,Getdate())) % 7 +1,'1/1/1900')

SET DATEFIRST 4
SELECT DATEADD(dd,DATEDIFF(dd,'1/1/1900',getdate()) + (@@DateFirst + @nextday + DATEPART(dw,Getdate())) % 7 +1,'1/1/1900')

all seem to return the correct day.

 

[vongrunt]

Yup, that's it.

I'll post my detailed queries (and give some stars :X) tomorrow.

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

That looks rather complicated.

I think this does the trick (I hope it does, otherwise )

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

as in

--Simple Test.

DECLARE @StartDate datetime
DECLARE @WhichDay int

SET @WhichDay = 1
SET @StartDate = '20060111'

WHILE @WhichDay <= 7
BEGIN
	[b]PRINT (7 + (@WhichDay - DATEPART(dw, @StartDate))) % 7 + @StartDate[/b]
	SET @WhichDay = @WhichDay + 1
END

--Detailed Example which [B][I]hopefully[/I][/B] thoroughly demonstrates my solution.

DECLARE @OriginalDate datetime
DECLARE @StartDate datetime
DECLARE @WhichDay int

DECLARE @OriginalDateFirst int
DECLARE @CurrentDateFirst int
SET @OriginalDateFirst = @@DATEFIRST

SET @OriginalDate = '20060111'

SET @StartDate = @OriginalDate
WHILE @StartDate <= @OriginalDate + 7
BEGIN
	SET @CurrentDateFirst = 1
	PRINT 'StartDate: ' + CONVERT(varchar, @StartDate)
	PRINT 'StartDate''s Weekday: ' + CONVERT(varchar, DATEPART(dw, @StartDate))
	WHILE @CurrentDateFirst <= 7
	BEGIN
		SET DATEFIRST @CurrentDateFirst
		PRINT 'Current Value of DATEFIRST: ' + CONVERT(varchar, @@DATEFIRST)
		PRINT '===================================='
		PRINT '===================================='
		SET @WhichDay = 1
		WHILE @WhichDay <= 7
		BEGIN
			PRINT 'Required Weekday: ' + CONVERT(varchar, @Whichday)
			PRINT 'Result: ' + CONVERT(varchar, [b](7 + (@WhichDay - DATEPART(dw, @StartDate))) % 7 + @StartDate[/b])
			PRINT 'Result''s Weekday: ' + CONVERT(varchar, DATEPART(dw, (7 + (@WhichDay - DATEPART(dw, @StartDate))) % 7 + @StartDate))
			SET @WhichDay = @WhichDay + 1
			PRINT '===================================='
		END
		SET @CurrentDateFirst = @CurrentDateFirst + 1
	END
	PRINT '===================================='
	SET @StartDate = @StartDate + 1
END

SET DATEFIRST  @OriginalDateFirst
PRINT @@DATEFIRST

 

[vongrunt]

OK, first buncha stars for efforts.

Then... have you checked these results for higher SET DATEFIRST, for example 7?

Lemme recap all scalar formulas so far:

declare @WhichDay tinyint       -- US English datefirst assumed (1 - Sunday)
declare @StartDate datetime
set @StartDate = '20060111'
set @WhichDay = 1 -- next Sunday, result should be Jan 15th

-- pwilson: Jan 17th 
SELECT DATEADD(dd,DATEDIFF(dd,'1/1/1900',@StartDate) + (@@DateFirst + @WhichDay + DATEPART(dw,@StartDate)) % 7 +1,'1/1/1900')

-- earthandfire MK I: Jan 18th 
select (@StartDate - (1 - @WhichDay)) +
        (7 * case when (@StartDate - (1 - @WhichDay))= @StartDate then 1 else 0 end)

-- earthandfire MK II: Jan 15th 
set datefirst 7
SELECT (7 + (@WhichDay - DATEPART(dw, @StartDate))) % 7 + @StartDate
-- but... 
set @WhichDay = 4 -- next Wednesday, result should be Jan 18th
SELECT (7 + (@WhichDay - DATEPART(dw, @StartDate))) % 7 + @StartDate

Maybe I'm / a lot (worked yesterday for 15 hours)... so feel free to correct me If I missed something.

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

My misunderstanding.

This should do the trick:

PRINT 7 + (@WhichDay - DATEPART(dw, @StartDate)) + @StartDate

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

I took this to mean that if today was Friday, then use today (not next Friday).

I only used the MOD operator % to convert a result of 7 to 0, so by removing it 7 remains.

Hope this makes sense. It did when I wrote it, it doesn't when I re-read it.

 

[earthandfire]

Ignore my last reply - its wrong.

 

[earthandfire]

Try this:

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

By the way, vongrunt, thanks for the star.

 

[SQLDenis]

I have a good one for this coming Friday
Have some HD space you will need it
The fastest execution will get stars for this one

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[pwilson]

declare @WhichDay tinyint       -- US English datefirst assumed (1 - Sunday)
declare @StartDate datetime
set @StartDate = '20060111'
set @WhichDay =  1-- next Sunday, result should be Jan 15th

//returns 1-16
SET DATEFIRST 7
SELECT (6 + (@WhichDay + DATEPART(dw, @StartDate))) % 7 + @StartDate + 1

//returns 1-18
SET DATEFIRST 5
SELECT (6 + (@WhichDay + DATEPART(dw, @StartDate))) % 7 + @StartDate + 1

I combined what we had so far into

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

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

But my code returns the same date for today
ie
Next wed and if today is wed will return today instead of the next wed.

declare @WhichDay tinyint       -- US English datefirst assumed (1 - Sunday)
declare @StartDate datetime
set @StartDate = '20060111'
set @WhichDay = 4-- next wednesday, result should be Jan 18th

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

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