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

Transact-SQL (2000)

SQL Query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-02-28 : 08:08:00

Eileen OBroin writes "Hi,
I have a column called Publish_date where I have date values as 20/01/2006. I need to specify that if the day is between 1 and 7 then the word "Week1" will be inserted in another column called Week_Closed, the same as if is from 8 to 14 will be "Week2" and if is 15 to 21 "Week3" etc.

Please!! I need this urgently thanks!!!""

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-28 : 08:24:32

[code]-- Try 1
SELECT CASE
WHEN DATEPART(day, Publish_date) BETWEEN 1 AND 7 THEN 'Week 1'
WHEN DATEPART(day, Publish_date) BETWEEN 8 AND 14 THEN 'Week 2'
WHEN DATEPART(day, Publish_date) BETWEEN 15 AND 21 THEN 'Week 3'
WHEN DATEPART(day, Publish_date) BETWEEN 22 AND 28 THEN 'Week 4'
WHEN DATEPART(day, Publish_date) BETWEEN 29 AND 31 THEN 'Week 5'
END
FROM {YourTableNameHere}

-- Try 2
SELECT 'Week ' + CAST(1 + (DATEPART(day, Publish_date) - 1) / 7 AS VARCHAR)
FROM {YourTableNameHere}[/code]

Peter Larsson
Helsingborg, Sweden

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-02-28 : 08:25:47

You could do something like this, which divides the day part of the date by seven, and uses the modulus operator to check the remainder:

DECLARE @date DATETIME
SET @date = GETDATE()
SELECT 'Week' + CAST(CASE 
                        WHEN DATEPART(day, @date) % 7 = 0 
                        THEN DATEPART(day, @date) / 7 
                        ELSE (DATEPART(day, @date)) / 7 + 1 
                      END AS CHAR(1)
                      )

Mark

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-28 : 08:49:40

[code]-- Try 3
SELECT 'Week ' + CAST(1 + DATEPART(day, Publish_date - 1) / 7 AS VARCHAR)
FROM {YourTableNameHere}[/code]

Peter Larsson
Helsingborg, Sweden

eilob
Starting Member

6 Posts

Posted - 2007-03-13 : 10:24:33

Hi Peso, thanks for that. What about if I need to use actual weeks as in the calendar, for example in March, Week 1 is from 1-4, Week 2 is from 5-11, and so on, which is different to the weeks I specify down below. There is any way I can do this??
Thanks
Eileen

quote:

Originally posted by Peso

-- Try 1
SELECT	CASE
		WHEN DATEPART(day, Publish_date) BETWEEN 1 AND 7 THEN 'Week 1'
		WHEN DATEPART(day, Publish_date) BETWEEN 8 AND 14 THEN 'Week 2'
		WHEN DATEPART(day, Publish_date) BETWEEN 15 AND 21 THEN 'Week 3'
		WHEN DATEPART(day, Publish_date) BETWEEN 22 AND 28 THEN 'Week 4'
		WHEN DATEPART(day, Publish_date) BETWEEN 29 AND 31 THEN 'Week 5'
	END
FROM	{YourTableNameHere}

-- Try 2
SELECT	'Week ' + CAST(1 + (DATEPART(day, Publish_date) - 1) / 7 AS VARCHAR)
FROM	{YourTableNameHere}

Peter Larsson
Helsingborg, Sweden

eilob
Starting Member

6 Posts

Posted - 2007-03-13 : 10:25:12

Hi mwjdavidson, thanks for that. What about if I need to use actual weeks as in the calendar, for example in March, Week 1 is from 1-4, Week 2 is from 5-11, and so on, which is different to the weeks I specify down below. There is any way I can do this??

quote:

Originally posted by mwjdavidson

You could do something like this, which divides the day part of the date by seven, and uses the modulus operator to check the remainder:

DECLARE @date DATETIME
SET @date = GETDATE()
SELECT 'Week' + CAST(CASE 
                        WHEN DATEPART(day, @date) % 7 = 0 
                        THEN DATEPART(day, @date) / 7 
                        ELSE (DATEPART(day, @date)) / 7 + 1 
                      END AS CHAR(1)
                      )

Mark

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-03-13 : 13:35:13

This should do what you want.

The F_TABLE_DATE function is available here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519


declare @start_date	datetime
declare @end_date	datetime

select	@start_date	= '20070101',
	@end_date	= '20071231'

select
	a.[DATE],
	[WEEK_NAME] =
		'WEEK'+convert(varchar(1),
			a.WEEK_STARTING_MON_SEQ_NO-
			b.WEEK_STARTING_MON_SEQ_NO+1 ),
	[WEEK_OF_MONTH] =
		a.WEEK_STARTING_MON_SEQ_NO-
		b.WEEK_STARTING_MON_SEQ_NO+1,
	WEEK_START_DATE =
		case
		when 	a.START_OF_MONTH_DATE >
			a.START_OF_WEEK_STARTING_MON_DATE
		then	a.START_OF_MONTH_DATE
		else	a.START_OF_WEEK_STARTING_MON_DATE
		end,
	WEEK_END_DATE =
		case
		when 	a.END_OF_MONTH_DATE <
			a.END_OF_WEEK_STARTING_MON_DATE
		then	a.END_OF_MONTH_DATE
		else	a.END_OF_WEEK_STARTING_MON_DATE
		end
from
	dbo.F_TABLE_DATE(@start_date, @end_date) a
	join
	(
	select
		bb.MONTH_SEQ_NO,
		WEEK_STARTING_MON_SEQ_NO =
			min(bb.WEEK_STARTING_MON_SEQ_NO)
	from
		dbo.F_TABLE_DATE(@start_date, @end_date) bb
	group by
		bb.MONTH_SEQ_NO
	) b
	on a.MONTH_SEQ_NO= b.MONTH_SEQ_NO
order by
	a.[DATE]

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources