relative weeks - 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)

relative weeks

Author

Topic

billsox
Yak Posting Veteran

74 Posts

Posted - 2002-12-02 : 18:30:23

Hi,

I've got a tricky problem that I hope someone can help me with. If you run the following script, you can see that I have a table with two columns:


CREATE TABLE #MyTest (week_value SMALLDATETIME, relative_week INT)
GO

DECLARE @i SMALLINT

SELECT @i = 0

WHILE @i <= 107
    BEGIN
        INSERT INTO #MyTest (week_value) VALUES (DATEADD(wk, @i, '1/1/99'))
        
        SELECT @i = @i + 1
    END
GO

Pretty simple so far -- weekly dates for 2 years and 3 weeks. What I'm trying to accomplish is updating relative_week in the following way:


week_value   relative_week
==========   =============
1/1/99                  1
1/8/99                  2
1/15/99                 3
1/22/99                 4
1/29/99                 5
...
1/2/00                  1
1/9/00                  2
1/16/00                 3
1/23/00                 4
1/30/00                 5
...
12/31/01                1
1/7/01                  2
1/14/01                 3

As you can see, I need to be able to determine what week of the year it is. But keep in mind that I cannot use the DATEPART function using the 'wk' parameter. That won't work for me for several reasons. First, although the dataset above starts at the first of the year, I work with other datasets that can start anytime during the year (e.g., 104 weeks from 6/26/99 - 6/23/01). And therefore, I would need 6/26/99 to be Week One. Second, the standard DATEPART function can return a week number of 53 or (in rare cases) 54. I have to be able to work in 52-week time periods.

So basically, I need to be able to determine a *relative* week number. And obviously I'd like to avoid any cursor-based approach. Any ideas?

Thanks for helping!

Bill

billsox
Yak Posting Veteran

74 Posts

Posted - 2002-12-02 : 18:33:58

Don't know why my text was cut off but here it is again...

Hi,

I've got a tricky problem that I hope someone can help me with.
If you run the following script, you can see that I have a table
with two columns:


CREATE TABLE #MyTest (week_value SMALLDATETIME, relative_week INT)
GO

DECLARE @i SMALLINT

SELECT @i = 0

WHILE @i <= 107
    BEGIN
        INSERT INTO #MyTest (week_value) VALUES (DATEADD(wk, @i, '1/1/99'))
        
        SELECT @i = @i + 1
    END
GO

Pretty simple so far -- weekly dates for 2 years and 3 weeks.
What I'm trying to accomplish is updating relative_week in the
following way:


week_value   relative_week
==========   =============
1/1/99                  1
1/8/99                  2
1/15/99                 3
1/22/99                 4
1/29/99                 5
...
1/2/00                  1
1/9/00                  2
1/16/00                 3
1/23/00                 4
1/30/00                 5
...
12/31/01                1
1/7/01                  2
1/14/01                 3

As you can see, I need to be able to determine what week of the
year it is. But keep in mind that I cannot use the DATEPART
function using the 'wk' parameter. That won't work for me for
several reasons. First, although the dataset above starts at
the first of the year, I work with other datasets that can start
anytime during the year (e.g., 104 weeks from 6/26/99 - 6/23/01).
And therefore, I would need 6/26/99 to be Week One. Second, the
standard DATEPART function can return a week number of 53 or
(in rare cases) 54. I have to be able to work in 52-week time
periods.

So basically, I need to be able to determine a *relative* week number.
And obviously I'd like to avoid any cursor-based approach. Any ideas?

Thanks for helping!

Bill

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-12-02 : 18:58:07

I think this is what you want

declare @MyTest table (week_value SMALLDATETIME, relative_week INT)
DECLARE @i SMALLINT

SELECT @i = 0

WHILE @i <= 107
BEGIN
INSERT INTO @MyTest (week_value) VALUES (DATEADD(wk, @i, '1/1/99'))

SELECT @i = @i + 1
END

update @MyTest
set relative_week = datediff(wk,b.minweek, a.week_value) + 1
from @MyTest a left join (select datepart(yy,week_value) as myyear, min(week_value) as minweek from @mytest group by datepart(yy,week_value)) b
on datepart(yy,a.week_value) = myyear

select * from @MyTest order by week_value

PS - I did it using a table variable...no cleanup required

Let me know if that's not it...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-12-02 : 19:01:47

bill - my solution doesn't work for

12/31/01 1
1/7/01 2
1/14/01 3

is that data correct? should it be 12/31/00 ?? in this case you'll need another field in your table to say whether the week_value is the starting point for your relative_week calculation. In my solution I just based it on the minimum date of the same year. If you had another field, then you could base it on that instead.

If my solution doesn't solve it for you, I suspect it is because you literally don't have enough information to produce an answer - hence the need for the extra field -

let me know...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-02 : 21:42:35

Thanks Rob,
I had a similar problem a few weeks back I've modified it slightly to fit your problem.

CREATE TABLE #MyTest (week_value SMALLDATETIME, relative_week INT)
DECLARE @i SMALLINT
SELECT @i = 0
WHILE @i <= 107
BEGIN
INSERT INTO #MyTest (week_value) VALUES (DATEADD(wk, @i, '1/1/99'))
SELECT @i = @i + 1
END


DECLARE @col1 nvarchar(4), @seq int

SELECT @seq=0 
UPDATE #MyTest 
SET @seq = relative_week = CASE @col1 WHEN DATEPART(yyyy,week_value) THEN @seq+1 ELSE 1 END, 
@col1 = DATEPART(yyyy,week_value)

SELECT week_value, relative_week
FROM #MyTest

DROP TABLE #MyTest

Edited by - ValterBorges on 12/02/2002 21:45:10

billsox
Yak Posting Veteran

74 Posts

Posted - 2002-12-02 : 22:09:09

rrb -- Yes, my list is incorrect. It should be:


12/31/00   1
1/7/01     2
1/14/01    3

Thanks for pointing that out. I'm uncertain that using the minimum
date of the same year will work in my case. I'll have to play
around with it to see.

Bill

quote:

bill - my solution doesn't work for

12/31/01 1
1/7/01 2
1/14/01 3

is that data correct? should it be 12/31/00 ?? in this case you'll need another field in your table to say whether the week_value is the starting point for your relative_week calculation. In my solution I just based it on the minimum date of the same year. If you had another field, then you could base it on that instead.

If my solution doesn't solve it for you, I suspect it is because you literally don't have enough information to produce an answer - hence the need for the extra field -

let me know...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-12-02 : 22:25:48

sure - let me know

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources