Showing posts with label closest. Show all posts
Showing posts with label closest. Show all posts

Wednesday, March 7, 2012

Closest Time in SQL Table

Hi. I have 2 tables which have the following formats:
TABLE A
PLC_TIME TAGNAME STATUS LOCATION
-- -- -- --
TABLE B
PLC_TIME PCVUE_TIME TAGNAME STATUS LOCATION
-- -- --
-- --
Both tables will be inserted with records by a software continuously
with the PLC_TIME of Table B null, ie
PLC_TIME is not inserted with records. For eg,
TABLE_A
PLC_TIME TAGNAME STATUS LOCATION
-- -- -- --
07-05-2007 1:00:32pm TAG.A ON SITEA
07-05-2007 1:40:11pm TAG.A OFF SITEA
07-05-2007 1:10:31pm TAG.A ON SITEA
07-05-2007 2:10:11pm TAG.B NORMAL SITEB
TABLE_B
PLC_TIME PCVUE_TIME TAGNAME STATUS LOCATION
-- -- --
-- --
null 07-05-2007 1:00:39pmTAG.A ONSITEA
null 07-05-2007 1:41:22pmTAG.A OFF SITEA
null 07-05-2007 2:10:12pm TAG.B NORMAL SITEB
I'm required to update PLC_TIME of TABLE B with the PLC_TIME from
table A
which both of them have the same TAGNAME and STATUS and the PLC_TIME
from table A must be
the CLOSEST to the PCVUE_TIME in table b. PLC_TIME is also always
earlier than PCVUE_TIME.
I have used the following SQL command in my VB program to update the
CLOSEST PLC_TIME of TABLE_B:
UPDATE TABLE_B SET PLC_TIME = (SELECT MAX(PLC_TIME) FROM TABLE_A WHERE
TAGNAME = TABLE_B.TAGNAME AND STATUS = TABLE_B.STATUS
AND PLC_TIME<TABLE_B.PCVUE_TIME AND
ABS(DATEDIFF(MINUTE,PLC_TIME,PCVUE_TIME))<31)
(Note: 31 is just an estimation of the maximum difference of PLC_TIME
and PCVUE_TIME in normal case.)
Since both TABLE_A and TABLE_B are updated randomly and the timing may
not be in order, i.e. Some earlier records may be inserted into
TABLE_A and TABLE_B
by the software, my VB program needs to "update TABLE_B set PLC_TIME"
constantly until the CLOSEST PLC_TIME is updated.
What i can think of is DATEDIFF() but DATEDIFF has a limitation that
it only compares the specific MINUTE of
PCVUE_TIME and PLC_TIME if i use DATEDIFF(minute,PLC_TIME, PCVUE_TIME)
and this will have a problem for 1:59pm and 2:00pm in which 2:00pm
will be treated
as earlier than 1:59pm. Also i may need to have multiple DATEDIFF
which compares YEAR, MONTH, DAY, HOUR and so on... which is not
feasible.
What correct SQL command can i use to update TABLE_B until the CLOSEST
PLC_TIME is updated into TABLE_B?
You can try something like this:
UPDATE TABLE_B SET PLC_TIME = A.PLC_TIME
FROM TABLE_B B
INNER JOIN
(SELECT MAX(TABLE_A.PLC_TIME) PLC_TIME, TABLE_A.TAGNAME, TABLE_A.STATUS
FROM TABLE_A
INNER JOIN TABLE_B
ON TABLE_A.TAGNAME = TABLE_B.TAGNAME AND TABLE_A.STATUS = TABLE_B.STATUS
WHERE TABLE_A.PLC_TIME<TABLE_B.PCVUE_TIME
GROUP BY TABLE_A.TAGNAME, TABLE_A.STATUS) A
ON A.TAGNAME = B.TAGNAME AND A.STATUS = B.STATUS
"albertleng" <albertleng@.gmail.com> wrote in message
news:1180449709.930509.45380@.r19g2000prf.googlegro ups.com...
> Hi. I have 2 tables which have the following formats:
> TABLE A
> PLC_TIME TAGNAME STATUS LOCATION
> -- -- -- --
> TABLE B
> PLC_TIME PCVUE_TIME TAGNAME STATUS LOCATION
> -- -- --
> -- --
> Both tables will be inserted with records by a software continuously
> with the PLC_TIME of Table B null, ie
> PLC_TIME is not inserted with records. For eg,
> TABLE_A
> PLC_TIME TAGNAME STATUS LOCATION
> -- -- -- --
> 07-05-2007 1:00:32pm TAG.A ON SITEA
> 07-05-2007 1:40:11pm TAG.A OFF SITEA
> 07-05-2007 1:10:31pm TAG.A ON SITEA
> 07-05-2007 2:10:11pm TAG.B NORMAL SITEB
>
> TABLE_B
> PLC_TIME PCVUE_TIME TAGNAME STATUS LOCATION
> -- -- --
> -- --
> null 07-05-2007 1:00:39pm TAG.A ON SITEA
> null 07-05-2007 1:41:22pm TAG.A OFF SITEA
> null 07-05-2007 2:10:12pm TAG.B NORMAL SITEB
> I'm required to update PLC_TIME of TABLE B with the PLC_TIME from
> table A
> which both of them have the same TAGNAME and STATUS and the PLC_TIME
> from table A must be
> the CLOSEST to the PCVUE_TIME in table b. PLC_TIME is also always
> earlier than PCVUE_TIME.
> I have used the following SQL command in my VB program to update the
> CLOSEST PLC_TIME of TABLE_B:
> UPDATE TABLE_B SET PLC_TIME = (SELECT MAX(PLC_TIME) FROM TABLE_A WHERE
> TAGNAME = TABLE_B.TAGNAME AND STATUS = TABLE_B.STATUS
> AND PLC_TIME<TABLE_B.PCVUE_TIME AND
> ABS(DATEDIFF(MINUTE,PLC_TIME,PCVUE_TIME))<31)
> (Note: 31 is just an estimation of the maximum difference of PLC_TIME
> and PCVUE_TIME in normal case.)
> Since both TABLE_A and TABLE_B are updated randomly and the timing may
> not be in order, i.e. Some earlier records may be inserted into
> TABLE_A and TABLE_B
> by the software, my VB program needs to "update TABLE_B set PLC_TIME"
> constantly until the CLOSEST PLC_TIME is updated.
> What i can think of is DATEDIFF() but DATEDIFF has a limitation that
> it only compares the specific MINUTE of
> PCVUE_TIME and PLC_TIME if i use DATEDIFF(minute,PLC_TIME, PCVUE_TIME)
> and this will have a problem for 1:59pm and 2:00pm in which 2:00pm
> will be treated
> as earlier than 1:59pm. Also i may need to have multiple DATEDIFF
> which compares YEAR, MONTH, DAY, HOUR and so on... which is not
> feasible.
>
> What correct SQL command can i use to update TABLE_B until the CLOSEST
> PLC_TIME is updated into TABLE_B?
>
|||Hi
"Quentin Ran" wrote:

> You can try something like this:
> UPDATE TABLE_B SET PLC_TIME = A.PLC_TIME
> FROM TABLE_B B
> INNER JOIN
> (SELECT MAX(TABLE_A.PLC_TIME) PLC_TIME, TABLE_A.TAGNAME, TABLE_A.STATUS
> FROM TABLE_A
> INNER JOIN TABLE_B
> ON TABLE_A.TAGNAME = TABLE_B.TAGNAME AND TABLE_A.STATUS = TABLE_B.STATUS
> WHERE TABLE_A.PLC_TIME<TABLE_B.PCVUE_TIME
> GROUP BY TABLE_A.TAGNAME, TABLE_A.STATUS) A
> ON A.TAGNAME = B.TAGNAME AND A.STATUS = B.STATUS
>
You should also have
WHERE B.PLC_TIME IS NULL
to both the derived table and update statement to eliminate records already
updated
i.e.
UPDATE B
SET PLC_TIME = A.PLC_TIME
FROM TABLE_B B
JOIN (SELECT MAX(C.PLC_TIME) AS PLC_TIME, C.TAGNAME, C.STATUS
FROM TABLE_A C
JOIN TABLE_B D ON C.TAGNAME = D.TAGNAME AND C.STATUS = D.STATUS
AND C.PLC_TIME< D.PCVUE_TIME AND D.PLC_TIME IS NULL
GROUP BY C.TAGNAME, C.STATUS) A
ON A.TAGNAME = B.TAGNAME AND A.STATUS = B.STATUS
WHERE B.PLC_TIME IS NULL
John

Closest Time in SQL Table

Hi. I have 2 tables which have the following formats:
TABLE A
PLC_TIME TAGNAME STATUS LOCATION
-- -- -- --
TABLE B
PLC_TIME PCVUE_TIME TAGNAME STATUS LOCATION
-- -- --
-- --
Both tables will be inserted with records by a software continuously
with the PLC_TIME of Table B null, ie
PLC_TIME is not inserted with records. For eg,
TABLE_A
PLC_TIME TAGNAME STATUS LOCATION
-- -- -- --
07-05-2007 1:00:32pm TAG.A ON SITEA
07-05-2007 1:40:11pm TAG.A OFF SITEA
07-05-2007 1:10:31pm TAG.A ON SITEA
07-05-2007 2:10:11pm TAG.B NORMAL SITEB
TABLE_B
PLC_TIME PCVUE_TIME TAGNAME STATUS LOCATION
-- -- --
-- --
null 07-05-2007 1:00:39pm TAG.A ON SITEA
null 07-05-2007 1:41:22pm TAG.A OFF SITEA
null 07-05-2007 2:10:12pm TAG.B NORMAL SITEB
I'm required to update PLC_TIME of TABLE B with the PLC_TIME from
table A
which both of them have the same TAGNAME and STATUS and the PLC_TIME
from table A must be
the CLOSEST to the PCVUE_TIME in table b. PLC_TIME is also always
earlier than PCVUE_TIME.
I have used the following SQL command in my VB program to update the
CLOSEST PLC_TIME of TABLE_B:
UPDATE TABLE_B SET PLC_TIME = (SELECT MAX(PLC_TIME) FROM TABLE_A WHERE
TAGNAME = TABLE_B.TAGNAME AND STATUS = TABLE_B.STATUS
AND PLC_TIME<TABLE_B.PCVUE_TIME AND
ABS(DATEDIFF(MINUTE,PLC_TIME,PCVUE_TIME))<31)
(Note: 31 is just an estimation of the maximum difference of PLC_TIME
and PCVUE_TIME in normal case.)
Since both TABLE_A and TABLE_B are updated randomly and the timing may
not be in order, i.e. Some earlier records may be inserted into
TABLE_A and TABLE_B
by the software, my VB program needs to "update TABLE_B set PLC_TIME"
constantly until the CLOSEST PLC_TIME is updated.
What i can think of is DATEDIFF() but DATEDIFF has a limitation that
it only compares the specific MINUTE of
PCVUE_TIME and PLC_TIME if i use DATEDIFF(minute,PLC_TIME, PCVUE_TIME)
and this will have a problem for 1:59pm and 2:00pm in which 2:00pm
will be treated
as earlier than 1:59pm. Also i may need to have multiple DATEDIFF
which compares YEAR, MONTH, DAY, HOUR and so on... which is not
feasible.
What correct SQL command can i use to update TABLE_B until the CLOSEST
PLC_TIME is updated into TABLE_B?You can try something like this:
UPDATE TABLE_B SET PLC_TIME = A.PLC_TIME
FROM TABLE_B B
INNER JOIN
(SELECT MAX(TABLE_A.PLC_TIME) PLC_TIME, TABLE_A.TAGNAME, TABLE_A.STATUS
FROM TABLE_A
INNER JOIN TABLE_B
ON TABLE_A.TAGNAME = TABLE_B.TAGNAME AND TABLE_A.STATUS = TABLE_B.STATUS
WHERE TABLE_A.PLC_TIME<TABLE_B.PCVUE_TIME
GROUP BY TABLE_A.TAGNAME, TABLE_A.STATUS) A
ON A.TAGNAME = B.TAGNAME AND A.STATUS = B.STATUS
"albertleng" <albertleng@.gmail.com> wrote in message
news:1180449709.930509.45380@.r19g2000prf.googlegroups.com...
> Hi. I have 2 tables which have the following formats:
> TABLE A
> PLC_TIME TAGNAME STATUS LOCATION
> -- -- -- --
> TABLE B
> PLC_TIME PCVUE_TIME TAGNAME STATUS LOCATION
> -- -- --
> -- --
> Both tables will be inserted with records by a software continuously
> with the PLC_TIME of Table B null, ie
> PLC_TIME is not inserted with records. For eg,
> TABLE_A
> PLC_TIME TAGNAME STATUS LOCATION
> -- -- -- --
> 07-05-2007 1:00:32pm TAG.A ON SITEA
> 07-05-2007 1:40:11pm TAG.A OFF SITEA
> 07-05-2007 1:10:31pm TAG.A ON SITEA
> 07-05-2007 2:10:11pm TAG.B NORMAL SITEB
>
> TABLE_B
> PLC_TIME PCVUE_TIME TAGNAME STATUS LOCATION
> -- -- --
> -- --
> null 07-05-2007 1:00:39pm TAG.A ON SITEA
> null 07-05-2007 1:41:22pm TAG.A OFF SITEA
> null 07-05-2007 2:10:12pm TAG.B NORMAL SITEB
> I'm required to update PLC_TIME of TABLE B with the PLC_TIME from
> table A
> which both of them have the same TAGNAME and STATUS and the PLC_TIME
> from table A must be
> the CLOSEST to the PCVUE_TIME in table b. PLC_TIME is also always
> earlier than PCVUE_TIME.
> I have used the following SQL command in my VB program to update the
> CLOSEST PLC_TIME of TABLE_B:
> UPDATE TABLE_B SET PLC_TIME = (SELECT MAX(PLC_TIME) FROM TABLE_A WHERE
> TAGNAME = TABLE_B.TAGNAME AND STATUS = TABLE_B.STATUS
> AND PLC_TIME<TABLE_B.PCVUE_TIME AND
> ABS(DATEDIFF(MINUTE,PLC_TIME,PCVUE_TIME))<31)
> (Note: 31 is just an estimation of the maximum difference of PLC_TIME
> and PCVUE_TIME in normal case.)
> Since both TABLE_A and TABLE_B are updated randomly and the timing may
> not be in order, i.e. Some earlier records may be inserted into
> TABLE_A and TABLE_B
> by the software, my VB program needs to "update TABLE_B set PLC_TIME"
> constantly until the CLOSEST PLC_TIME is updated.
> What i can think of is DATEDIFF() but DATEDIFF has a limitation that
> it only compares the specific MINUTE of
> PCVUE_TIME and PLC_TIME if i use DATEDIFF(minute,PLC_TIME, PCVUE_TIME)
> and this will have a problem for 1:59pm and 2:00pm in which 2:00pm
> will be treated
> as earlier than 1:59pm. Also i may need to have multiple DATEDIFF
> which compares YEAR, MONTH, DAY, HOUR and so on... which is not
> feasible.
>
> What correct SQL command can i use to update TABLE_B until the CLOSEST
> PLC_TIME is updated into TABLE_B?
>|||Hi
"Quentin Ran" wrote:
> You can try something like this:
> UPDATE TABLE_B SET PLC_TIME = A.PLC_TIME
> FROM TABLE_B B
> INNER JOIN
> (SELECT MAX(TABLE_A.PLC_TIME) PLC_TIME, TABLE_A.TAGNAME, TABLE_A.STATUS
> FROM TABLE_A
> INNER JOIN TABLE_B
> ON TABLE_A.TAGNAME = TABLE_B.TAGNAME AND TABLE_A.STATUS = TABLE_B.STATUS
> WHERE TABLE_A.PLC_TIME<TABLE_B.PCVUE_TIME
> GROUP BY TABLE_A.TAGNAME, TABLE_A.STATUS) A
> ON A.TAGNAME = B.TAGNAME AND A.STATUS = B.STATUS
>
You should also have
WHERE B.PLC_TIME IS NULL
to both the derived table and update statement to eliminate records already
updated
i.e.
UPDATE B
SET PLC_TIME = A.PLC_TIME
FROM TABLE_B B
JOIN (SELECT MAX(C.PLC_TIME) AS PLC_TIME, C.TAGNAME, C.STATUS
FROM TABLE_A C
JOIN TABLE_B D ON C.TAGNAME = D.TAGNAME AND C.STATUS = D.STATUS
AND C.PLC_TIME< D.PCVUE_TIME AND D.PLC_TIME IS NULL
GROUP BY C.TAGNAME, C.STATUS) A
ON A.TAGNAME = B.TAGNAME AND A.STATUS = B.STATUS
WHERE B.PLC_TIME IS NULL
John

Closest Time in SQL Table

Hi. I have 2 tables which have the following formats:
TABLE A
PLC_TIME TAGNAME STATUS LOCATION
-- -- -- --
TABLE B
PLC_TIME PCVUE_TIME TAGNAME STATUS LOCATION
-- -- --
-- --
Both tables will be inserted with records by a software continuously
with the PLC_TIME of Table B null, ie
PLC_TIME is not inserted with records. For eg,
TABLE_A
PLC_TIME TAGNAME STATUS LOCATION
-- -- -- --
07-05-2007 1:00:32pm TAG.A ON SITEA
07-05-2007 1:40:11pm TAG.A OFF SITEA
07-05-2007 1:10:31pm TAG.A ON SITEA
07-05-2007 2:10:11pm TAG.B NORMAL SITEB
TABLE_B
PLC_TIME PCVUE_TIME TAGNAME STATUS LOCATION
-- -- --
-- --
null 07-05-2007 1:00:39pm TAG.A ON SITEA
null 07-05-2007 1:41:22pm TAG.A OFF SITEA
null 07-05-2007 2:10:12pm TAG.B NORMAL SITEB
I'm required to update PLC_TIME of TABLE B with the PLC_TIME from
table A
which both of them have the same TAGNAME and STATUS and the PLC_TIME
from table A must be
the CLOSEST to the PCVUE_TIME in table b. PLC_TIME is also always
earlier than PCVUE_TIME.
I have used the following SQL command in my VB program to update the
CLOSEST PLC_TIME of TABLE_B:
UPDATE TABLE_B SET PLC_TIME = (SELECT MAX(PLC_TIME) FROM TABLE_A WHERE
TAGNAME = TABLE_B.TAGNAME AND STATUS = TABLE_B.STATUS
AND PLC_TIME<TABLE_B.PCVUE_TIME AND
ABS(DATEDIFF(MINUTE,PLC_TIME,PCVUE_TIME)
)<31)
(Note: 31 is just an estimation of the maximum difference of PLC_TIME
and PCVUE_TIME in normal case.)
Since both TABLE_A and TABLE_B are updated randomly and the timing may
not be in order, i.e. Some earlier records may be inserted into
TABLE_A and TABLE_B
by the software, my VB program needs to "update TABLE_B set PLC_TIME"
constantly until the CLOSEST PLC_TIME is updated.
What i can think of is DATEDIFF() but DATEDIFF has a limitation that
it only compares the specific MINUTE of
PCVUE_TIME and PLC_TIME if i use DATEDIFF(minute,PLC_TIME, PCVUE_TIME)
and this will have a problem for 1:59pm and 2:00pm in which 2:00pm
will be treated
as earlier than 1:59pm. Also i may need to have multiple DATEDIFF
which compares YEAR, MONTH, DAY, HOUR and so on... which is not
feasible.
What correct SQL command can i use to update TABLE_B until the CLOSEST
PLC_TIME is updated into TABLE_B?You can try something like this:
UPDATE TABLE_B SET PLC_TIME = A.PLC_TIME
FROM TABLE_B B
INNER JOIN
(SELECT MAX(TABLE_A.PLC_TIME) PLC_TIME, TABLE_A.TAGNAME, TABLE_A.STATUS
FROM TABLE_A
INNER JOIN TABLE_B
ON TABLE_A.TAGNAME = TABLE_B.TAGNAME AND TABLE_A.STATUS = TABLE_B.STATUS
WHERE TABLE_A.PLC_TIME<TABLE_B.PCVUE_TIME
GROUP BY TABLE_A.TAGNAME, TABLE_A.STATUS) A
ON A.TAGNAME = B.TAGNAME AND A.STATUS = B.STATUS
"albertleng" <albertleng@.gmail.com> wrote in message
news:1180449709.930509.45380@.r19g2000prf.googlegroups.com...
> Hi. I have 2 tables which have the following formats:
> TABLE A
> PLC_TIME TAGNAME STATUS LOCATION
> -- -- -- --
> TABLE B
> PLC_TIME PCVUE_TIME TAGNAME STATUS LOCATION
> -- -- --
> -- --
> Both tables will be inserted with records by a software continuously
> with the PLC_TIME of Table B null, ie
> PLC_TIME is not inserted with records. For eg,
> TABLE_A
> PLC_TIME TAGNAME STATUS LOCATION
> -- -- -- --
> 07-05-2007 1:00:32pm TAG.A ON SITEA
> 07-05-2007 1:40:11pm TAG.A OFF SITEA
> 07-05-2007 1:10:31pm TAG.A ON SITEA
> 07-05-2007 2:10:11pm TAG.B NORMAL SITEB
>
> TABLE_B
> PLC_TIME PCVUE_TIME TAGNAME STATUS LOCATION
> -- -- --
> -- --
> null 07-05-2007 1:00:39pm TAG.A ON SITEA
> null 07-05-2007 1:41:22pm TAG.A OFF SITEA
> null 07-05-2007 2:10:12pm TAG.B NORMAL SITEB
> I'm required to update PLC_TIME of TABLE B with the PLC_TIME from
> table A
> which both of them have the same TAGNAME and STATUS and the PLC_TIME
> from table A must be
> the CLOSEST to the PCVUE_TIME in table b. PLC_TIME is also always
> earlier than PCVUE_TIME.
> I have used the following SQL command in my VB program to update the
> CLOSEST PLC_TIME of TABLE_B:
> UPDATE TABLE_B SET PLC_TIME = (SELECT MAX(PLC_TIME) FROM TABLE_A WHERE
> TAGNAME = TABLE_B.TAGNAME AND STATUS = TABLE_B.STATUS
> AND PLC_TIME<TABLE_B.PCVUE_TIME AND
> ABS(DATEDIFF(MINUTE,PLC_TIME,PCVUE_TIME)
)<31)
> (Note: 31 is just an estimation of the maximum difference of PLC_TIME
> and PCVUE_TIME in normal case.)
> Since both TABLE_A and TABLE_B are updated randomly and the timing may
> not be in order, i.e. Some earlier records may be inserted into
> TABLE_A and TABLE_B
> by the software, my VB program needs to "update TABLE_B set PLC_TIME"
> constantly until the CLOSEST PLC_TIME is updated.
> What i can think of is DATEDIFF() but DATEDIFF has a limitation that
> it only compares the specific MINUTE of
> PCVUE_TIME and PLC_TIME if i use DATEDIFF(minute,PLC_TIME, PCVUE_TIME)
> and this will have a problem for 1:59pm and 2:00pm in which 2:00pm
> will be treated
> as earlier than 1:59pm. Also i may need to have multiple DATEDIFF
> which compares YEAR, MONTH, DAY, HOUR and so on... which is not
> feasible.
>
> What correct SQL command can i use to update TABLE_B until the CLOSEST
> PLC_TIME is updated into TABLE_B?
>|||Hi
"Quentin Ran" wrote:

> You can try something like this:
> UPDATE TABLE_B SET PLC_TIME = A.PLC_TIME
> FROM TABLE_B B
> INNER JOIN
> (SELECT MAX(TABLE_A.PLC_TIME) PLC_TIME, TABLE_A.TAGNAME, TABLE_A.STATUS
> FROM TABLE_A
> INNER JOIN TABLE_B
> ON TABLE_A.TAGNAME = TABLE_B.TAGNAME AND TABLE_A.STATUS = TABLE_B.STATUS
> WHERE TABLE_A.PLC_TIME<TABLE_B.PCVUE_TIME
> GROUP BY TABLE_A.TAGNAME, TABLE_A.STATUS) A
> ON A.TAGNAME = B.TAGNAME AND A.STATUS = B.STATUS
>
You should also have
WHERE B.PLC_TIME IS NULL
to both the derived table and update statement to eliminate records already
updated
i.e.
UPDATE B
SET PLC_TIME = A.PLC_TIME
FROM TABLE_B B
JOIN (SELECT MAX(C.PLC_TIME) AS PLC_TIME, C.TAGNAME, C.STATUS
FROM TABLE_A C
JOIN TABLE_B D ON C.TAGNAME = D.TAGNAME AND C.STATUS = D.STATUS
AND C.PLC_TIME< D.PCVUE_TIME AND D.PLC_TIME IS NULL
GROUP BY C.TAGNAME, C.STATUS) A
ON A.TAGNAME = B.TAGNAME AND A.STATUS = B.STATUS
WHERE B.PLC_TIME IS NULL
John

Closest match SQL query

I have a need to execute a query in T-SQL on a numeric field in a SQL table.

However if there is no exact match I'd likea query that will return the row that is just below my value.

As an example if the table has values: 1,2,4,5,7,9, 15 and 20 for instance and I am matching with a varibale containing the value 9 then I'd like it to return that row. however if my variable has the value 19 I want the row containign 15 returned.

Is this possible? Can anyone help me with such a query?

Regards

Clive

If you use <= in your WHERE clause, and an Order By ... DESC, you will get the results you want. When you use ExecuteScalar(), it will only return the first value in the resultset.|||

Here is one (SQL Server 2005):

SELECT NumFROM(SELECT Num, Row_Number()over(orderby NumDESC)as rNum

FROM yourTable

WHERE num<=19) t

WHERE rNum=1

Another one (should work with 2000):

SELECTTop 1 NumFROM(SELECT Num,(SELECTCOUNT(*)FROM yourTable bWHERE a.Num<=b.Num)as rNum

FROM yourTable a

WHERE Num<=19) t

OrderBY rNum

|||

SELECT TOP 1 *

FROM MyTable

WHERE Num<=19

ORDER BY Num DESC

|||

Thanks for your help. However I'm not doign too well, i've not managed to get these to work. I shoudl clarify that I am useing SQL 2005.

The cloest seems to be:

SELECT TOP 1 *

FROM table

WHERE id <=15

That does indeed return one row however since I can't guarantee the order of the data in the table I need to sort it first I think.

I have tried :

SELECT top 1 *

from (SELECT * from table ORDER BY id)

WHERE ID <=15 but also this fails.

Any help appreciated.

Regards

Clive

|||

Use Motley's code. You will see that it includes an order by clause. You need to Order By id DESC.

SELECT TOP 1 *

FROM table

WHERE id <=15

ORDER BY id DESC

|||

Thanks Mike,

That works perfectly - I was being dumb and putting the ORDER BY clause in the wrong place.

Clive

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...