Closest in Column

What SQL statement do I use to find what is the closest value in a data
set to the value I have.
For example I need to search in a column for the closest value that
exists to what I have:
E.g. my database has
X
1.2
1.3
2.6
1.0
2.5
1.4
1.7
I have 1.5 so I need to a query that return 1.4.
Any help would be appreciated
Thanks
TarryThis may not be very efficient unless you have an index on the column
X, but...
Could you query for 2 numbers, for a given SearchNumber to search for:
A. Largest number smaller than SearchNumber
B. Smallest number larger than SearchNumber
And then select whichever is closer to SearchNumber, A or B.
Basically:
SELECT
CASE
WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
T.Smaller
ELSE T.Larger
END
FROM
(
SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
FROM MyTable T1
FULL OUTER JOIN MyTable T2 ON T1.X = T2.X
WHERE T1.X <= @.SearchNumber AND T2.X >= @.SearchNumber
) T|||Lubdha Khandelwal wrote:
> This may not be very efficient unless you have an index on the column
> X, but...
> Could you query for 2 numbers, for a given SearchNumber to search for:
> A. Largest number smaller than SearchNumber
> B. Smallest number larger than SearchNumber
> And then select whichever is closer to SearchNumber, A or B.
> Basically:
> SELECT
> CASE
> WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
> T.Smaller
> ELSE T.Larger
> END
> FROM
> (
> SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
> FROM MyTable T1
> FULL OUTER JOIN MyTable T2 ON T1.X = T2.X
> WHERE T1.X <= @.SearchNumber AND T2.X >= @.SearchNumber
> ) T
Lubha
I think we should remove <= from the query and join condition also
should be changed.
it should be
SELECT
CASE
WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
T.Smaller
ELSE T.Larger
END
FROM
(
SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
FROM MyTable T1
FULL OUTER JOIN MyTable T2 ON T1.X < T2.X
WHERE T1.X < @.SearchNumber AND T2.X > @.SearchNumber
) T
Or
SELECT @.SearchNumber - T.Smaller,T.larger - @.SearchNumber,
CASE
WHEN (@.SearchNumber - T.Smaller < T.larger - @.SearchNumber ) THEN
T.Smaller
ELSE T.Larger
END
FROM
(
select (select max(x) from mytable where x < @.searchnumber) smaller,
(select min(x) from mytable where x > @.searchnumber) larger
) T
Regards
Amish Shah|||> SELECT
> CASE
> WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
> T.Smaller
> ELSE T.Larger
> END
> FROM
> (
> SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
> FROM MyTable T1
> FULL OUTER JOIN MyTable T2 ON T1.X < T2.X
> WHERE T1.X < @.SearchNumber AND T2.X > @.SearchNumber
> ) T
>
But then you're not checking for equality. What if @.SearchNumber itself
exists in column X, shouldn't it return @.SearchNumber?|||Lubdha Khandelwal wrote:
> > SELECT
> > CASE
> > WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
> > T.Smaller
> > ELSE T.Larger
> > END
> > FROM
> > (
> > SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
> > FROM MyTable T1
> > FULL OUTER JOIN MyTable T2 ON T1.X < T2.X
> > WHERE T1.X < @.SearchNumber AND T2.X > @.SearchNumber
> > ) T
> >
>
> But then you're not checking for equality. What if @.SearchNumber itself
> exists in column X, shouldn't it return @.SearchNumber?
Ok , but for join also you should join it on < not on =
Here is gives null when using = for joins.
create table mytable(x decimal(10,2))
insert into mytable values(1)
insert into mytable values(2)
insert into mytable values(3)
insert into mytable values(4)
insert into mytable values(5)
declare @.searchnumber decimal(10,2)
set @.searchnumber = 2.5
SELECT t.smaller, t.larger,
CASE
WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
T.Smaller
ELSE T.Larger
END
FROM
(
SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
FROM MyTable T1
FULL OUTER JOIN MyTable T2 ON T1.X = T2.X
WHERE T1.X <= @.SearchNumber AND T2.X >= @.SearchNumber
) T
Regards
Amish Shah|||CREATE TABLE #temp (
Value NUMERIC(8, 2)
)
INSERT INTO #temp (Value) VALUES ( 1.2 )
INSERT INTO #temp (Value) VALUES ( 1.3 )
INSERT INTO #temp (Value) VALUES ( 2.6 )
INSERT INTO #temp (Value) VALUES ( 1.0 )
INSERT INTO #temp (Value) VALUES ( 2.5 )
INSERT INTO #temp (Value) VALUES ( 1.4 )
INSERT INTO #temp (Value) VALUES ( 1.7 )
DECLARE @.Target NUMERIC(8, 2)
SELECT @.Target = 1.5
SELECT *
FROM #temp
WHERE CAST(ABS(Value - @.Target) AS NUMERIC(8, 2)) = (SELECT
MIN(CAST(ABS(Value - @.Target) AS NUMERIC(8, 2))) FROM #temp)|||Actually, I came across another way to get the closest in a column...
SELECT TOP 1 *
FROM MyTable
ORDER BY ABS(X - @.SearchNumber) ASC|||Lubdha Khandelwal wrote:
> Actually, I came across another way to get the closest in a column...
> SELECT TOP 1 *
> FROM MyTable
> ORDER BY ABS(X - @.SearchNumber) ASC
Very nice! Painfully simple! Made me slap my forehead twice...

Closest in Column

What SQL statement do I use to find what is the closest value in a data
set to the value I have.
For example I need to search in a column for the closest value that
exists to what I have:
E.g. my database has
X
1.2
1.3
2.6
1.0
2.5
1.4
1.7
I have 1.5 so I need to a query that return 1.4.
Any help would be appreciated
Thanks
TarryThis may not be very efficient unless you have an index on the column
X, but...
Could you query for 2 numbers, for a given SearchNumber to search for:
A. Largest number smaller than SearchNumber
B. Smallest number larger than SearchNumber
And then select whichever is closer to SearchNumber, A or B.
Basically:
SELECT
CASE
WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
T.Smaller
ELSE T.Larger
END
FROM
(
SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
FROM MyTable T1
FULL OUTER JOIN MyTable T2 ON T1.X = T2.X
WHERE T1.X <= @.SearchNumber AND T2.X >= @.SearchNumber
) T|||Lubdha Khandelwal wrote:

> This may not be very efficient unless you have an index on the column
> X, but...
> Could you query for 2 numbers, for a given SearchNumber to search for:
> A. Largest number smaller than SearchNumber
> B. Smallest number larger than SearchNumber
> And then select whichever is closer to SearchNumber, A or B.
> Basically:
> SELECT
> CASE
> WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
> T.Smaller
> ELSE T.Larger
> END
> FROM
> (
> SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
> FROM MyTable T1
> FULL OUTER JOIN MyTable T2 ON T1.X = T2.X
> WHERE T1.X <= @.SearchNumber AND T2.X >= @.SearchNumber
> ) T
Lubha
I think we should remove <= from the query and join condition also
should be changed.
it should be
SELECT
CASE
WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
T.Smaller
ELSE T.Larger
END
FROM
(
SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
FROM MyTable T1
FULL OUTER JOIN MyTable T2 ON T1.X < T2.X
WHERE T1.X < @.SearchNumber AND T2.X > @.SearchNumber
) T
Or
SELECT @.SearchNumber - T.Smaller,T.larger - @.SearchNumber,
CASE
WHEN (@.SearchNumber - T.Smaller < T.larger - @.SearchNumber ) THEN
T.Smaller
ELSE T.Larger
END
FROM
(
select (select max(x) from mytable where x < @.searchnumber) smaller,
(select min(x) from mytable where x > @.searchnumber) larger
) T
Regards
Amish Shah|||
> SELECT
> CASE
> WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
> T.Smaller
> ELSE T.Larger
> END
> FROM
> (
> SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
> FROM MyTable T1
> FULL OUTER JOIN MyTable T2 ON T1.X < T2.X
> WHERE T1.X < @.SearchNumber AND T2.X > @.SearchNumber
> ) T
>
But then you're not checking for equality. What if @.SearchNumber itself
exists in column X, shouldn't it return @.SearchNumber?|||Lubdha Khandelwal wrote:

>
> But then you're not checking for equality. What if @.SearchNumber itself
> exists in column X, shouldn't it return @.SearchNumber?
Ok , but for join also you should join it on < not on =
Here is gives null when using = for joins.
create table mytable(x decimal(10,2))
insert into mytable values(1)
insert into mytable values(2)
insert into mytable values(3)
insert into mytable values(4)
insert into mytable values(5)
declare @.searchnumber decimal(10,2)
set @.searchnumber = 2.5
SELECT t.smaller, t.larger,
CASE
WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
T.Smaller
ELSE T.Larger
END
FROM
(
SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
FROM MyTable T1
FULL OUTER JOIN MyTable T2 ON T1.X = T2.X
WHERE T1.X <= @.SearchNumber AND T2.X >= @.SearchNumber
) T
Regards
Amish Shah|||CREATE TABLE #temp (
Value NUMERIC(8, 2)
)
INSERT INTO #temp (Value) VALUES ( 1.2 )
INSERT INTO #temp (Value) VALUES ( 1.3 )
INSERT INTO #temp (Value) VALUES ( 2.6 )
INSERT INTO #temp (Value) VALUES ( 1.0 )
INSERT INTO #temp (Value) VALUES ( 2.5 )
INSERT INTO #temp (Value) VALUES ( 1.4 )
INSERT INTO #temp (Value) VALUES ( 1.7 )
DECLARE @.Target NUMERIC(8, 2)
SELECT @.Target = 1.5
SELECT *
FROM #temp
WHERE CAST(ABS(Value - @.Target) AS NUMERIC(8, 2)) = (SELECT
MIN(CAST(ABS(Value - @.Target) AS NUMERIC(8, 2))) FROM #temp)|||Actually, I came across another way to get the closest in a column...
SELECT TOP 1 *
FROM MyTable
ORDER BY ABS(X - @.SearchNumber) ASC|||Lubdha Khandelwal wrote:
> Actually, I came across another way to get the closest in a column...
> SELECT TOP 1 *
> FROM MyTable
> ORDER BY ABS(X - @.SearchNumber) ASC
Very nice! Painfully simple! Made me slap my forehead twice...

clearing reportviewer and reload of dynamic report

I have an application that builds the SQL Select statement and creates the report in ReportViewer. The trouble is this, it will not reset the data that is already loading in the report. I have to close out of the ReportViewer screen and come back in to make new selection options. What am I missing?.

ReportViewer.ProcessingMode = ProcessingMode.Local
Dim rView As LocalReport = ReportViewer.LocalReport
rView.ReportPath = "myReport.rdlc"
Dim ds As DataSet = GetDonorData() '<-- This executes my sql query
Dim dsDonor As New ReportDataSource()
dsDonor.Name = "dsDonor_Data"
dsDonor.Value = ds.Tables("dsDonor")
rView.DataSources.Add(dsDonor)

PrivateFunction GetDonorData()
Dim dsDispAsNew DataSet
Dim sqlDonorDataAsString ="SELECT * from Table Where Class =" & selitem
Using connectionAsNew SqlConnection("Data Source=Server;"persist security info=False;Database=Test")
Dim commandAsNew SqlCommand(sqlDonorData, connection)
Dim DonorAdapterAsNew SqlDataAdapter(command)
DonorAdapter.Fill(dsDisp,"dsDonor")
DonorAdapter.Dispose()
command.Dispose()
Return dsDisp
EndFunction

If anyone is interested, I added:
"rView.DataSources.Clear" just before the add statement.

|||

Thanksklschultz013

For anyone interested this is slightly different and in C#

//RV2 is the report

RV2.ProcessingMode = ProcessingMode.Local;
RV2.LocalReport.ReportPath = "Reporting/ReportBookings.rdlc"; //the physical name of the report
DataSet ds = RunStoredProcSelect(spStr, ParsArray, ParCnt);//function to return a dataset
ReportDataSource rptds = new ReportDataSource();
rptds.Name = "DataSet1_bBookingSelect";//Name of the Datasource in the Report [as in the report designer]
rptds.Value = ds.Tables[0];
RV2.LocalReport.DataSources.Clear();
RV2.LocalReport.DataSources.Add(rptds);
RV2.LocalReport.Refresh();