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.

ZJAY
  • 2,517
  • 9
  • 32
  • 51
  • A little formatting goes a long way to understand what you are trying to do... – Patrick Dec 19 '16 at 06:44
  • 3
    You don't need to write such a function. It's already there: `generate_series()` –  Dec 19 '16 at 06: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? – pozs Dec 19 '16 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. – ZJAY Dec 19 '16 at 14:29
  • @ a_horse_with_no_name Can you please elaborate further. – ZJAY Dec 19 '16 at 16:12
  • Possible duplicate of [Generate\_series in Postgres from start and end date in a table](http://stackoverflow.com/questions/29023336/generate-series-in-postgres-from-start-and-end-date-in-a-table) – Stavr00 Dec 20 '16 at 20:26
  • You couldn't boil this down to something more universal than EXACTLY what you're trying to do without any schema or test data? http://meta.dba.stackexchange.com/a/2642/2639 – Evan Carroll Dec 20 '16 at 20:39
  • @Evan Carroll I have sample data in a CSV format. What is the best way to share? – ZJAY Dec 20 '16 at 20:51

0 Answers0