0

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.

8
  • A little formatting goes a long way to understand what you are trying to do... Commented Dec 19, 2016 at 6:44
  • 3
    You don't need to write such a function. It's already there: generate_series() Commented Dec 19, 2016 at 6:53
  • You need a MAX of what exactly? Because what you filter with + 45 right now is how many rows should be considered in SUM()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 of q1.a/q2.d in the outer query? I hardly see how you could extrapolate that; could you expand your question? Commented Dec 19, 2016 at 10:26
  • Thanks for the comments. I need the max value of q1.a/q2.d. +45 doesn't necessarily yield the highest value because +45 impacts both the numerator AND denominator, and they are pulling from two different time series/intervals (2016 in the numerator, 2015 in the denominator). I am looking for which date (i.e., +45 or +44 or +43) yields the highest value for q1.a/q2.d. Commented Dec 19, 2016 at 14:29
  • @ a_horse_with_no_name Can you please elaborate further. Commented Dec 19, 2016 at 16:12

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.