Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
using rate for latest date if no date is available

תשובה using rate for latest date if no date is available

  • Wednesday, July 11, 2012 8:10 PM
     
     
    Sign In to Vote
    0

    Using oracle 9i(sql developer)

    Can someone show me how to use the rate value for most recent date if there is no date in table 2.  currently I have hard coded June 4 (Monday) to use thrusday’s rate as there is no rate of june 4<sup>th</sup>. Weekend was the easy part. But I want to do this without having to manually hard code the holiday dates…can someone help..

    select

    m.effective_date,

    m.value*al.rate

    from table1 m, table2 al

    where 

    M.ID = al.ID

    AND M.EFFECTIVE_DATE BETWEEN '01-jun-12' and '30-jun-12'

    and AL.value_date = m.EFFECTIVE_date -     ( case    

    when m.EFFECTIVE_date = '04-jun-12' then 3

    when to_char(m.EFFECTIVE_date,'DY') = 'SUN' then 2 

    when to_char(m.EFFECTIVE_date,'DY') = 'SAT' then 1 

    ELSE 0 

    end )

    table1

    Effective_date

    Value

    06-01-2012

    100

    06-02-2012

    200

    06-03-2012

    600

    06-04-2012

    600

    All the way to 06-30-2012

    All the way to 06-30-2012

    Table 2 (does not have weekends and some dates depending on <st1:country-region w:st="on"><st1:place w:st="on">UK</st1:place></st1:country-region> holiday)

    Value_date

    Rate

    06-01-2012

    .2

    06-05-2012

    1.5

    06-06-2012

    5.3

    06-07-2012

    1.5

    06-08-2012

    5.6

    06-11-2012

    1.5

    06-12-2012

    1.9

    06-13-2012

    2.1
     

All Replies

  • Wednesday, July 11, 2012 9:26 PM
     
     
    Sign In to Vote
    0
    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats (you got it wrong). Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. Also, this is not an ORACLE forum. 

    Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

     
  • Thursday, July 12, 2012 3:55 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    select T1.Effective_Date, T1.Value, T2.Rate

    from Table1 T1

    OUTER APPLY (select top (1) * from Table2 T2 where T2.Value_Date>=T1.Effective_Date ORDER BY Value_Date) T2

    ---------------------

    The above is the SQL Server syntax, you may need to research similar syntax in Oracle.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog