Querying for fun! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Querying for fun!

Author

Topic

theboyholty
Posting Yak Master

226 Posts

Posted - 2009-06-12 : 05:16:15

Its Friday, the boss is off and I can't be bovrilled doing the work I'm paid to do so I've written this query with the intention of calculating the frequency with which a given date, falls on a particular day of the week over a period of a number of years.

The query below calculates the years when new years day was a monday between the years 1800 and 4800. It doesn't rely on any tables so you can paste it into your query window and run it.

As you'll see if you run this, the frequency generally is 6 5 6 11 6 5 6 11 etc but leap years and century years skew that cycle slightly so I've done it over 3000 years but i can't quite finish it.


CREATE TABLE #Dates (
TheDate datetime,
WeekDayNum int)

DECLARE @DATE datetime
SET @DATE = {d '1800-01-01'}
DECLARE @COUNT int
SET @COUNT = 1

WHILE @COUNT <= 3000
BEGIN
	INSERT INTO #Dates (TheDate,WeekDayNum)	
	SELECT @DATE as TheDate,datepart(weekday,@DATE) as WeekDayNum
	SET @DATE = DATEADD(yy,1,@DATE)
	SET @COUNT = @COUNT+1
END

SELECT YEAR(TheDate) as NewYearMondays FROM #Dates WHERE WeekDayNum = 2

DROP TABLE #Dates

Problem is, now I've got the years in one column but I need to run a further query to work out the gap between each entry in the column. Come on fellow geeks, help me out here.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-12 : 05:40:12

Sorry for my lack of knowledge...can some1 explain to me wat is weekday in datepart?it seems to be No. in the month but im not quite sure

theboyholty
Posting Yak Master

226 Posts

Posted - 2009-06-12 : 05:46:03

Its day of the week. Sunday is 1, Monday is 2 Wednesday is 3 etc. Well it is on my system anyway - you can set Monday to be 1 for example if you want.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-12 : 05:50:02

Weekday is day of week; Monday, Tuesday, Wednesday, Thursday, Friday, Saturday or Sunday.

E 12°55'05.63"
N 56°04'39.26"

theboyholty
Posting Yak Master

226 Posts

Posted - 2009-06-12 : 05:58:07

You'd have to use DATENAME to get 'Friday', rather than DATEPART which is what I used.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-12 : 06:00:01

urs question...i oni can think it require datedif, but i still unable to create a query out haha

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-12 : 06:01:33

quote:
Originally posted by theboyholty

DECLARE @DATE datetime
SET @DATE = {d '1800-01-01'}

Why, why why, are you invoking a ODBC call here?

E 12°55'05.63"
N 56°04'39.26"

theboyholty
Posting Yak Master

226 Posts

Posted - 2009-06-12 : 06:09:44

Go on then smartypants, how would you have set a date parameter?

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-12 : 06:12:09

[code]
;with data ([year], row_no)
as
(
select [year] = NUMBER,
row_no = row_number() over (order by NUMBER)
from F_TABLE_NUMBER_RANGE(1900, 9999)
where datename(weekday, dateadd(year, NUMBER - 1900, 0)) = 'Monday'
)
select d1.[year], d2.[year], gap = d1.[year] - d2.[year]
from data d1
left join data d2 on d1.row_no = d2.row_no + 1
order by d1.[year]
[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-12 : 06:13:26

quote:
Originally posted by theboyholty

Go on then smartypants, how would you have set a date parameter?

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club


DECLARE @DATE datetime
SET @DATE = '1800-01-01'

KH
[spoiler]Time is always against us[/spoiler]

theboyholty
Posting Yak Master

226 Posts

Posted - 2009-06-12 : 06:14:57

OK. Not being funny or owt, but what difference does it make?

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-12 : 06:20:17

You are calling another layer, outside the scope of SQL Server.

E 12°55'05.63"
N 56°04'39.26"

mikebird
Aged Yak Warrior

529 Posts

Posted - 2009-06-12 : 06:22:50

Just beautiful! No tables until built. That's goin' in my liddle library. Notify me o' da updates.

I'll read the comments, now...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-12 : 06:29:28

wahhh settled ady...i still half way thinking...T.T

theboyholty
Posting Yak Master

226 Posts

Posted - 2009-06-12 : 06:35:52

quote:
You are calling another layer, outside the scope of SQL Server

Well thanks, I did not know that it made any diff.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-12 : 21:39:07

Sorry i know i not so smart, so i totally copy khtan ideas and put into urs query...
ps. if khtan u dislike this post plz do tell me, i will del it

CREATE TABLE #Dates (
TheDate datetime)

DECLARE @DATE datetime
SET @DATE = {d '1800-01-01'}
DECLARE @COUNT int
SET @COUNT = 1

WHILE @COUNT <= 3000
BEGIN
	if datepart(weekday,@DATE) = 2
	BEGIN
		INSERT INTO #Dates (TheDate)	
		SELECT @DATE as TheDate
	END
	SET @DATE = DATEADD(yy,1,@DATE)
	SET @COUNT = @COUNT+1
END

SELECT	datepart(year, d1.TheDate) as startyear, 
	datepart(year, d2.TheDate) as endyear, 
	(datepart(year, d1.TheDate)-datepart(year, d2.TheDate)) as gap
FROM	(SELECT	row_number() over (order by Thedate) as countx, Thedate
	FROM	#Dates)d1
	Left Join
	(SELECT	row_number() over (order by Thedate) as countx, Thedate
	FROM	#Dates)d2 on d1.countx = d2.countx + 1
ORDER BY d1.TheDate

DROP TABLE #Dates

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-12 : 22:23:31

no worries.

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-12 : 22:29:49

;with data ([year], row_no)
as
(
select [year] = NUMBER,
row_no = row_number() over (order by NUMBER)
from F_TABLE_NUMBER_RANGE(1800, 9999)
where datename(weekday, dateadd(year, NUMBER - 1800, 0)) = 'Monday'
)
select d1.[year], d2.[year], gap = d1.[year] - d2.[year]
from data d1
left join data d2 on d1.row_no = d2.row_no + 1
order by d1.[year]

Msg 517, Level 16, State 1, Line 1
Adding a value to a 'datetime' column caused overflow.

but when i put 1800 as starting date, the error pop out

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-12 : 23:47:35

the max date is 9999-12-31 for datetime data type

you are generating 9999 - 1800 = 8199 number of years with the F_TABLE_NUMBER_RANGE

date 0 is '1900-01-01'
this statement "dateadd(year, NUMBER - 1800, 0)" will add 8199 years to 1900 which gives 10099 and that exceed the max date of year 9999

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-12 : 23:51:11

quote:
Originally posted by waterduck

;with data ([year], row_no)
as
(
select [year] = NUMBER,
row_no = row_number() over (order by NUMBER)
from F_TABLE_NUMBER_RANGE(1800, 9999)
where datename(weekday, dateadd(year, NUMBER - 1900, 0)) = 'Monday'
)
select d1.[year], d2.[year], gap = d1.[year] - d2.[year]
from data d1
left join data d2 on d1.row_no = d2.row_no + 1
order by d1.[year]

Msg 517, Level 16, State 1, Line 1
Adding a value to a 'datetime' column caused overflow.

but when i put 1800 as starting date, the error pop out

also the dateadd part should - 1900 cause the reference date 0 is year 1900-01-01.

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-13 : 00:43:22

ic, thx for explaining, i really learn much from u hehe almost like my teacher...^^

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources