locked
Get later record, if not exist, then get record before RRS feed

  • Question

  • Hello,

    I'm having a following problem.

    Take data structure:

    Date Rate
    2009.01.01 1
    2009.02.01 2
    2009.03.01 3

    For any given date, I need to find a record immediatelly after it, or, if it doesn't exist, then record before it.

    So, 2009.01.02 should return 2009.02.01 2, 2009.02.10 should return 20093.03.01 3, 2009.03.15 should return 20093.03.01 3.

    Selecting before or after record is easy using top1 ascending or descending. However, I can't combine them using union since union does not allow ordering/top.

    The only solution I found was:

    1. Create functions to return record before date, and record after date
    2. Create function to return union of previous two function output.
    3. Create function to return later record of second function output.

    That seems way too much overhead for a simple task. Is there any better way?

    Thanks!
    Wednesday, November 11, 2009 1:10 PM

Answers

  • Try:

    select top (1) *
    from (
    (select top (1) * from T where [Date] > @Date order by [Date])
    union all
    (select top (1) * from T where [Date] < @Date order by [Date] DESC)
    ) as U
    order by [Date] DESC;
    GO


    AMB
    • Marked as answer by Kalman Toth Monday, November 16, 2009 11:42 PM
    Wednesday, November 11, 2009 1:19 PM
  • Here is one solution:

    CREATE TABLE DateRange (
     dt DATETIME, 
     range_key INT PRIMARY KEY);
     
    INSERT INTO DateRange VALUES('20090101', 1);
    INSERT INTO DateRange VALUES('20090201', 2);
    INSERT INTO DateRange VALUES('20090301', 3);
    
    DECLARE @search_dt DATETIME;
    
    SET @search_dt = '20090210';
    
    SELECT TOP(1) dt, range_key
    FROM DateRange
    ORDER BY CASE WHEN DATEDIFF(DAY, @search_dt, dt) >= 0 THEN 1
                  ELSE 2
             END, ABS(DATEDIFF(DAY, @search_dt, dt));

    Plamen Ratchev
    • Marked as answer by Kalman Toth Monday, November 16, 2009 11:42 PM
    Wednesday, November 11, 2009 1:23 PM

All replies

  • Try:

    select top (1) *
    from (
    (select top (1) * from T where [Date] > @Date order by [Date])
    union all
    (select top (1) * from T where [Date] < @Date order by [Date] DESC)
    ) as U
    order by [Date] DESC;
    GO


    AMB
    • Marked as answer by Kalman Toth Monday, November 16, 2009 11:42 PM
    Wednesday, November 11, 2009 1:19 PM
  • Here is one solution:

    CREATE TABLE DateRange (
     dt DATETIME, 
     range_key INT PRIMARY KEY);
     
    INSERT INTO DateRange VALUES('20090101', 1);
    INSERT INTO DateRange VALUES('20090201', 2);
    INSERT INTO DateRange VALUES('20090301', 3);
    
    DECLARE @search_dt DATETIME;
    
    SET @search_dt = '20090210';
    
    SELECT TOP(1) dt, range_key
    FROM DateRange
    ORDER BY CASE WHEN DATEDIFF(DAY, @search_dt, dt) >= 0 THEN 1
                  ELSE 2
             END, ABS(DATEDIFF(DAY, @search_dt, dt));

    Plamen Ratchev
    • Marked as answer by Kalman Toth Monday, November 16, 2009 11:42 PM
    Wednesday, November 11, 2009 1:23 PM
  • guys, you are amazing. both these solutions work!

    actually, now i'm really ashamed, since the first suggestion was a first thing i've tried, but it kept throwing some strange errors at me about missing parenthesis and such, and the only thing that was lacking was alias for final subquery! oh dear *blushes*

    anyway, which of these would scale better?

    thanks once more!
    Wednesday, November 11, 2009 1:31 PM
  • It is better if you test each query and measure the performance so that you can answer this question yourself; do you need help making this determination?.
    Wednesday, November 11, 2009 1:49 PM
  • I suspect Alejandro's solution will work faster as it can use more efficiently indexes (if one exists on the date column). You have to adjust Alejandro's method to use "date >= @date" in the first subquery to find exact matches.
    Plamen Ratchev
    Wednesday, November 11, 2009 2:38 PM
  • guys, you are amazing. both these solutions work!

    actually, now i'm really ashamed, since the first suggestion was a first thing i've tried, but it kept throwing some strange errors at me about missing parenthesis and such, and the only thing that was lacking was alias for final subquery! oh dear *blushes*

    anyway, which of these would scale better?

    thanks once more!

    Create a covering index like "([Date]) include([Rate])" and test both suggestions in your environment.Check both execution plans.

    How many rows are you expecting to have?


    AMB
    Wednesday, November 11, 2009 3:11 PM
  • I have around 80000 entries in "rate" table. This "Rate" result will be joined around 5 times per each record of fact table, where we'll have up to 5 000 000 records [lookup rate's for different columns in fact table by record date]. Talking of which, I'd need an optimal way of joining that aswell. Perhaps a simple UDF?
    Wednesday, November 11, 2009 7:15 PM