Matching Values
-
I m done for today .... : )
I ll check the post tommorrow cause this can be done without RECURSIVITY
Kindest Regards,Vasc
-
Day's over here... I'll try to check that first thing tomorrow morning.
-
>>I ll check the post tommorrow cause this can be done without RECURSIVITY<<
That, is a smart move
* Noel -
Thank You all. Especially Vasc. I had to leave early for Dr's Appt.
Vasc with this change
SELECT @MinValue=SUM(Value) FROM MyTable WHERE Col<=@Pos
SELECT @MinValue=SUM(Value) FROM MyTable WHERE Col<@Pos It works for all cases except when it needs 105 (The max value) for computing. Let s see tomorrow.
Regards,
gova -
Thanks Farrell I have been trying to follow this thread and keep having to work. Having it all in one place is a blessing.
It would appear that Vasc has turned out a masterpiece.
Mike
-
Thanks Michael. Let me wait for Vasc's solution fine tuning before making this to C#.
Regards,
gova -
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 ''
--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 @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
END
RETURN ''
END
PS Seems that the sol for 883 is
105 100 95 90 85 80 75 70 65 41 30 26 21 : )
Kindest Regards,Vasc
-
I checked several combinations Vasc. It works great. I have been thinking I am intelligent. Now I would say I am nowhere close to intelligent. I couldn't have done this solution in one year.
Vasc thank you very much.
Thanks for everyone else for the help, interest and enthusiasm.
Regards,
gova -
I still can't make this thing work.. what's wrong with it?
if object_id ('FUNCTION_NAME') > 0
drop function FUNCTION_NAME
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MyTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MyTable]
GO
CREATE TABLE [dbo].[MyTable] (
[Value] [int] NOT NULL ,
[Col] [int] identity(1,1)
) ON [PRIMARY]
GO
INSERT INTO dbo.MyTable (Value) VALUES (10)
INSERT INTO dbo.MyTable (Value) VALUES (15)
INSERT INTO dbo.MyTable (Value) VALUES (21)
INSERT INTO dbo.MyTable (Value) VALUES (25)
INSERT INTO dbo.MyTable (Value) VALUES (26)
INSERT INTO dbo.MyTable (Value) VALUES (30)
INSERT INTO dbo.MyTable (Value) VALUES (30)
INSERT INTO dbo.MyTable (Value) VALUES (40)
INSERT INTO dbo.MyTable (Value) VALUES (41)
INSERT INTO dbo.MyTable (Value) VALUES (45)
INSERT INTO dbo.MyTable (Value) VALUES (50)
INSERT INTO dbo.MyTable (Value) VALUES (55)
INSERT INTO dbo.MyTable (Value) VALUES (60)
INSERT INTO dbo.MyTable (Value) VALUES (60)
INSERT INTO dbo.MyTable (Value) VALUES (65)
INSERT INTO dbo.MyTable (Value) VALUES (70)
INSERT INTO dbo.MyTable (Value) VALUES (75)
INSERT INTO dbo.MyTable (Value) VALUES (80)
INSERT INTO dbo.MyTable (Value) VALUES (85)
INSERT INTO dbo.MyTable (Value) VALUES (90)
INSERT INTO dbo.MyTable (Value) VALUES (95)
INSERT INTO dbo.MyTable (Value) VALUES (100)
INSERT INTO dbo.MyTable (Value) VALUES (105)
GO
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@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 @Pos=MAX(Col) FROM MyTable WHERE Value<=@MyNumb and Col1)
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
END
RETURN ''
END
GO
drop function FUNCTION_NAME
drop table MyTable
GO
-
Don't forget to post the final script with test cases.
Looks like none of us could have done this in a year (except Noeld maybe).
-
/* Final draft of the entire solution */
/* Solution to find a set of values that matches one value. Find first possible Match*/
/* Will work for 32 rows to be checked due to limitation of SQL Nesting level 32 */
/* Copy Right ??? Vasc. */
SET NOCOUNT ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MyTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MyTable]
GO
CREATE TABLE [dbo].[MyTable] (
[Value] [int] NOT NULL ,
[Col] [int] NOT NULL IDENTITY
) ON [PRIMARY]
GO
INSERT INTO [MyTable] (Value) VALUES (10)
INSERT INTO [MyTable] (Value) VALUES (15)
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 (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 (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)
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FUNCTION_NAME]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[FUNCTION_NAME]
GO
/*** Elegant Recursive function developed by Vasc. ***/
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 @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
END
RETURN ''
END
GO
/* Using the Function*/
DECLARE @myAmount NUMERIC(12,2) SET @myAmount = 1263 -- SET THIS VALUE AS VALUE TO BE FOUND
DECLARE @MyCol int
SELECT @MyCol=MAX(Col) FROM MyTable
SELECT dbo.FUNCTION_NAME(@MyCol,@myAmount)
GO
DECLARE @myAmount NUMERIC(12,2) SET @myAmount = 88 -- SET THIS VALUE AS VALUE TO BE FOUND
DECLARE @MyCol int
SELECT @MyCol=MAX(Col) FROM MyTable
SELECT dbo.FUNCTION_NAME(@MyCol,@myAmount)
GO
DECLARE @myAmount NUMERIC(12,2) SET @myAmount = 200 -- SET THIS VALUE AS VALUE TO BE FOUND
DECLARE @MyCol int
SELECT @MyCol=MAX(Col) FROM MyTable
SELECT dbo.FUNCTION_NAME(@MyCol,@myAmount)
GO
drop function FUNCTION_NAME
drop table MyTable
GO
/*
I checked several combinations Vasc. It works great. I have been thinking I am intelligent. Now I would say I am nowhere close to intelligent. I couldn't have done this solution in one year.
Vasc thank you very much.
Thanks for everyone else for the help, interest and enthusiasm.
*/
Regards,
gova -
Thanx
. -
Not sure If would have been able neither I felt very skeptical of the brute force method but Vasc proof me wrong!
I would have tried any math search algorithm first but that takes a while 
Good Job!
* Noel -
Even after the solution I did not get the logic. I had to re-create this function in C# and debug step by step to get to know how it works.
If any one interested in C# code I can post that. I don't know whether it would be possible to load the project files here.
Regards,
gova -
govinn I would like to see the C# code if possible thanks.
Mike
Viewing 15 posts - 61 through 75 (of 101 total)
You must be logged in to reply to this topic. Login to reply