Try this. You can extend it to 31 vars just be adding the extra INSERTs.

CREATE FUNCTION dbo.VarTable (var1 FLOAT, var2 FLOAT, var3 FLOAT, var4

FLOAT)

RETURNS result TABLE (vfrom INTEGER, vto INTEGER, hits INTEGER, value

FLOAT)

AS

BEGIN

DECLARE t TABLE (varno INTEGER, value FLOAT)

INSERT INTO t VALUES (1,var1)

INSERT INTO t VALUES (2,var2)

INSERT INTO t VALUES (3,var3)

INSERT INTO t VALUES (4,var4)

/* ...etc... */

INSERT INTO result

SELECT MIN(varno), MAX(varno), COUNT(*) hits, value

FROM

(SELECT value, varno,

(SELECT MIN(varno)

FROM t AS T2

WHERE T2.varno > T1.varno AND T1.value <> T2.value) AS vto

FROM t AS T1) AS T

GROUP BY value, vto

RETURN

END

--

David Portas

------------

Please reply only to the newsgroup

--

"Vivek Dangwal" <vivdangwalhotmail.com> wrote in message

news:e0I5MJSRDHA.2424tk2msftngp13.phx.gbl...no> In a user-defined function, I have 31 variables each of which stores a

> "float" type value.

> The values stored in all these variables are supposedly random, and havevalues,> defining order.

>

> say, I have

> var1 = 2300.0,

> var2 = 2312.4,

> var3 = 2312.4,

> var4 = 2504.2,

> ....

> and so on upto

> var31 = 2400.0

>

> What I have to accomplish is to get adjacent (or consecutive) equalthe> say if we take the above example then I should get a table similar to the

> following:

>

> FROM TO No.of hits Value

> -----------------------------------------------------------------------

> 1 1 1

> 2300.0

> 2 3 2

> 2312.4

> ..........

> you get the picture......

> Any ideas on how to get this? I am free to use a table variable to storeform.> result in the above form. But I require such a result exactly in this> Thanks....

>

>

## Bookmarks