Get Most Current Date

Database
N/A

Get Most Current Date

Good Day All,

This is my first time posting and have looked for help here before, but can't seem to figure out what I need to do.  So I figured I would reach out and ask for assistance pertaining to my query.

I need to run a query that allows me to pull out data that has the most current date.  Below is the query I am using:

select ca8172.*

,ord.order_due_dt as ORD_DUE_DT

,ord.cust_reason_txt as RSN_TXT

,ord.order_reason_cd as ORD_RSN_CD

from ORDER_ACTION ord, ca8172_AcctCharge ca8172

where ord.acct= ca8172.acct

and ord.bypass_reason_cd = 'VS'

and ord.current_status_cd = 'DO'

and ord.order_reason_cd in ('VA','ML','DR')

When I run the query, the information looks like this:

ACCT      -- CODE -- CHG DT  -- CHRG -- ORD DUE DT -- RSN TXT -- ORD RSN CD

1598753 -- IPTV    -- 6/1/2013 -- 5.00    -- 5/1/2013       -- Voluntary -- VA       

1598753 -- IPTV    -- 6/1/2012 -- 5.00    -- 5/9/2012       -- Voluntary -- VA       

1598753 -- IPTV    -- 2/1/2012 -- 5.00    -- 1/13/2012     -- Voluntary -- VA      

What I would like to do is isolate the most current date; in this instance 05/01/2013 in the Order Due Date so that all other date (05/09/2012 & 01/13/2012) are no longer relevant.  How do I go about doing that?  Any assistance would be appreciative.

Thanks!

Tags (1)
3 REPLIES
N/A

Re: Get Most Current Date

The easiest way is to add a RANK:

select ca8172.*
,ord.order_due_dt as ORD_DUE_DT
,ord.cust_reason_txt as RSN_TXT
,ord.order_reason_cd as ORD_RSN_CD
from ORDER_ACTION ord, ca8172_AcctCharge ca8172
where ord.acct= ca8172.acct
and ord.bypass_reason_cd = 'VS'
and ord.current_status_cd = 'DO'
and ord.order_reason_cd in ('VA','ML','DR')
qualify rank() over (order by ORD_DUE_DT desc) = 1

If you want to get the most current date for different groups, e.g. per ACCT simply add "PARTITION BY ACCT"  before the ORDER BY

Dieter

N/A

Re: Get Most Current Date

DNOETH, thanks for the update.  It appears that I didn't explain what I was needing very well, so I will try to do so this time, as when I tried the above query, it provided an overall MAX DATE to all accounts.

Below is the data that I have:

ACCT      -- CODE -- CHG DT  -- CHRG -- ORD DUE DT -- RSN TXT -- ORD RSN CD

1598753 -- IPTV    -- 6/1/2013 -- 5.00    -- 5/1/2013       -- Voluntary -- VA       

1598753 -- IPTV    -- 6/1/2012 -- 5.00    -- 5/9/2012       -- Voluntary -- VA       

1598753 -- IPTV    -- 2/1/2012 -- 5.00    -- 1/13/2012     -- Voluntary -- VA      

1425410 -- IPTV    -- 6/1/2013 -- 5.00    -- 5/19/2013     -- Voluntary -- VA       

1425410 -- IPTV    -- 2/1/2012 -- 5.00    -- 1/23/2012     -- Voluntary -- VA      

1032045 -- IPTV    -- 4/1/2013 -- 5.00    -- 3/3/2013       -- Voluntary -- VA       

1032045 -- IPTV    -- 8/1/2012 -- 5.00    -- 7/13/2012     -- Voluntary -- VA      

1135015 -- IPTV    -- 8/1/2013 -- 5.00    -- 7/28/2013     -- Voluntary -- VA       

1135015 -- IPTV    -- 1/1/2013 -- 5.00    -- 12/13/2012   -- Voluntary -- VA

1135015 -- IPTV    -- 7/1/2012 -- 5.00    -- 6/7/2012       -- Voluntary -- VA       

1135015 -- IPTV    -- 4/1/2012 -- 5.00    -- 3/21/2012     -- Voluntary -- VA      

The query provided worked, by picking up the MAXDATE of 7/28/2013, but applied it to all the accounts, since I didn't inform you that I needed the MAXDATE per each Account.  My apologies.  So what I am looking for is a query (what I currently have is still posted above) that will filter out the results so that it picks up the MAXDATE for each respective Account, where the results look something like this:

ACCT      -- CODE -- CHG DT  -- CHRG -- ORD DUE DT -- RSN TXT -- ORD RSN CD

1598753 -- IPTV    -- 6/1/2013 -- 5.00    -- 5/1/2013       -- Voluntary -- VA  

1425410 -- IPTV    -- 6/1/2013 -- 5.00    -- 5/19/2013     -- Voluntary -- VA   

1032045 -- IPTV    -- 4/1/2013 -- 5.00    -- 3/3/2013       -- Voluntary -- VA     

1135015 -- IPTV    -- 8/1/2013 -- 5.00    -- 7/28/2013     -- Voluntary -- VA   

Thanks again,

Charles

N/A

Re: Get Most Current Date

Hi Charles,

simply add a PARTITION BY:

qualify rank() over (PARTITION BY ACCT order by ORD_DUE_DT desc) = 1

Dieter