Looping I think? - 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

General SQL Server Forums

New to SQL Server Programming

Looping I think?

Author

Topic

matta0990
Starting Member

44 Posts

Posted - 2012-04-27 : 04:17:05

Hi, i have to tables.

table 1 and table 2

table 1 has a list of dates e.g

01/01/2012
02/01/2012 etc...

and table 2 has numbers
1
2
3
4
5
etc....

i need to join the 2 tables but keep the numbers looping

e.g

01/01/12 1
02/01/12 2
03/01/12 3
04/01/12 1
05/01/12 2
06/01/12 3
07/01/12 1
08/01/12 2
09/01/12 3
10/01/12 1
11/01/12 2
12/01/12 3

any ideas?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-27 : 04:22:17

what happen to 4 and 5 in table 2 ?

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

matta0990
Starting Member

44 Posts

Posted - 2012-04-27 : 04:26:23

yeh sorry, just ignor 4 and 5, should hav ejust been 1, 2 ,3

:-)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-27 : 04:35:05

If it is just a running number that reset every 3, you can generate a running number with row_number().

Do you really need the number from table 2 ?

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

matta0990
Starting Member

44 Posts

Posted - 2012-04-27 : 04:40:04

yes, the numbers in table 2 are diffrent depending diffrent where clauses.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-27 : 04:46:29

[code]
select t1.dates, t2.num
from (
select dates, rn = row_number() over (order by dates)
from table1
) t1
inner join
(
select num,
rn = row_number() over (order by num),
rc = count(*) over()
from table2
) t2 on (t1.rn - 1) % t2.rc + 1 = t2.rn
order by t1.dates
[/code]

another variation
[code]
select t1.dates, t2.num
from (
select dates, rn = (row_number() over (order by dates) - 1) % (select count(*) from table2) + 1
from table1
) t1
inner join
(
select num,
rn = row_number() over (order by num)
from table2
) t2 on t1.rn = t2.rn
order by t1.dates
[/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