Home » SQL & PL/SQL » SQL & PL/SQL » Subtracting Years from As_Of_Date (PL/SQL, Version 9.0.6.1665)
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'
|
|
|
|
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'
|
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Mar 18 20:39:14 CDT 2024
|