Matching Values
-
Check this :
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 Value FROM MyTable WHERE Value=@MyNumb)
RETURN ' ' + LTRIM(STR(@MyNumb))
--if no Value was found then iterate trough table until the Value in table is less than MyValue
SELECT @NewValue=Value FROM MyTable WHERE Col=@Pos
WHILE (@Pos>0 AND @NewValue>@MyNumb)
BEGIN
SET @Pos=@Pos-1
SELECT @NewValue=Value FROM MyTable WHERE Col=@Pos
END
WHILE (@Pos>0)
BEGIN
SELECT @NewValue=Value FROM MyTable WHERE Col=@Pos
SET @TempResult=dbo.Function_Name(@Pos,@MyNumb-@NewValue)
IF @TempResult<>''
BEGIN
SET @TempResult= ' ' + LTRIM(STR(@NewValue)) + @TempResult
RETURN @TempResult
END
SET @Pos=@Pos-1
END
RETURN ''
END
And the QA
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
) ON [PRIMARY]
GO
INSERT INTO @Values (Value) VALUES (15)
INSERT INTO @Values (Value) VALUES (10)
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 (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 (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)
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)
If you allow more comb than 1 for a number than the code changes a little
Example I have a value of 40 and I can use it 3 times to get my result
Kindest Regards,Vasc
-
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 Value FROM MyTable WHERE Value=@MyNumb)
RETURN ' ' + LTRIM(STR(@MyNumb))
--if no Value was found then iterate trough table until the Value in table is less than MyValue
/* SELECT @NewValue=Value FROM MyTable WHERE Col=@Pos
WHILE (@Pos>0 AND @NewValue>@MyNumb)
BEGIN
SET @Pos=@Pos-1
SELECT @NewValue=Value FROM MyTable WHERE Col=@Pos
END
*/
SELECT @NewValue=MAX(Value), @Pos=MAX(Col) FROM MyTable WHERE Value<@MyNumb
WHILE (@Pos>0)
BEGIN
SELECT @NewValue=Value FROM MyTable WHERE Col=@Pos
SET @TempResult=dbo.Function_Name(@Pos,@MyNumb-@NewValue)
IF @TempResult<>''
BEGIN
SET @TempResult= ' ' + LTRIM(STR(@NewValue)) + @TempResult
RETURN @TempResult
END
SET @Pos=@Pos-1
END
RETURN ''
END
Kindest Regards,Vasc
-
This script doesn't work for me... I'm sure it's nothing but I can't find it.
-
Thanks Vasc. It will take a while for me to get this logic.
But When I tried this
DECLARE @myAmount NUMERIC(12,2) SET @myAmount = 500 -- SET THIS VALUE AS VALUE TO BE FOUND
DECLARE @MyCol int
SELECT @MyCol=MAX(Col) FROM MyTable
SELECT dbo.FUNCTION_NAME(@MyCol,@myAmount)
I get this
105 105 105 105 80 -- 105 we have only once but it repeats to give the answer. Can you please check this.
Regards,
gova -
Ups : ))
I m missing here the part where you actually put some data in MyTable srry
Kindest Regards,Vasc
-
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
Kindest Regards,Vasc
-
Thanks Noel. I would do it by any means either in SQL or Front END through whatever I Can. Still I am totally lost with the logic to do that.
Regards,
gova -
Remember that if you want to use SQl you have to be carefull about
Nested stored procedure levels 32 cause you won't be safe if you have more than 32 rows in your table
Kindest Regards,Vasc
-
Thanks Vsac. It works cool for most cases now. I think it needs little fine tuning as
For 1206 it gives two hundreds(We have one in the table).
ADD : I don't this It can ever reach 32. I will eliminate the rows like so all equel and greater values will be gone as we don't needem in the calculations
DECLARE @myValues TABLE
(
Ctr INT IDENTITY,
Value NUMERIC(10,2))
/* GET ALL THE VALUES LESS THAN THE MAIN AMOUNT */
INSERT INTO @myValues (Value)
SELECT Value FROM @Values WHERE Value < @myAmount
Regards,
gova -
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)
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.Function_Name(@Pos,@MyNumb-@NewValue)
IF @TempResult<>''
BEGIN
SET @TempResult= ' ' + LTRIM(STR(@NewValue)) + @TempResult
RETURN @TempResult
END
END
RETURN ''
END
Kindest Regards,Vasc
-
Could someone post the whole thing (create table, insert date and some test).. I'd like to save that somewhere for safekeeping... and actually understanding what's happening
. -
Good Job!!!
--Check this :
CREATE FUNCTION [dbo].[RecursiveValues]
(
@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)
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.RecursiveValues(@Pos,@MyNumb-@NewValue)
IF @TempResult<>''
BEGIN
SET @TempResult= ' ' + LTRIM(STR(@NewValue)) + @TempResult
RETURN @TempResult
END
END
RETURN ''
END
--And the QA
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) NOT NULL
) ON [PRIMARY]
GO
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 (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)
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 where value <= @myAmount
SELECT dbo.RecursiveValues(@MyCol,@myAmount)
I think if this is rewriten without recursion it will be better for SQL but if you doit on client side Stack space is more abundant
* Noel -
Sorry Vsac I am really embarrassed since still I did not get your logic and just testing your code. So I am bothering you again to fine tune.
For 88 and all consecutive Numbers this works great.
For 263 it gives 21 twice.
For 883 and higher values it doesn't return any value.
Remi I will do that when we are done with this
added - Same with your function Noel
Regards,
gova -
Thanx govinn...
Are you sure there's a match for 883??
-
Yes Remi I got it with
SELECT SUM(value) FROM [MyTable] WHERE COL < 20
Question to Vasc
Can we ignore this tw steps as I make sure The table queried will not have matcihng value or a value greater than @MyNumbSo Any Value will be smaller than @MyNumb--check to see if a number matches myValueIF EXISTS(SELECT Value FROM MyTable WHERE
Value=@MyNumb)RETURN ' ' + LTRIM(STR(@MyNumb))
--if no Value was found then iterate trough table until the Value in table is less than MyValueSELECT @NewValue=MAX(Value), @Pos=MAX(Col) FROM MyTable WHERE Value<@MyNumb and Col<@Pos
Added : Sorry I got the logic it is needed
Regards,
gova
Viewing 15 posts - 31 through 45 (of 101 total)
You must be logged in to reply to this topic. Login to reply