Hi All,
Based on the date range in calendar table, we need to run the following query. Currently, there is no change in date range and it is 3, and following
query is running in system.
SELECT S.ID,
S.INTRL_ID,
MAX(
GREATEST (
((today.price - yesterday.price)/yesterday.price)
((yesterday.price - dbyesterday.price)/dbyesterday.price)
)
) * 100.00 MAX_PRICE
FROM calendar C_today,
calendar c_yesterday,
calendar c_dbyesterday
price today,
price yesterday,
price dbyesterday,
detail s
where s.id = today.id
and s.id = yesterday.id
and s.id = dbyesterday.id
and today.dt = c_todat.dt
and yesterdat.dt = c_yesterday.dt
and dbyesterday.dy = c_dbyesterday.dt
and c_today.dt = sysdate
and c_yesterday.dt = sysdate - 1
and c_dbyesterday.dt = syadate - 2
But, as now we need to make it dynamic, lets say instead of 3, date range in calendar table has value 2 then
SELECT S.ID,
S.INTRL_ID,
MAX(
GREATEST (
((today.price - yesterday.price)/yesterday.price)
)
) * 100.00 MAX_PRICE
FROM calendar C_today,
calendar c_yesterday,
price today,
price yesterday,
detail s
where s.id = today.id
and s.id = yesterday.id
and today.dt = c_todat.dt
and yesterdat.dt = c_yesterday.dt
and today.dt = sysdate
and yesterdat.dt = sysdate -1
and if date range in calendar table has value 4 then...
SELECT S.ID,
S.INTRL_ID,
MAX(
GREATEST (
((today.price - yesterday.price)/yesterday.price)
((yesterday.price - dbyesterday.price)/dbyesterday.price)
((dbyesterday.price - lastday.price)/lastday.price)
)
) * 100.00 MAX_PRICE
FROM calendar C_today,
calendar c_yesterday,
calendar c_dbyesterday,
calendar c_lastday,
price today,
price yesterday,
price dbyesterday,
price lastday
detail s
where s.id = today.id
and s.id = yesterday.id
and s.id = dbyesterday.id
and s.id = lastday.id
and today.dt = c_todat.dt
and yesterdat.dt = c_yesterday.dt
and dbyesterday.dy = c_dbyesterday.dt
and lastday.dt = c_lastday.dt
and today.dt = sysdate
and yesterday.dt = sysdate - 1
and dbyesterday.dt = sysdate - 2
and lastday.dt = sysdate - 3
and so on....
can someone help me on this... how to dynamic select, from and where condition? If it is possible, using any analytical function, please do let me know