WEEK STRAT AND WEEK END - 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 2008 Forums

Transact-SQL (2008)

WEEK STRAT AND WEEK END

Author

Topic

kiranmurali
Yak Posting Veteran

55 Posts

Posted - 2010-09-03 : 04:43:50

Hi,

can any one help me how to get the week start,weekend and week of a particular month.

1. the week start should be on monday
and the week end is on sunday
and previous months date should not be displayed in the first and last week of the month

i have to populate the my master table like this

ID MONTH YEAR WEEK WEEKSTART WEEKEND WK
1 1 2010 1 28 3 1
2 1 2010 2 4 10 2
3 1 2010 3 11 17 3
4 1 2010 4 18 24 4
5 1 2010 5 25 31 5
6 2 2010 1 1 7 6
7 2 2010 2 8 14 7
8 2 2010 3 15 21 8
9 2 2010 4 22 28 9
10 3 2010 1 1 7 10
11 3 2010 2 8 14 11
12 3 2010 3 15 21 12
13 3 2010 4 22 28 13
14 4 2010 1 29 4 14
15 4 2010 2 5 11 15
16 4 2010 3 12 18 16
17 4 2010 4 19 25 17
18 4 2010 5 26 2 18
19 5 2010 2 3 9 19
20 5 2010 3 10 16 20
21 5 2010 4 17 23 21
22 5 2010 5 24 30 22
23 6 2010 1 31 6 23
24 6 2010 2 7 13 24
25 6 2010 3 14 20 25
26 6 2010 4 21 27 26
27 7 2010 1 28 4 27
28 7 2010 2 5 11 28
29 7 2010 3 12 18 29
30 7 2010 4 19 25 30
31 7 2010 5 26 1 31
32 8 2010 1 2 8 32
33 8 2010 2 9 15 33
34 8 2010 3 16 22 34
35 8 2010 4 23 29 35
36 9 2010 1 30 5 36
37 9 2010 2 6 12 37
38 9 2010 3 13 19 38
39 9 2010 4 20 26 39
40 10 2010 1 27 3 40
41 10 2010 2 4 10 41
42 10 2010 3 11 17 42
43 10 2010 4 18 24 43
44 10 2010 5 25 31 44
45 11 2010 1 1 7 45
46 11 2010 2 8 14 46
47 11 2010 3 15 21 47
48 11 2010 4 22 28 48
49 12 2010 1 29 5 49
50 12 2010 2 6 12 50
51 12 2010 3 13 19 51
52 12 2010 4 20 26 52
53 12 2010 5 27 2 53

Thanks in advance
kiranmayee

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-03 : 05:11:48

[code]DECLARE @Year SMALLINT = 2010

;WITH cteSource(WK, WeekStart, WeekEnd)
AS (
SELECT Number + 1 AS WK,
DATEADD(DAY, DATEDIFF(DAY, 0, DATEADD(YEAR, @Year - 1900, 0)) / 7 * 7, 7 * Number) AS WeekStart,
DATEADD(DAY, DATEDIFF(DAY, 0, DATEADD(YEAR, @Year - 1900, 0)) / 7 * 7, 7 * Number + 6) AS WeekEnd
FROM master.dbo.spt_values
WHERE type = 'P'
AND number BETWEEN 0 AND 52
), cteCalendar
AS (
SELECT WK AS [ID],
CASE WK
WHEN 1 THEN 1
ELSE DATEPART(MONTH, WeekStart)
END AS [MONTH],
CASE WK
WHEN 1 THEN @Year
ELSE DATEPART(YEAR, WeekStart)
END AS [YEAR],
DATEPART(DAY, WeekStart) AS [WEEKSTART],
DATEPART(DAY, WeekEnd) AS [WEEKEND]
FROM cteSource
)
SELECT [ID],
[MONTH],
[YEAR],
ROW_NUMBER() OVER (PARTITION BY [MONTH] ORDER BY [ID]) AS [WEEK],
[WEEKSTART],
[WEEKEND],
[ID] AS [WK]
FROM cteCalendar[/code]

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

kiranmurali
Yak Posting Veteran

55 Posts

Posted - 2010-09-03 : 06:59:48

Hi,

thanks a lot for your quick response.

i have one more request.
if we take the current month the week is number 1 starts
from 6th 1 to 5 was in previous month week 5.

my concern is that it should display
1st week starting from 1st.
it should end with end date.

Pls give me suggestions to achieve this.

Thanks in advance.
Kiranmayee

kiranmurali
Yak Posting Veteran

55 Posts

Posted - 2010-09-06 : 03:09:35

using the above table i have to display as below

WEEKS
30 to 5 SEP 2010
6 to 12 SEP 2010
13 to 19 SEP 2010
20 to 26 SEP 2010

but the first week should be 1 to 5 not 30 to 5
and the last week should be 27 to 30

can any one help me in solving this.

Thanks in advance
Kiranmayee

kiranmurali
Yak Posting Veteran

55 Posts

Posted - 2010-09-08 : 02:29:00

can any one help me to achieve this,its very urgent.

Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-08 : 03:01:48

Have you tried altering the query yourself?
Perhaps maybe using another CASE statement?

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

kiranmurali
Yak Posting Veteran

55 Posts

Posted - 2010-09-08 : 03:56:24

yes, i tried in different ways.

regards,
Kiranmayee

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-08 : 04:02:17

Can you show us?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-08 : 04:04:12

quote:
Originally posted by kiranmurali

using the above table i have to display as below

WEEKS
30 to 5 SEP 2010
6 to 12 SEP 2010
13 to 19 SEP 2010
20 to 26 SEP 2010

This is NOT as requested originally.
Now you say you want every month to ahve it's weeks start from the 1st and end with the latest day?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-08 : 04:13:27

[code]DECLARE @Year SMALLINT = 2010

;WITH cteDays(theDate)
AS (
SELECT DATEADD(YEAR, @Year - 1900, 0) AS theDate

UNION ALL

SELECT DATEADD(DAY, 1, theDate)
FROM cteDays
WHERE DATEPART(YEAR, DATEADD(DAY, 1, theDate)) = @Year
)
SELECT ROW_NUMBER() OVER (ORDER BY DATEPART(MONTH, theDate), 1 + (DATEPART(DAY, theDate) - 1) / 7) AS ID,
DATEPART(MONTH, theDate) AS [MONTH],
DATEPART(YEAR, theDate) AS [YEAR],
1 + (DATEPART(DAY, theDate) - 1) / 7 AS [WEEK],
MIN(DATEPART(DAY, theDate)) AS [WEEKSTART],
MAX(DATEPART(DAY, theDate)) AS [WEEKEND]
FROM cteDays
GROUP BY DATEPART(YEAR, theDate),
DATEPART(MONTH, theDate),
1 + (DATEPART(DAY, theDate) - 1) / 7
OPTION (MAXRECURSION 366)[/code]

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

kiranmurali
Yak Posting Veteran

55 Posts

Posted - 2010-09-08 : 08:43:21

Thanks a lot its working fine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources