Get Most Current Date

Database
Fan

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
Senior Apprentice

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

Fan

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

Senior Apprentice

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