Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQL2
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;
@daniilyar

This comment has been minimized.

Copy link
Owner Author

daniilyar commented Aug 19, 2014

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.