May 31, 2006 at 3:02 pm
Hi,
I need help with the following:
Here's the sample of the two tables .
I want to update column BP_AT_time in Table1 by BP_Code from Table2.
When is equal there's no problem.
When I don't have match by a.bp_period< b.period then I don't know how to do it
I want to use row from Table2 next greater then BP_period from table 1
In this scenario processing I would use 901211 to update table 1 because the first greather then
table1
200402 640559 NULL
table2
200411 640559 901211
update table1
set BP_At_Time = b.BP_Code
from table1 a, table2 b
where a.matter_code=b.matter_code
and a.bp_period=b.period
Table1
BP_Period Matter_Code BP_At_Time
------------- -------------------- -----------
200301 640559 NULL
200402 640559 NULL
200503 640559 NULL
200504 640559 NULL
200605 640559 NULL
200606 640559 NULL
Table2
period matter_code BP_Code
------ ---------------- -------
200312 640559 901034
200411 640559 901211
200503 640559 901035
200510 640559 901036
200607 640559 901037
Results needed
Period_yyyymm Matter_Code BP_At_Time
------------- -------------------- -----------
200301 640559 901034
200402 640559 901211
200503 640559 901035
200504 640559 901036
200605 640559 901037
200606 640559 901037
Thanks in advance.
David
May 31, 2006 at 4:27 pm
Try this:
DECLARE @Table1 table(BP_Period int, Matter_Code int, BP_At_Time int NULL)
INSERT @table1 values(200301, 640559, NULL)
INSERT @table1 values(200402, 640559, NULL)
INSERT @table1 values(200503, 640559, NULL)
INSERT @table1 values(200504, 640559, NULL)
INSERT @table1 values(200605, 640559, NULL)
INSERT @table1 values(200606, 640559, NULL)
DECLARE @Table2 table(Period int, Matter_Code int, BP_Code int)
INSERT @table2 values(200312, 640559, 901034)
INSERT @table2 values(200411, 640559, 901211)
INSERT @table2 values(200503, 640559, 901035)
INSERT @table2 values(200510, 640559, 901036)
INSERT @table2 values(200607, 640559, 901037)
UPDATE t1
set t1.BP_At_Time = t3.BP_Code
from @table1 t1
INNER JOIN (select a.matter_code, a.bp_period, min(b.period) period
FROM @table1 a
INNER JOIN @table2 b
ON a.matter_code=b.matter_code AND b.period >= a.bp_period
GROUP BY a.matter_code, a.bp_period
) t2
ON t1.matter_code=t2.matter_code and t1.bp_period=t2.bp_period
INNER JOIN @table2 t3
ON t1.matter_code=t3.matter_code and t2.period=t3.period
SELECT * FROM @table1
May 31, 2006 at 4:56 pm
a
BP_At_Time = b.BP_Code
table1 a
(select a.matter_code, a.bp_period, min(b.period) period
V
a.matter_code = V.matter_code
a.bp_period = V.bp_period
table2 b
a.matter_code=b.matter_code
a.period = b.period
[edit - looks like someone else got there first. My warning above still applies.]
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 1, 2006 at 8:25 am
Jeff, Thanks alot it worked.
June 1, 2006 at 8:29 am
Tim,
Thanks. Worked.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply