select p.name,

sum(CASE WHEN nStatus=1 0 else nSpeed end ),sum(nLineTotal)

from statusTable st join product p ON

st.pdtno = p.pdtno

group by p.name,

st.publyear,

st.issord,

st.pdtrnno

This one I think is should work properly:

CREATE FUNCTION [dbo].[FUNCTION_NAME]

(@Pos int, @MyNumb int)

RETURNS  varchar(400)

AS 

BEGIN

 DECLARE @TempResult varchar(400)

 DECLARE @MinValue int

 DECLARE @NewValue int

 SELECT @MinValue=SUM(Value) FROM MyTable WHERE Col<=@Pos

 IF @MyNumb>@MinValue RETURN...

I m done for today .... : )

I ll check the post tommorrow cause this can be done without RECURSIVITY

I don't have them anymore ....

SET NOCOUNT ON

DECLARE @Values TABLE

(

Value   NUMERIC(10,2) 

Col int identity(1,1)

)

/* 20 or more values that are available */

INSERT INTO @Values (Value) VALUES (10)

INSERT INTO...

This is MyTable:

Value       Col        

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

10          1

15          2

21          3

25          4

26          5

30          6

30          7

40          8

41          9

45          10

50          11

55          12

60          13

60          14

65          15

70          16

75          17

80          18

85          19

90          20

95          21

100         22

105         23

this...

 105  95  85  75  65  60  50  41  30  21

This is the result

(with my last version that I posted)

in the original table was value 10 after 15 that has...

For last function the call changes :

SET NOCOUNT ON

DECLARE @myAmount NUMERIC(12,2)  SET @myAmount =160 -- SET THIS VALUE AS VALUE TO BE FOUND

DECLARE @MyCol int

SELECT @MyCol=MAX(Col)+1 FROM MyTable

SELECT dbo.FUNCTION_NAME(@MyCol,@myAmount)

CREATE FUNCTION [dbo].[FUNCTION_NAME]

( @Pos int, @MyNumb int)

RETURNS  varchar(400)

AS 

BEGIN

 DECLARE @TempResult varchar(400)

 DECLARE @MinValue int

 DECLARE @NewValue int

 SELECT @MinValue=SUM(Value) FROM MyTable WHERE Col<=@Pos

 IF @MyNumb>@MinValue RETURN ''

 --check to see if a number matches myValue

 IF...

And the flaw seems to be here : )

 --check to see if a number matches myValue

 IF EXISTS(SELECT Value FROM MyTable WHERE Value=@MyNumb )

  RETURN '  ' + LTRIM(STR(@MyNumb))

is in fact...

Srry for delay I m quite busy ATM

the last code I posted semmed to work fine

I ll check again

883 doesn't match in my code... have to check

CREATE FUNCTION [dbo].[FUNCTION_NAME]

(

 @Pos int,

 @MyNumb int

)

RETURNS  varchar(400)

AS 

BEGIN

 DECLARE @TempResult varchar(400)

 DECLARE @MinValue int

 DECLARE @NewValue int

 SELECT @MinValue=SUM(Value) FROM MyTable WHERE Col<@Pos

 IF @MyNumb>@MinValue RETURN ''

 --check to see if a number matches myValue

 IF EXISTS(SELECT...

Remember that if you want to use SQl you have to be carefull about

cause you won't be safe if...

Replace

 SELECT @NewValue=MAX(Value), @Pos=MAX(Col) FROM MyTable WHERE Value<@MyNumb

with

 SELECT @NewValue=MAX(Value), @Pos=MAX(Col) FROM MyTable WHERE Value<@MyNumb and Col<@Pos

Ups : ))

I m missing here the part where you actually put some data in MyTable srry

Small improvement to func since is TSQL... : )

CREATE FUNCTION [dbo].[FUNCTION_NAME]

(

 @Pos int,

 @MyNumb int

)

RETURNS  varchar(400)

AS 

BEGIN

 DECLARE @TempResult varchar(400)

 DECLARE @MinValue int

 DECLARE @NewValue int

 --check to see if a number matches myValue

 IF EXISTS(SELECT...