Solved

Oracle - Max effective dates and Max invalid date

Posted on 2014-02-15
6
519 Views
Last Modified: 2014-02-15
Hi

I've got this Query where i would like to get the result of the RD.EFFECTIVE_DATE and the Max RP.DATE_INVALID.

even if i put Max in front of TO_CHAR (RD.EFFECTIVE_DATE, 'yyyymmdd') AS EFFECTIVE_DATE,, it just don't work.

How can i do that?

Thanks for your help.


SELECT   'H',
         'M',
         R.ID,
         TO_CHAR (RD.EFFECTIVE_DATE, 'yyyymmdd') AS EFFECTIVE_DATE,
         TO_CHAR (RP.DATE_INVALID, 'yyyymmdd') AS INVALID_DATE,
         R.BREAK_TYPE,
         RATE_UNIT,
         CURRENCY_CODE,
         R.RATE_MEASURE,
         INCREMENTAL_FLAG,
         REF_NAME,
         'D',
         'M',
         RD.RADIAL_RATE_ID,
         TO_CHAR (RD.EFFECTIVE_DATE, 'yyyymmdd') AS EFFECTIVE_RATE,
         RD.MAX_BREAK,
         RD.UNIT_RATE,
         RD.CONSTANT_CHARGE,
         RD.MIN_CHARGE,
         RD.MAX_CHARGE
  FROM   RADIAL_RATE R, RADIAL_RATE_DETAIL RD, RADIAL_RATE_PERIOD RP
 WHERE   R.ID = RD.RADIAL_RATE_ID AND RD.RADIAL_RATE_ID = RP.RADIAL_RATE_ID AND R.ID = RP.RADIAL_RATE_ID
 ORDER BY R.ID;

Open in new window

0
Comment
Question by:Wilder1626
  • 3
  • 3
6 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39861940
you mean something like this?


SELECT   'H',
         'M',
         R.ID,
         TO_CHAR (RD.EFFECTIVE_DATE, 'yyyymmdd') AS EFFECTIVE_DATE,
         TO_CHAR (RP.DATE_INVALID, 'yyyymmdd') AS INVALID_DATE,
         R.BREAK_TYPE,
         RATE_UNIT,
         CURRENCY_CODE,
         R.RATE_MEASURE,
         INCREMENTAL_FLAG,
         REF_NAME,
         'D',
         'M',
         RD.RADIAL_RATE_ID,
         TO_CHAR (RD.EFFECTIVE_DATE, 'yyyymmdd') AS EFFECTIVE_RATE,
         RD.MAX_BREAK,
         RD.UNIT_RATE,
         RD.CONSTANT_CHARGE,
         RD.MIN_CHARGE,
         RD.MAX_CHARGE,
         max(RD.EFFECTIVE_DATE) over() max_effective,
        max(RP.DATE_INVALID) over() max_invalid
  FROM   RADIAL_RATE R, RADIAL_RATE_DETAIL RD, RADIAL_RATE_PERIOD RP
 WHERE   R.ID = RD.RADIAL_RATE_ID AND RD.RADIAL_RATE_ID = RP.RADIAL_RATE_ID AND R.ID = RP.RADIAL_RATE_ID
 ORDER BY R.ID;



if not,  please post sample data and expected output
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 39861986
Hi sdstuber

I still see weird results.

With the query you provided me, i still have the same result from my previous one.

Please look at the attachment.

You will see 2 sheets.

Query sheet is the result of the above SQL and in Result needed, what i'm expecting to receive.

You will also see the e tables:
RADIAL_RATE
RADIAL_RATE_DETAIL
RADIAL_RATE_PERIOD

Let me know if something can be done.

Thanks  for your help.
extract.xls
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39861994
the sample and expected looked like they had output from my incorrect query

going back to your original query though it looks like you're just trying to get one row for which ever one has the max of those 2 columns

so, just order by those descending and take whatever comes out on top


SELECT *
  FROM (SELECT 'H',
               'M',
               r.id,
               TO_CHAR(rd.effective_date, 'yyyymmdd') AS effective_date,
               TO_CHAR(rp.date_invalid, 'yyyymmdd') AS invalid_date,
               r.break_type,
               rate_unit,
               currency_code,
               r.rate_measure,
               incremental_flag,
               ref_name,
               'D',
               'M',
               rd.radial_rate_id,
               TO_CHAR(rd.effective_date, 'yyyymmdd') AS effective_rate,
               rd.max_break,
               rd.unit_rate,
               rd.constant_charge,
               rd.min_charge,
               rd.max_charge
          FROM radial_rate r, radial_rate_detail rd, radial_rate_period rp
         WHERE r.id = rd.radial_rate_id
           AND rd.radial_rate_id = rp.radial_rate_id
           AND r.id = rp.radial_rate_id
        ORDER BY rd.effective_date DESC, rp.date_invalid DESC)
 WHERE ROWNUM = 1
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 11

Author Comment

by:Wilder1626
ID: 39862000
I think it would work if i only have one ID.

Ex: 4TRACKS_PH37'.

But if i have 100 different IDs with multiple dates just like 4TRACKS_PH37'. I would get all most up to date for each IDs.

I'm just trying it and i only get 1 record (1 ID) when i should get about 145 different ones.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39862002
In that case you use row_number analytic, partitioning by your id

Same idea, but each id gets it's own counter


SELECT *
  FROM (SELECT 'H',
               'M',
               r.id,
               TO_CHAR(rd.effective_date, 'yyyymmdd') AS effective_date,
               TO_CHAR(rp.date_invalid, 'yyyymmdd') AS invalid_date,
               r.break_type,
               rate_unit,
               currency_code,
               r.rate_measure,
               incremental_flag,
               ref_name,
               'D',
               'M',
               rd.radial_rate_id,
               TO_CHAR(rd.effective_date, 'yyyymmdd') AS effective_rate,
               rd.max_break,
               rd.unit_rate,
               rd.constant_charge,
               rd.min_charge,
               rd.max_charge,
               ROW_NUMBER()
                   OVER(PARTITION BY r.id ORDER BY rd.effective_date DESC, rp.date_invalid DESC)
                   rn
          FROM radial_rate r, radial_rate_detail rd, radial_rate_period rp
         WHERE r.id = rd.radial_rate_id
           AND rd.radial_rate_id = rp.radial_rate_id
           AND r.id = rp.radial_rate_id)
 WHERE rn = 1
0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 39862025
Thanks for your help.

just went over all records and i have a perfect match.

Thanks again
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question