Matching Values

Question

Matching Values

Viewing 15 posts - 46 through 60 (of 101 total)

You must be logged in to reply to this topic. Login to reply

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 1

crap it ate my post and I have to run. But I would like to say that it may be possible to do this with SQL. I will check back later.

Mike

Quick logic for client side

create a function that has one input var of type string.

the first call uses str select value from values where value <= target

function

use client side cursor

declare a total var

declare a currentvalue var

declare an arryofvalues

total=0, currentvalue = 0

open ado recordset with input string

movefirst

while not rs.eof or rs.bof

currentvalue = rs.fields(value)

total = total + currentvalue

if total > target

total = total - currentvalue

end if

if total < target

redim aryofvalues

add currentvalue to aryofvalues

end if

if total = target then

read aryofvalues for results

plus currentvalue

end if

get next record

if eof then

move to first rs get currentvalue

check to see if movefirst gives eof if so then out of here

else

call recursive funciton with select value from values where value < currentvaule

end if

wend

noeld SSC Guru Points: 96590 More actions · Answer 2

goving as the function was written recursively on each iteration @MyNumb will have a different value. I think that those steps have to remain where I see the logic flawed is in:

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

because You really need the Pos of the Max(Value) and not two MAX() statements! (those are not necessarily equal )

* Noel

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 3

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

Kindest Regards,

Vasc

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 4

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 :

--check to see if a number matches myValue

IF EXISTS(SELECT Value FROM MyTable WHERE Value=@MyNumb and Col<@Pos)

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

Kindest Regards,

Vasc

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 5

Thank You Vasc 263 is fixed with that.

Any value that needs to compute with max value returns blank.

Regards,
gova

Farrell Keough SSCoach Points: 17414 More actions · Answer 6

So many of us seemed to have been fascinated by this, I thought it might be good to repost the entire code. I have yet to play with this, but it looks extremely interesting ; EXCELLENT JOB, Vasc!!!!

(Please note, I called the funtion FindNumbers)

CREATE TABLE MyTable( Value integer NOT NULL,

Col integer IDENTITY( 1, 1) NOT NULL ) ON [PRIMARY]

INSERT INTO MyTable( Value) VALUES( 15)

INSERT INTO MyTable( Value) VALUES( 10)

INSERT INTO MyTable( Value) VALUES( 21)

INSERT INTO MyTable( Value) VALUES( 25)

INSERT INTO MyTable( Value) VALUES( 26)

INSERT INTO MyTable( Value) VALUES( 30)

INSERT INTO MyTable( Value) VALUES( 40)

INSERT INTO MyTable( Value) VALUES( 41)

INSERT INTO MyTable( Value) VALUES( 45)

INSERT INTO MyTable( Value) VALUES( 50)

INSERT INTO MyTable( Value) VALUES( 55)

INSERT INTO MyTable( Value) VALUES( 60)

INSERT INTO MyTable( Value) VALUES( 65)

INSERT INTO MyTable( Value) VALUES( 70)

INSERT INTO MyTable( Value) VALUES( 75)

INSERT INTO MyTable( Value) VALUES( 80)

INSERT INTO MyTable( Value) VALUES( 85)

INSERT INTO MyTable( Value) VALUES( 90)

INSERT INTO MyTable( Value) VALUES( 95)

INSERT INTO MyTable( Value) VALUES( 100)

INSERT INTO MyTable( Value) VALUES( 105)

CREATE FUNCTION [dbo].[FindNumbers] ( @Pos integer, @MyNumb integer)

RETURNS varchar(400)

AS

BEGIN

DECLARE @TempResult varchar(400)

DECLARE @MinValue integer

DECLARE @NewValue integer

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

IF @MyNumb>@MinValue RETURN ''

-- check to see if a number matches myValue

IF EXISTS( SELECT Value FROM MyTable WHERE Value = @MyNumb AND Col < @Pos)

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

-- if no Value was found then iterate trough table until the Value in table is less than MyValue

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

WHILE( @Pos > 0)

BEGIN

SET @Pos = @Pos - 1

SELECT @NewValue = Value FROM MyTable WHERE Col = @Pos

