This might be a very basic question so apologies in advance. I need number of samples from last 3 months, 6 months, 1 year, 2 years etc. and depending on if I have enough samples,(I'm trying to collect enough no. of samples by going back in history, so when I collect enough, I'll stop) I will run some calculation with them. First I pick whatever data is available from the last 2 years. Then I look up how many samples are there for the past year and for the past 2 years. Let's say if I have say 30 samples last year that's enough and I take them. If not I Stretch to 2 years and try to collect the values of all those samples.
COUNT(CASE WHEN ((to_date(sysdate)-to_date(c.sample_date)) < 365) THEN 1 END) AS TOTAL_N_IN_1_YEAR,
COUNT(CASE WHEN ((to_date(sysdate)-to_date(c.sample_date)) BETWEEN 0 and 730) THEN 1 END) AS TOTAL_N_IN_2_YEARS,
CASE WHEN (to_date(sysdate)-to_date(c.sample_date) < 365) THEN 1 ELSE 0 END as LASTYEAR,
ROUND(avg(s.MV),9) mean,
from
schema.t_samples s
schema.t_samples_calc c,
where
c.sample_id = s.sample_id
c.sample_date > sysdate-730
group by c.ch_id, c.ckc_id, CASE WHEN (to_date(sysdate)-to_date(c.sample_date) < 365) THEN 1 ELSE 0 END
How can I refer to these values I calculated in TOTAL_N_IN_1_YEAR, TOTAL_N_IN_1_YEARS and use them to choose only the values of the samples in the period I need?
I would like to achieve something like this
CHANNEL ID | NO_OF_SAMPLES_IN_LAST_1_YEAR | NO_OF_SAMPLES_IN_LAST_2_YEARS | MEAN | MEDIAN | IQR ...etc.
23 32 (let's say >30 is enough) 54 | ... | ...
now I need s.value for only the last 32 samples...
say, If TOTAL_N_IN_1_YEAR > 30 then I want the values only from last 1 year, I will calculate their MEAN, MEDIAN, IQR etc.
This works right now if TOTAL_N_IN_1_YEAR > 30, however won't work for <30 for example.
Thank you a lot for your help :)
