Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    492

    Unanswered: need help with lookup query

    I'm trying to create a query that does a lookup for a key. The difficulty is that the lookup-table may have duplicate entries for the same key but with different startdates. I need to query for the nearest startdate but no earlier than the date going with the key.

    I think I'm almost there using datediff to find the lookup I need but I just need the final push (I hope)...

    Code:
    use monkey
    go
    set nocount on
    go
    
    create table table1 (
      id integer
    , mykey  varchar(10)
    , mydate datetime
    )
    go
    create table table2 (
       mykey  varchar(10)
    ,  startdate datetime
    ,  alternative varchar(30)
    )
    insert into table1 values (1, 'key1', '2011-01-01')
    insert into table2 values ('key1', '2010-01-01', 'alternative 1')
    insert into table2 values ('key1', '2011-01-01', 'alternative 2')
    -- do not find this one because it's after mydate
    insert into table2 values ('key1', '2011-02-01', 'alternative 3')
    go
    
    -- find for key1 'alternative 2'
        select t1.id
             , t1.mykey
             , t2.alternative
             , min(datediff(dd, startdate, mydate))
          from table1 t1
    inner join table2 t2 on (t1.mykey = t2.mykey)
         where startdate <= mydate
      group by t1.id
             , t1.mykey
             , t2.alternative
    go
    
    go
    drop table table1
    drop table table2
    go

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    anyone who can improve?

    Code:
        select t1.id
             , t1.mykey
             , t2.alternative
          from table1 t1
    inner join table2 t2 on (t1.mykey = t2.mykey)
         where startdate = (select max(startdate)
                              from table2 t22
                           where t1.mykey = t22.mykey
                             and t22.startdate <= t1.mydate)

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    ;with OrderedData as
    (select	Table1.id,
    	Table1.mykey,
    	Table2.alternative,
    	ROW_NUMBER() over (Partition by Table1.id order by Table2.startdate) as RowOrder
    from	Table1
    	inner join table2
    		on Table1.mykey = Table2.mykey
    		and Table1.mydate >= Table2.startdate)
    select	id,
    	mykey,
    	alternative
    from	OrderedData
    where	RowOrder = 1
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •