Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
supe avg_prices query
WITH dates AS (
select distinct FS_PERM_SEC_ID,
sieve2.utils_pkg.sv_businessday(sysdate-1) D0,
sieve2.utils_pkg.add_businessdays(sysdate, -5) D7,
sieve2.utils_pkg.sv_businessday(add_months(sysdate, -1)) M1,
sieve2.utils_pkg.sv_businessday(add_months(sysdate, -3)) M3,
sieve2.utils_pkg.sv_businessday(add_months(sysdate, -12)) Y1
from THEMES.SECURITIES_MAPPING_TEMP mapping
)
select dates.fs_perm_sec_id,
price1d.C_PRICE_CLOSE_USD price1d,
price7d.C_PRICE_CLOSE_USD price7d,
price1m.C_PRICE_CLOSE_USD price1m,
price3m.C_PRICE_CLOSE_USD price3m,
price1y.C_PRICE_CLOSE_USD price1y,
price_ytd.C_PRICE_CLOSE_USD price_ytd
from
FSDF_CALC.DAILY_PRICE_CLOSE_VIEW_1Y price1d,
FSDF_CALC.DAILY_PRICE_CLOSE_VIEW_1Y price7d,
FSDF_CALC.DAILY_PRICE_CLOSE_VIEW_1Y price1m,
FSDF_CALC.DAILY_PRICE_CLOSE_VIEW_1Y price3m,
FSDF_CALC.DAILY_PRICE_CLOSE_VIEW_1Y price1y,
FSDF_CALC.DAILY_PRICE_CLOSE_VIEW_1Y price_ytd,
dates
where dates.FS_PERM_SEC_ID in ('QG4JJ2-S-JP', 'WDHLLN-S-US') and
dates.FS_PERM_SEC_ID = price1d.FS_PERM_SEC_ID(+)
and dates.FS_PERM_SEC_ID = price7d.FS_PERM_SEC_ID(+)
and dates.FS_PERM_SEC_ID = price1m.FS_PERM_SEC_ID(+)
and dates.FS_PERM_SEC_ID = price3m.FS_PERM_SEC_ID(+)
and dates.FS_PERM_SEC_ID = price1y.FS_PERM_SEC_ID(+)
and dates.FS_PERM_SEC_ID = price_ytd.FS_PERM_SEC_ID(+)
and dates.D0 = price1d.price_date(+)
and dates.D7 = price7d.price_date(+)
and dates.M1 = price1m.price_date(+)
and dates.M3 = price3m.price_date(+)
and dates.Y1 = price1y.price_date(+)
and price_ytd.price_date(+) = THEMES.AVG_PRICES_PKG.ytd_day(dates.FS_PERM_SEC_ID)
;
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.