Week Number - 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

General SQL Server Forums

New to SQL Server Programming

Week Number

Author

Topic

learntsql

524 Posts

Posted - 2009-08-31 : 08:49:08

Hi
How to find the week number for given month and year
ThankQ.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-31 : 08:51:20

User datepart function

Madhivanan

Failing to plan is Planning to fail

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-08-31 : 08:52:31

select datepart(wk,'08/01/2009')

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

learntsql

524 Posts

Posted - 2009-08-31 : 08:58:21

Senthil what you are proposing is for all months
but i want weeknumber for my given inputs month number and year;
My requirement is i have datecolumn and users column;
i want to find out how many jobs completed by a user in all weeks of that months.

My output should like
User--Week1--Week2--Week3--Week4
User1--12--33--22--43
...
...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-31 : 09:08:10

are you using SQL Server 2005 / 2008 ?

what is the definition of your week ?

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

learntsql

524 Posts

Posted - 2009-08-31 : 09:09:36

I am Using SQL Server 2005.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-31 : 09:19:07

something like this ?


declare @sample table
(
	[user]		int,
	[datecol]	datetime
)

insert into @sample
select	1, '2009-08-01'	union all
select	2, '2009-08-06'	union all
select	3, '2009-08-12'	union all
select	1, '2009-08-31'	union all
select	2, '2009-08-20'

select	[user], [1], [2], [3], [4], [5], [6]
from
(
	select	[user], 
		week_no = datepart(week, datecol) 
			- datepart(week, dateadd(month, datediff(month, 0, datecol), 0)) + 1
	from	@sample
) d
pivot
(
	count(week_no)
	for week_no in ([1], [2], [3], [4], [5], [6]) 
) p

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

learntsql

524 Posts

Posted - 2009-08-31 : 09:28:40

Thanks Khtan It worked,
but cant we make month numbers dynamically in pivot clause.
Or how can we write for Matrix report.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-31 : 09:31:52

quote:
Originally posted by learntsql

Thanks Khtan It worked,
but cant we make month numbers dynamically in pivot clause.
Or how can we write for Matrix report.

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources