SQL2

gistfile1.txt

select FS_PERM_SEC_ID, period, avg(p_price) avg_price from (
  select FS_PERM_SEC_ID, p_price, SECURITY_PRICE_DATE, 'DAY' period
  from FSDF_PRICE_EOD.FP_BASIC_BD where FS_PERM_SEC_ID = 'XQC8FD-S-ES' and 
  SECURITY_PRICE_DATE between sieve2.UTILS_PKG.add_businessdays(sysdate, -1) and sysdate
union all
  select FS_PERM_SEC_ID, p_price, SECURITY_PRICE_DATE, 'WEEK' period
  from FSDF_PRICE_EOD.FP_BASIC_BD where FS_PERM_SEC_ID = 'XQC8FD-S-ES' and 
  SECURITY_PRICE_DATE between sieve2.UTILS_PKG.add_businessdays(sysdate, -7) and sysdate
union all
  select FS_PERM_SEC_ID, p_price, SECURITY_PRICE_DATE, 'MONTH' period
  from FSDF_PRICE_EOD.FP_BASIC_BD where FS_PERM_SEC_ID = 'XQC8FD-S-ES' and 
  SECURITY_PRICE_DATE between sieve2.UTILS_PKG.add_businessdays(sysdate, -30) and sysdate
union all
  select FS_PERM_SEC_ID, p_price, SECURITY_PRICE_DATE, 'QUARTER' period
  from FSDF_PRICE_EOD.FP_BASIC_BD where FS_PERM_SEC_ID = 'XQC8FD-S-ES' and 
  SECURITY_PRICE_DATE between sieve2.UTILS_PKG.add_businessdays(sysdate, -90) and sysdate
union all
  select FS_PERM_SEC_ID, p_price, SECURITY_PRICE_DATE, 'YEAR' period
  from FSDF_PRICE_EOD.FP_BASIC_BD where FS_PERM_SEC_ID = 'XQC8FD-S-ES' and 
  SECURITY_PRICE_DATE between sieve2.UTILS_PKG.add_businessdays(sysdate, -365) and sysdate
) group by FS_PERM_SEC_ID, period;

Result:

XQC8FD-S-ES WEEK 7.249
XQC8FD-S-ES QUARTER 7.43192222222222222222222222222222222222
XQC8FD-S-ES DAY 7.314
XQC8FD-S-ES MONTH 7.4003
XQC8FD-S-ES YEAR 6.32793261455525606469002695417789757412