Date Linked with Week of Month Query

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 2000 Forums

Transact-SQL (2000)

Date Linked with Week of Month Query

Author

Topic

adlo
Posting Yak Master

108 Posts

Posted - 2004-11-02 : 11:17:38

I trying to do a query that link the Sundays of a spesific Month with a week number (of that month).
E.g.
Sunday 6 Nov = 1
Sunday 13 Nov = 2

I have got a table with the Sunday Dates for that month and a table with weeknumbers(1,2,3,4,5).
How do I link them?

Is there perhaps a way to assign a new field with the value of the row number of each spesific row?

Thanks for any help

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-02 : 11:26:31

[code]
Declare @myTable table (sunday datetime)
Insert Into @myTable
Select '11/6/2004'
Union Select convert(datetime,'11/6/2004')+7
Union Select convert(datetime,'11/6/2004')+14

Select
*,
WeekNum = (Select count(*) From @myTable Where sunday < A.sunday)+1
From @myTable A
[/code]

Corey

adlo
Posting Yak Master

108 Posts

Posted - 2004-11-02 : 11:35:27

SundayTable (Created using Select Statement)
2004-11-07 00:00:00.000
2004-11-14 00:00:00.000
2004-11-21 00:00:00.000
2004-11-28 00:00:00.000

and a got a table
WeekNoTable
1
2
3
4
5

Is there any other way to this without using your way which looks more complicated than needed.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-02 : 12:06:49

not sure why you need a table of Week Numbers ....

you can do something like this:



declare @somedate datetime;

set @somedate = '11/28/2004'

select case month(@somedate) when month(@somedate-28) then 5
                             when month(@somedate-21) then 4
                             when month(@somedate-14) then 3
                             when month(@somedate-7) then 2
			     else 1
	end as WeekNo

Obivously, SELECT from your table, applying the CASE logic to your date column of Sundays, instead of using the variable in my example. Try it out, it should work.

(that's one of the weirdest CASE expressions you'll ever see, I bet)

- Jeff

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-02 : 15:57:18

So what am I missing...

select (day(@somedate)-1) / 7 + 1 as weekno

rockmoose

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-02 : 15:59:40

yeah that works too