May 14, 2007 at 1:24 am
Hello to you all,
I've got 2 tables :
Table A has a lot of proces data, each time a product is created, a line of proces data is written into the database.
To make it easy : Each record in Table A has the columns : A.DateTime, A.Order, A.Post, A.Lot, A.Temp1, ...
Table B has a lot of administrative data. This data is the feedback of what was produced at a given time.
To make it easy : Each record in Table B has the columns : B.StartDate, B.EndDate, B.Order, B.Post, B.Lot, B.Stat, ...
To make it clear what I'm looking for, I'll give an example of entered data.
Table A
DateTime Order Post Lot Temp1
01/05/07 8h40 495
01/05/07 8h46 496
01/05/07 9h30 445
Table B
StartDate EndDate Order Post Lot Stat
01/05/07 8h35 01/05/07 9h10 94125 05 2361 00
01/05/07 9h20 01/05/07 10h02 96154 02 3154 01
What I need to do is to make a query which can lookup the Order, Post and Lot in table B, based on start and enddate, and then using this combination, update table A with the correct Order, Post and Lot nr.
This result would look like :
Table A
DateTime Order Post Lot Temp1
01/05/07 8h40 94125 05 2361 495
01/05/07 8h46 94125 05 2361 496
01/05/07 9h30 96154 02 3154 445
Any ideas what's the best solution to do this ?
May 14, 2007 at 2:22 am
Try
DECLARE @a TABLE ([DateTime] datetime, [Order] int, Post int, Lot int, Temp1 int)
DECLARE @b-2 TABLE (StartDate datetime, EndDate datetime, [Order] int, Post int, Lot int, Stat int)
INSERT INTO @a SELECT '2007/05/07 08:40:00', NULL, NULL, NULL, 495
UNION ALL SELECT '2007/05/07 08:46:00', NULL, NULL, NULL, 496
UNION ALL SELECT '2007/05/07 09:30:00', NULL, NULL, NULL, 445
INSERT INTO @b-2 SELECT '2007/05/07 08:35:00', '2007/05/07 09:10:00', 94125, 05, 2361, 00
UNION ALL SELECT '2007/05/07 09:20:00', '2007/05/07 10:02:00', 96154, 02, 3154, 01
SELECT A.[DateTime], B.[Order], B.Post, B.Lot, A.Temp1
FROM @a A
INNER JOIN @b-2 B ON A.[DateTime] BETWEEN B.StartDate AND B.EndDate
K. Matsumura
May 14, 2007 at 2:44 am
Are we assuming that the date ranges in table B are non overlapping? if that is the case then soln above suffices, otherwise you will need another condition.
Update
TableA
set
TableA.[Order] = B.[Order], TableA.Post = B.Post, TableA.Lot = B.Lot
from
TableA A,TableB B
where
A.[DateTime] between B.StartDate AND B.EndDate
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply