What I am attempting to do is create a function of the query below, where instead of using + 45 days, the function iterates through all values from + 35, 36, 37...+45 days and returns the MAX value for EACH cobrand_id.
Notice 45 days appears twice in this query. This would be the input variable.
with q1 as (
SELECT '17_Q4' qqyy, cobrand_id, sum(calc) AS a
FROM temp_08.jwn_calc s
where s.optimized_transaction_date > DATE '2016-10-29' AND
s.optimized_transaction_date <= DATE '2016-10-29' + interval '45 days'
GROUP BY qqyy, cobrand_id )
select q1.qqyy, q1.cobrand_id, q1.a/q2.d AS sum from q1
inner join (
SELECT cobrand_id AS c,
sum(CASE WHEN optimized_transaction_date > date '2015-10-31'
AND optimized_transaction_date <= date '2015-10-31' + interval '45 days' THEN Calc END) /
sum(CASE WHEN optimized_transaction_date > date '2015-10-31'
AND
optimized_transaction_date <= date '2015-10-31' + interval '91 days' THEN Calc END) AS d
FROM temp_08.jwn_calc
GROUP BY cobrand_id
) as q2 on q1.cobrand_id = q2.c
Conceptually, what I am doing is looking at what % of the fiscal quarter was complete 45 days into last year, and using that to extrapolate/divide to derive a full value of this year's quarter.
In response to Generate_series, my question is distinct in that I am querying across 15 different days (35, 36, 37...45 days) to pull out the max value.
generate_series()+ 45right now is how many rows should be considered inSUM()s. Max of those will always be the longest range (unless you have negative data OFC). So it shouldn't worth to even try the shorter ranges. Or you want to calculate the max ofq1.a/q2.din the outer query? I hardly see how you could extrapolate that; could you expand your question?