October 29, 2005 at 9:50 pm
hi,
I need help building the SQL for this one...
I have 2 tables, one a lookup tbl, the other tbl holds millions of transations. The problem is the key_id in the transation table was overlayed by our vendor and we're trying to assign the proper key_id based on the datestamp of the transaction record.
The lookup table holds both keys: Good_key and Key_id and the date range for each key.
example:
Lookup tbl
Good_key Key_id Relation_key Date_Range
123 111 111 01/01/2001 - 12/31/2001
456 222 111 01/01/2002 - 06/30/3003
789 333 111 07/01/2003 - 10/31/2005
Trans tbl
Key_id Date Dollars
111 6/29/2003 $50.00 (Key_id should have been: 456)
333 2/01/2001 $25.00 (Key_id should have been: 123)
I tried many attemps at self-joining the Lookup table to itself then the Trans tbl, to no avail.
Any help of direction would be greatly appreciated.
thank you,
October 30, 2005 at 5:22 am
if 06/30/3003 was a typo
and date_range in the lookup table is split in 2 fields
it can be something like this
select trans_key.key_id,trans_date,Moneyfield,keylookup.good_key as corrected_key
from test.dbo.trans_key trans_key /*transaction table*/
inner join test.dbo.keylookup keylookup /*lookup table*/
on trans_key.trans_date between keylookup.date_start and keylookup.date_end
and Relation_key=111 /*relation with table?*/
order by trans_key.key_id
October 30, 2005 at 6:30 am
Question One:
In table Lookup is column Date_Range really one column containing the characters
'01/01/2001 - 12/31/2001' ?
If so, you need to change the lookup table to have 2 columns, Date_Range_Low
and Date_Range_High. Alternatively, create a new table with this SQL:
select Good_key
, CONVERT( datetime, Substring(Date_Range,1,10) , 101 )
, CONVERT( datetime, Substring(Date_Range,14,10) , 101 )
from Lookup
Question Two:
What is the meaning of columns Key_id and Relation_key in table LookUp?
Relation_key appears to have a constant value of 111.
Update Trans WITH ( TABLOCKX )
-- TABLOCKX will lock the entire table and prevent any other activity.
set Key_id =
(select Good_key
from Lookup
where Relation_key = 111
and Date between Date_Range_Low
and Date_Range_High
)
Before running the above statement, drop any indicies that reference key_id and re-create the indicies after the update has run.
SQL = Scarcely Qualifies as a Language
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply