December 11, 2010 at 3:27 pm
I have 3 tables where Tables A and B with column Timelogged and Table C has same time Timelogged as A and extra column as Value 0,i have requirement to check the table B and if table B has record of timelogged
with in 1 minute as time logged A then i need to update table C from value 0 to 1 of that timelogged value as B
ex:
Table A (Columnn:Timelogged ,Values: 2010-12-11 12:35:00.000)
Table B (Columnn:Timelogged ,Values: 2010-12-11 12:35:06.623)
Table C (Columnn:Timelogged ,Values: 2010-12-11 12:35:06.623 and Column:Value ,Values 0)
Table A Table B Table C
TimeLogged Timelogged TimeloggedValue
2010-12-11 12:35:00.000 2010-12-11 12:35:06.623 2010-12-11 12:35:00.000 0
can some body point me to achieve the above in efficient way?
Thanks!!
December 13, 2010 at 12:36 pm
Something like this maybe.
I've had to make a few assumptions, such as what exactly you mean by "within 1 minute", and I've also assumed that tables TableA and TableC do not store times in the Timelogged columns to a higher precision than 1 minute. Also, are the values of the Timelogged columns in the TableA and TableC tables unique?
--UPDATE C SET Value = 1 /* Uncomment UPDATE when tested */
SELECT C.Timelogged, C.Value /* Comment out SELECT when tested */
FROM TableC AS C
JOIN TableA AS A ON (C.Timelogged = A.Timelogged)
WHERE EXISTS (
SELECT 1 FROM TableB AS B
WHERE (B.Timelogged >= A.Timelogged)
AND (B.Timelogged < DATEADD(minute, 1, A.Timelogged))
)
AND (C.Value = 0)
The update will be faster if there are indexes on the three Timelogged columns.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply