quote:

---

Originally posted by Michael Valentine Jones

Homework question?



CODO ERGO SUM

---

quote:

---

Originally posted by jalejandro0211

office Question

quote:

---

Originally posted by Michael Valentine Jones

Homework question?

CODO ERGO SUM

---

---

quote:

---

Originally posted by Michael Valentine Jones

quote:

---

Originally posted by jalejandro0211

office Question

quote:

---

Originally posted by Michael Valentine Jones

Homework question?

CODO ERGO SUM

---

---

OK, why are you trying to do this? What is the business problem you are trying to solve?

Why do you have a requirement to not use a stored procedure?



CODO ERGO SUM

---

set nocount on
set ansi_warnings off

if object_id('tempdb..#vals') > 0 
	drop table #vals
create table #vals (initPos char(1), dispVal char(1)) --display value can be anything


if object_id('dbo.fnCircleOffset') > 0
	drop function dbo.fnCircleOffset
go
create function dbo.fnCircleOffset(@initPos char(1), @offset int)
returns char(1)
as
begin
-- declare @initPos char(1), @offset int
-- select @initPos = 'a', @offset = 1

	declare @AdjPos char(1)
		,@outer varchar(103)
		,@inner varchar(50)
		,@outeroffset int
		,@inneroffset int
		,@idx int

	select	@outer = '12348cgfed9512348cgfed9512348cgfed95'
		,@inner = '67ba67ba67ba'
		,@outeroffset = @offset%12
		,@inneroffset = @offset%4


	if charindex(@initPos, @outer) > 0
	begin
		set @idx = charindex(@initPos, @outer, 12) + @outeroffset
		set @AdjPos = substring(@outer, @idx, 1)
	end
	else if charindex(@initPos, @inner) > 0
	begin
		set @idx = charindex(@initPos, @inner, 4) + @inneroffset
		set @AdjPos = substring(@inner, @idx, 1)
	end

--select @idx, @AdjPos, @inneroffset

	return @AdjPos
end
go

insert #vals (initPos, dispVal)
select '1','1' union
select '2','2' union
select '3','3' union
select '4','4' union
select '5','5' union
select '6','6' union
select '7','7' union
select '8','8' union
select '9','9' union
select 'a','a' union
select 'b','b' union
select 'c','c' union
select 'd','d' union
select 'e','e' union
select 'f','f' union
select 'g','g'



declare @offset int
set @offset = -2
while @offset < 3
begin
	print 'Offet is: ' + convert(varchar, @offset)
	select	max(c1) c1
		,max(c2) c2
		,max(c3) c3
		,max(c4) c4
	from	(
		select	case when dbo.fnCircleOffset(initPos, @offset) in ('1','5','9','d') then dispVal end as c1
			,case when dbo.fnCircleOffset(initPos, @offset) in ('2','6','a','e') then dispVal end as c2
			,case when dbo.fnCircleOffset(initPos, @offset) in ('3','7','b','f') then dispVal end as c3
			,case when dbo.fnCircleOffset(initPos, @offset) in ('4','8','c','g') then dispVal end as c4
			,case 
				when dbo.fnCircleOffset(initPos, @offset) in ('1','2','3','4') then 1 
			 	when dbo.fnCircleOffset(initPos, @offset) in ('5','6','7','8') then 2 
				when dbo.fnCircleOffset(initPos, @offset) in ('9','a','b','c') then 3 
				when dbo.fnCircleOffset(initPos, @offset) in ('d','e','f','g') then 4 
				end as r
		from	#vals
		) a
	group by r

	set @offset = @offset + 1
end


output:
Offet is: -2
c1   c2   c3   c4   
---- ---- ---- ---- 
3    4    8    c
2    b    a    g
1    7    6    f
5    9    d    e

Offet is: -1
c1   c2   c3   c4   
---- ---- ---- ---- 
2    3    4    8
1    7    b    c
5    6    a    g
9    d    e    f

Offet is: 0
c1   c2   c3   c4   
---- ---- ---- ---- 
1    2    3    4
5    6    7    8
9    a    b    c
d    e    f    g

Offet is: 1
c1   c2   c3   c4   
---- ---- ---- ---- 
5    1    2    3
9    a    6    4
d    b    7    8
e    f    g    c

Offet is: 2
c1   c2   c3   c4   
---- ---- ---- ---- 
9    5    1    2
d    b    a    3
e    7    6    4
f    g    c    8

quote:

---

Originally posted by jalejandro0211

Tks TG 4 ur help, excelent work, I will try...

---

quote:

---

Originally posted by jalejandro0211

TG the values are not constant, they change in every table...

Today can be

1 2 3 4
5 6 7 8
...

Tomorrow...

a b c d
1 3 g p
4 r t 4
...
etc...

---