Wilder1626
asked on
Oracle - Max effective dates and Max invalid date
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.
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;
ASKER
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
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
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
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,
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,
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
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your help.
just went over all records and i have a perfect match.
Thanks again
just went over all records and i have a perfect match.
Thanks again
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