Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL puzzles 4

Status
Not open for further replies.
SQLDenis

SQLDenis

Programmer
Joined
Oct 1, 2005
Messages
5,575
Location
US
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:
Personal Blog:
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.)
 
Upvote 0 Downvote
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)
[banghead]
 
Upvote 0 Downvote

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.
 
Upvote 0 Downvote
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 -
Code: 
/**
--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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Code: 
DECLARE @nextday int
SET @nextday = 7
SELECT DATEADD(dd,DATEDIFF(dd,'1/1/1900',getdate()) + (@nextday + DATEPART(dw,Getdate())) % 7 +1,'1/1/1900')

 
Upvote 0 Downvote
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)
[banghead]
 
Upvote 0 Downvote
ok then

Code: 
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.
 
Upvote 0 Downvote
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)
[banghead]
 
Upvote 0 Downvote
That looks rather complicated.

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

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

as in

Code: 
--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

Code: 
--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

[vampire][bat]
 
Upvote 0 Downvote
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:
Code: 
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 [yawn]/[spineyes] 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)
[banghead]
 
Upvote 0 Downvote
My misunderstanding.

This should do the trick:

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


vongrunt said:
For specified date, calculate next occurence of weekday. For example, next Friday starting (but not including!) from today.
Click to expand...

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.

[vampire][bat]
 
Upvote 0 Downvote
Try this:

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

By the way, vongrunt, thanks for the star.

[vampire][bat]
 
Upvote 0 Downvote
Code: 
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
Code: 
//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.
Code: 
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')

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

MathiasB
  • Locked
  • Question Question
Parameterised sql server query
Replies
8
Views
859
Chriss Miller
C
gbaughma
  • Locked
  • Question Question
SQL Performance
Replies
11
Views
1K
pjw001
pjw001
Steve Yu
  • Locked
  • Question Question
How secure is MS SQL on the Internet vs behind firewall inside a LAN
Replies
3
Views
868
Chris Miller
Chris Miller
V
  • Locked
  • Question Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
998
Volvere
V
elly00
  • Locked
  • Question Question
create sql view using batch file
Replies
5
Views
948
Mike Lewis
Mike Lewis

Part and Inventory Search

Sponsor

Back
Top