title: PostgreSQL - show data of previous year + current year's week
body: <p>I need to make a column that would show previous year's profit till given year's week.
  So it would split current year in weeks and it would show what is the profit for given week.
  To make it more clear let say previous year profit was 1000. This year's first week profit is 100, second week's 200, thirds, week -100 (was loss) and so on.
  So it should look like this:</p>
  
  <pre><code>week1|week2|week3|
  1100 |1300 |1200 |
  </code></pre>
  
  <p>What I tried was:</p>
  
  <pre><code>SELECT
  CASE when f1.year = DATE_PART('year', now()) THEN f1.week END as week,
  profit as profit
  FROM (
  SELECT 
  DATE_PART('week', so.date_order) as week,
  DATE_PART('year', so.date_order) as year,
  so.profit as profit
  FROM
  sale_order as so
  GROUP BY
  week, year, profit
  WHERE
  so.date_order &gt;= date_trunc('year', now() - '1 year'::interval)::timestamp::date  and so.date_order &lt;= date_trunc('year', now()+ '1 year'::interval)::timestamp::date-1 
  )as f1
  GROUP BY
  week, profit
  ORDER BY
  week
  </code></pre>
  
  <p>But this is not working as I need, because it splits profit for every given week. What I mean it shows only that weeks profit, but I need 'that weeks profit' + 'previous years profit'.</p>
  
  <p>My query trying window function:</p>
  
  <pre><code>(
  SELECT
  x.id as id,week as week, x.last_year_profit + y.running_profit as week_profit
  FROM
  (
  SELECT
  min(sol.id) as id,
   --DATE_PART('year',  so.date_order) AS calcyear, DATE_PART('week',  so.date_order) AS calcweek,
  sum(sol.price_subtotal - (CASE WHEN sol.account_cost_amount != 0 THEN sol.account_cost_amount ELSE sol.purchase_price END )) as last_year_profit
  -- sum(sol.price_subtotal) as price_unit, sum(sol.purchase_price) as purchase_price, sum(sol.account_cost_amount) as account_cost_amount
  FROM
  sale_order as so
  INNER JOIN sale_order_line as sol ON (sol.order_id = so.id)
  INNER JOIN res_partner as rp ON (so.partner_id = rp.id)
  WHERE EXISTS (
  SELECT * FROM  res_partner_category_rel rpcl
  WHERE 
  rpcl.partner_id=rp.id and rpcl.category_id=37
  and (so.date_order &gt;= date_trunc('year', now() - '1 year'::interval)::timestamp::date  and so.date_order &lt;= date_trunc('year', now())::timestamp::date-1 )
  and so.state != 'cancel'
  )
  ) as x
  CROSS JOIN (
  SELECT
  date_trunc('week', so.date_order) as week,
  sum(sum(sol.price_subtotal - (CASE WHEN sol.account_cost_amount != 0 THEN sol.account_cost_amount ELSE sol.purchase_price END ))) OVER  ( ORDER BY date_trunc('week', so.date_order)) as running_profit
  
  FROM
  sale_order as so
  INNER JOIN sale_order_line as sol ON (sol.order_id = so.id)
  INNER JOIN res_partner as rp ON (so.partner_id = rp.id)
  WHERE EXISTS (
  SELECT * FROM  res_partner_category_rel rpcl
  WHERE 
  rpcl.partner_id=rp.id and rpcl.category_id=37
  AND so.date_order &gt;= date_trunc('year', now())::timestamp::date
  AND    so.date_order &lt;  date_trunc('year', now() + '1 year'::interval)::timestamp::date 
  and so.state != 'cancel'
  )
  GROUP BY
  week
  ) as y
  GROUP BY
  id, week,week_profit
  ) as f1
  </code></pre>
  
  <p>For some reason it does not split profit in weeks, but show only one row total like this:</p>
  
  <pre><code>week    |week_profit|
  20130114| 1500       |
  </code></pre>