SET @TempResult = dbo.FindNumbers( @Pos, @MyNumb - @NewValue)

IF @TempResult <> ''

BEGIN

SET @TempResult = ' ' + LTRIM( STR( @NewValue)) + @TempResult

RETURN @TempResult

END

RETURN ''

END

DECLARE @MyAmount NUMERIC(12,2)

DECLARE @MyCol int

SET @MyAmount = 88 -- SET THIS VALUE AS VALUE TO BE FOUND

SELECT @MyCol = MAX( Col) FROM MyTable

SELECT dbo.FindNumbers( @MyCol, @MyAmount)

I wasn't born stupid - I had to study.

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 7

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 Value FROM MyTable WHERE Value=@MyNumb and Col<=@Pos)

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

--if no Value was found then iterate trough table until the Value in table is less than MyValue

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

WHILE (@Pos>1)

BEGIN

SELECT @NewValue=Value FROM MyTable WHERE Col=@Pos

SET @Pos=@Pos-1

SET @TempResult=dbo.Function_Name(@Pos,@MyNumb-@NewValue)

IF @TempResult<>''

BEGIN

SET @TempResult= ' ' + LTRIM(STR(@NewValue)) + @TempResult

RETURN @TempResult

END

RETURN ''

END

Kindest Regards,

Vasc

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

[Farrell's version]

Doesn't work in this case :

DECLARE @MyAmount NUMERIC(12,2)

DECLARE @MyCol int

SET @MyAmount = 627 --Select sum(Value) from MyTable where value % 2 = 1

SELECT @MyCol = MAX( Col) FROM MyTable

SELECT dbo.FindNumbers( @MyCol, @MyAmount)

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 9

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)

Kindest Regards,

Vasc

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Still no luck with

SET @MyAmount = 627 --Select sum(Value) from MyTable where value % 2 = 1

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

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 11

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 to be changed so the values are ordered ASC

Kindest Regards,

Vasc

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

Ninja's_RGR'us

SSC Guru

Points: 294069

June 21, 2005 at 3:03 pm

#567263

Can you repost the whole scrit?

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 13

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 is the function

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 Value FROM MyTable WHERE Value=@MyNumb and Col<=@Pos)

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

--if no Value was found then iterate trough table until the Value in table is less than MyValue

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

WHILE (@Pos>1)

BEGIN

SELECT @NewValue=Value FROM MyTable WHERE Col=@Pos

SET @Pos=@Pos-1

SET @TempResult=dbo.Function_Name(@Pos,@MyNumb-@NewValue)

IF @TempResult<>''

BEGIN

SET @TempResult= ' ' + LTRIM(STR(@NewValue)) + @TempResult

RETURN @TempResult

END

RETURN ''

END

nad this is how I call it

SET NOCOUNT ON

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

DECLARE @MyCol int

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

SELECT dbo.FUNCTION_NAME(@MyCol,@myAmount)

Kindest Regards,

Vasc

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

Sorry but I'm feeling lazy... what about those insert statments?

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 15

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 @Values (Value) VALUES (15)

INSERT INTO @Values (Value) VALUES (21)

INSERT INTO @Values (Value) VALUES (25)

INSERT INTO @Values (Value) VALUES (26)

INSERT INTO @Values (Value) VALUES (30)

INSERT INTO @Values (Value) VALUES (40)

INSERT INTO @Values (Value) VALUES (41)

INSERT INTO @Values (Value) VALUES (45)

INSERT INTO @Values (Value) VALUES (50)

INSERT INTO @Values (Value) VALUES (55)

INSERT INTO @Values (Value) VALUES (60)

INSERT INTO @Values (Value) VALUES (65)

INSERT INTO @Values (Value) VALUES (70)

INSERT INTO @Values (Value) VALUES (75)

INSERT INTO @Values (Value) VALUES (80)

INSERT INTO @Values (Value) VALUES (85)

INSERT INTO @Values (Value) VALUES (90)

INSERT INTO @Values (Value) VALUES (95)

INSERT INTO @Values (Value) VALUES (100)

INSERT INTO @Values (Value) VALUES (105)

INSERT INTO MyTable SELECT * FROM @Values

Kindest Regards,

Vasc