OraFAQ Forum: SQL & PL/SQL » Subtracting Years from As_Of

Home » SQL & PL/SQL » SQL & PL/SQL » Subtracting Years from As_Of_Date (PL/SQL, Version 9.0.6.1665)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Subtracting Years from As_Of_Date [message #632054]

Fri, 23 January 2015 12:25

jburdell
Messages: 2
Registered: January 2015
Location: Boston

Junior Member

Hi, I am running a performance query where I want to get Yearly performance for multiple years with As_Of_date as a parameter of the query. I was wondering whether there is a way to subtract one year of the as_of_date so that you only need to input one date and it gets you performance for multiple years.

Equally if you know an easier way to do this then I would appreciate the help. I have included the query below.

select agh.grp_id,
agh.perf_incep_dt as "Since Incept Dt",

Performance package(agh.grp_id,'T','s','C','&as_of_date1',12,agh.base_curcy_cd) as "2014",
Performance package(agh.grp_id,'T','s','C','&as_of_date2',12,agh.base_curcy_cd) as "2013",
Performance package(agh.grp_id,'T','s','C','&as_of_date3',12,agh.base_curcy_cd) as "2012",
Performance package(agh.grp_id,'T','s','C','&as_of_date4',12,agh.base_curcy_cd) as "2011",
Performance package(agh.grp_id,'T','s','C','&as_of_date5',12,agh.base_curcy_cd) as "2010",
Performance package(agh.grp_id,'T','s','C','&as_of_date6',12,agh.base_curcy_cd) as "2009",
Performance package(agh.grp_id,'T','s','C','&as_of_date7',12,agh.base_curcy_cd) as "2008",
Performance package(agh.grp_id,'T','S','C','&as_of_date8',12,agh.base_curcy_cd) as "2007",
Performance package(agh.grp_id,'T','S','C','&as_of_date9',agh.perf_incep_dt,agh.base_curcy_cd) as "Since Incept"

from accountgrouptable agh
where agh.grp_id = '&grp_id'

Report message to a moderator

Re: Subtracting Years from As_Of_Date [message #632057 is a reply to message #632054]

Fri, 23 January 2015 12:26

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Report message to a moderator

Re: Subtracting Years from As_Of_Date [message #632058 is a reply to message #632057]

Fri, 23 January 2015 12:35

jburdell
Messages: 2
Registered: January 2015
Location: Boston

Junior Member

Apologies for the format of the code. As I said above I am seeking a way to be able to remove the multiple As of Dates that I currently have in this query. I know it is possible to use subtract with sysdate but was wondering if there is anything similar I could use here.

Thank you

select agh.grp_id,
  agh.perf_incep_dt as "Since Incept Dt",
  
  perfserv_pkg.RtP(agh.grp_id,'T','s','C','&as_of_date1',12,agh.base_curcy_cd) as "2014",
  perfserv_pkg.RtP(agh.grp_id,'T','s','C','&as_of_date2',12,agh.base_curcy_cd) as "2013",
  perfserv_pkg.RtP(agh.grp_id,'T','s','C','&as_of_date3',12,agh.base_curcy_cd) as "2012",
  perfserv_pkg.RtP(agh.grp_id,'T','s','C','&as_of_date4',12,agh.base_curcy_cd) as "2011",
  perfserv_pkg.RtP(agh.grp_id,'T','s','C','&as_of_date5',12,agh.base_curcy_cd) as "2010",
  perfserv_pkg.RtP(agh.grp_id,'T','s','C','&as_of_date6',12,agh.base_curcy_cd) as "2009",
  perfserv_pkg.RtP(agh.grp_id,'T','s','C','&as_of_date7',12,agh.base_curcy_cd) as "2008",
  perfserv_pkg.RtP(agh.grp_id,'T','S','C','&as_of_date8',12,agh.base_curcy_cd) as "2007",
  perfserv_pkg.RtP(agh.grp_id,'T','S','C','&as_of_date9',agh.perf_incep_dt,agh.base_curcy_cd) as "Since Incept"
  
from wmc.acct_group_hst_tbl agh
where agh.grp_id = '&grp_id'

Report message to a moderator

Re: Subtracting Years from As_Of_Date [message #632061 is a reply to message #632058]

Fri, 23 January 2015 13:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Realize we only know what you post here.

we don't have your tables, data, or requirements.

I be honest with you I have no idea what the correct answer would look like since I don't understand what you are asking about.

Report message to a moderator