Sequence of 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 2005 Forums

Transact-SQL (2005)

Sequence of weeks

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2008-07-22 : 15:26:37

[code]
create table #test1 (id int, offsetdays int)
insert into #test1
select 1, 0 union all
select 2, 14 union all
select 3, 28 union all
select 4, 42
[/code]

I want to convert the offsetdays to week, something like below.

id week
1, 1
1, 2
2, 3 -- The offset is 14 days, which means id 1 will have week 1,2
2, 4
3, 5
3, 6
....
....

Any help is appreciated.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-22 : 15:32:36

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-22 : 15:50:05

I'll take a stab at the question...(and try to answer my guess)


select ca.id
       ,n.number [week]
from   (select max(offsetdays)/7 [week] from #test1) wk
join   master..spt_Values n
              on n.type = 'p'
              and n.number <= wk.[week]
              and n.number > 0
cross apply (select max(id) id from #test1 where offsetdays/7 < n.number) ca

OUTPUT:
id          week
----------- -----------
1           1
1           2
2           3
2           4
3           5
3           6

Be One with the Optimizer
TG

karuna
Aged Yak Warrior

582 Posts

Posted - 2008-07-22 : 15:56:53

quote:
Originally posted by jsmith8858

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

Sorry about that. After I posted I realized, the question sounds very vague.

I will try to explain it to my best.

Basically, I want to break up the offset days, and display it as weeks.
I need the weeks to be in sequence with related id.

I have a sequence of ids, mapped against a column (offsetdays).
The offsetdays will be like 0,14,28,42 or 0,7,28,42 and so on.
The offset number says how many weeks the previous id should be listed in the result.


create table #test1 (id int, offsetdays int)
insert into #test1 
select 1, 0 union all
select 2, 14 union all
select 3, 28 union all
select 4, 42

In the above case, the offsetdays of 14 (which is mapped against 2) dictates that id 1 has to be listed for 2 weeks,
and in case of 3 the offset is 28 days,
this can be again calculated from id 1 or
from offset of 3 - offset of 2 = 28-14 = 14 so id 2 will be listed for 2 weeks again
If the offset of 3 had been specified as 21, then id 2 will be listed only for 1 week, since 14-21 = 7

I am still not clear, if this is clear enough, I hope it gives an idea of what I'm trying to do.

So far, I have


select id, (days/7)+1 as week from #test1
id    week
1	1
2	3
3	5
4	7

Thanks
Karunakaran

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources