Week returns to 1, 2, 3, 4 or 5

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)

Week returns to 1, 2, 3, 4 or 5

Author

Topic

jeeve01
Starting Member

25 Posts

Posted - 2009-05-13 : 23:26:01

may you pls help me how to generate returns 1 2 3 4 or 5 week number for every month.

Thank you in advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-13 : 23:29:09

What is your definition of week no of the month ? Week 1 of the month starts from 1st of the month ?

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

jeeve01
Starting Member

25 Posts

Posted - 2009-05-14 : 00:02:04

Week equivalent (or definition) for every quarter is equal to 5 weeks for 1st month and 4 weeks for second month as well as for the third month. We may call it 544 cycle. First month begins with january.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-14 : 00:11:14

[code]
select week_no = ((day(getdate() - 1) / 7) + 1
[/code]

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

jeeve01
Starting Member

25 Posts

Posted - 2009-05-14 : 01:23:05

im not sure if this script would return the desired result for not all given figure of days fall in the same number of weeks.

let me site a given sample data:
yyyy/mm/dd return to a value of meaning
2009/01/01 1 week 1 of the month
2009/01/30 5 week 5 of the month
2009/02/19 4 week 4 of the month
2009/03/30 1 week 1 of the month

for dw doesnt help
DATEPART(DW,'2009/03/30) result to 14 or 14th weeks of the year.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-14 : 02:23:40

quote:
2009/02/19 4 week 4 of the month
2009/03/30 1 week 1 of the month

why 2009-02-19 is Week 4 of the month ?
and 2009-03-30 is Week 1 of the month ?

Please define what do you consider week 1 of the month, week 2 of the month etc.

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

jeeve01
Starting Member

25 Posts

Posted - 2009-05-14 : 03:24:20

you're right!!!! KH.

i go around with ur formula

DATEPART(DAY, getdate() - 1) / 7 + 1 = week with in the month

Thanks a lot.

4? its an error. i think it should be 3.

'till next odds.

jeeve01
Starting Member

25 Posts

Posted - 2009-05-14 : 03:51:28

....but this script returns March 30, 2009 as week 5, using a datefirst of 7.

Under 544 cycle, this should return to week 4. ....have an idea.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-14 : 03:53:49

quote:
Originally posted by jeeve01

....but this script returns March 30, 2009 as week 5, using a datefirst of 7.

Under 544 cycle, this should return to week 4. ....have an idea.

Can you explain more on the 544 cycle ? Why under this 544 cycle, 2009-03-30 is Week 5 ? Based on this 544 cycle, what are the dates for Week 1, Week 2, Week 3, Week 4 ?

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

jeeve01
Starting Member

25 Posts

Posted - 2009-05-14 : 04:05:14

quote:
Originally posted by jeeve01

....but this script returns March 30, 2009 as week 5, using a datefirst of 7.

Under 544 cycle, this should return to week 4. ....have an idea.

544 means
January = 5 weeks
February = 4 weeks
March = 4 weeks

i mean... under 544 cycle, March 30, 2009 should return to week 1 of April 2009...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-14 : 05:44:57

[code]
DECLARE @date TABLE
(
[date] datetime
)

INSERT INTO @date
SELECT '2009-01-01' UNION ALL
SELECT '2009-01-30' UNION ALL
SELECT '2009-02-19' UNION ALL
SELECT '2009-03-30'

SELECT [date], week_no,
mth_week = CASE WHEN ((week_no - 1) % 13) + 1 <= 5
THEN ((week_no - 1) % 13) + 1
WHEN ((week_no - 1) % 13) + 1 <= 9
THEN ((week_no - 1) % 13) + 1 - 5
ELSE ((week_no - 1) % 13) + 1 - 9
END
FROM
(
SELECT [date],
week_no = (DATEPART(dayofyear, [date]) - 1) / 7 + 1
FROM @date d
) d
ORDER BY [date]
[/code]

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

jeeve01
Starting Member

25 Posts

Posted - 2009-05-17 : 20:57:14

why is it Jan 06 2009 returns to week 1 this should be week 2, and March 30, 2009 returns to week 4, this date should return to week1 of April.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-17 : 21:07:52

quote:
Originally posted by jeeve01

why is it Jan 06 2009 returns to week 1 this should be week 2, and March 30, 2009 returns to week 4, this date should return to week1 of April.

You tell me why not. Until now you have not give us clear definition of your week. All you are saying is Jan is 5 week, Feb is 4 week, Mar is 4 week. Week 1 is starts from which day ? or is it always 1st of the month. I have been asking you for these information since my 1st post in this thread and your answer has not clear and precise.

In order for us to help you you should gives a clear definition of your week and with sufficient example, not just 3 or 4 dates this is week 1, that is week 3.

Example & explanation please.

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

jeeve01
Starting Member

25 Posts

Posted - 2009-05-17 : 21:40:05

544 cycle in one Quarter look like this based on calendar dates

Jan2009
Week1 --- Jan1 - Jan3
Week2 --- Jan4 - Jan10
Week3 ----Jan11 - Jan17
Week4 ---- Jan18 - Jan24
Week5 ---- Jan25 - Jan31
Feb2009
Week1 --- Feb1 - Feb7
Week2 ---- Feb8 - Feb14
Week3 ---- Feb15 - Feb21
Week4 --- Feb22 - feb28
March2009 -- here's my odds
Week1 --- Mar01 - MAr07
Week2 --- MAr08 - MAr14
Week3 --- Mar15 - MAr21
Week4 --- Mar22 - Mar28
Arp2009
Week1 - Mar29 - Apr01 and so on for 5 weeks where in week 5 falls to..

Week5 - Apr27 - May02

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-17 : 22:34:00

[code]
SELECT [date],
[week no]= case WHEN (datepart(week, [date]) - 1) % 13 + 1 <= 5
THEN (datepart(week, [date]) - 1) % 13 + 1
WHEN (datepart(week, [date]) - 1) % 13 + 1 <= 9
THEN (datepart(week, [date]) - 1) % 13 + 1 - 5
ELSE (datepart(week, [date]) - 1) % 13 + 1 - 9
END
FROM
(
select [date] = dateadd(day, NUMBER, '20090101')
from F_TABLE_NUMBER_RANGE(0, 365)
) d
ORDER BY [date][/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources