Advertisement

07.16.2008 at 08:25AM PDT, ID: 23570036
[x]
Attachment Details

join based on effective date

Asked by ewang1205 in Oracle Database

Tags: oracle

I have the following two tables.  I like to join based on effective date so I can get effecitve tax_rate for each state every month.  
sate amount month_year
NY       100 JAN-2008
NY       150 FEB-2008
NY       200 MAR-2008
OK       100 JAN-2008
OK      150 FEB-2008
OK       200 MAR-2008
OK       200 APR-2008
OK       200 MAY-2008
TX       100 JAN-2008
TX      150 FEB-2008
TX       200 MAR-2008



STATE  EFF_DATE        EXP_DATE      TAX_RATE
NY        JAN-2008  FEB-2008       10%
NY        MAR-2008                 11%
OK      JAN-2008  FEB-2008      12%
OK      MAR-2008  APR-2008      13%
OK      APR-2008            14%
TX      JAN-2008            15%


Here is what I expect to see.  Is there a simple SQL?  Thanks.
NY 100 JAN-2008  10%
NY 150 FEB-2008  10%
NY 200 MAR-2008  11%
OK 100 JAN-2008  12%
OK 150 FEB-2008  12%
OK 200 MAR-2008  13%
OK 200 APR-2008  13%
OK 200 MAY-2008  14%
TX 100 JAN-2008       15%
TX 150 FEB-2008       15%
TX 200 MAR-2008  15%
Start Free Trial
 
Keywords: join based on effective date
Title
1 Need Row associated with Max(eff_…
2 Legalize marijuana?
3 % and Taxes
4 What is exp and imp??
5 Taxes ??
6 Taxes
 
Loading Advertisement...
 
[+][-]07.16.2008 at 08:34AM PDT, ID: 22016956

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.16.2008 at 08:35AM PDT, ID: 22016959

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.16.2008 at 08:36AM PDT, ID: 22016977

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zone: Oracle Database
Tags: oracle
Sign Up Now!
Solution Provided By: DrSQL
Participating Experts: 2
Solution Grade: A
 
 
[+][-]07.16.2008 at 08:50AM PDT, ID: 22017123

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.16.2008 at 08:53AM PDT, ID: 22017164

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.16.2008 at 09:01AM PDT, ID: 22017268

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.16.2008 at 09:32AM PDT, ID: 22017574

Assisted solutions are selected by the member who asked the question as a comment that contributed to their question's solution.

Start your 7-day free trial to view this Assisted Solution or ask the Experts your question.

 
[+][-]07.16.2008 at 09:41AM PDT, ID: 22017655

Assisted solutions are selected by the member who asked the question as a comment that contributed to their question's solution.

Start your 7-day free trial to view this Assisted Solution or ask the Experts your question.

 
[+][-]07.16.2008 at 09:42AM PDT, ID: 22017665

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.16.2008 at 10:53AM PDT, ID: 22018404

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628