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