title: Latest records using sql
body: <p>I need to build weekly aggregated data for sales. Where <code>TRNST_QTY</code>is the latest available quantity of the closing week and <code>PRCHS_QTY</code> is the sum of purchase quantity for the particular week. I have date dimension where date key and corresponding week key is present.</p>
  
  <p>If there is any week there is no transaction happened it should take the last week's <code>TRNST_QTY</code> and <code>PRCHS_QTY</code> will be <code>0</code></p>
  
  <p><strong><em>Table 1 (Sales table):</em></strong></p>
  
  <pre><code>DT_KEY      ITEM    TRNST_QTY   PRCHS_QTY
  20160515    4098    2               1
  20160516    4098    10              1
  20160601    4098    4               0
  20160603    4098    8               0
  20160611    4098    6               4
  20160616    4098    4               0
  20160622    4098    8               0
  20160623    4098    2               0
  20160714    4098    11              3
  </code></pre>
  
  <p><strong>Output required(weekly aggregate):</strong></p>
  
  <pre><code>WK_DT_KEY   ITEM    TRNST_QTY   PRCHS_QTY
  20160521    4098    10          2
  20160607    4098    8           0
  20160614    4098    6           4
  20160621    4098    4           0
  20160630    4098    2           0
  20160707    4098    2           0
  20160714    4098    11          3
  </code></pre>
  
  <p><strong>Date &amp; Week mapping:</strong></p>
  
  <pre><code>DT_KEY      WK_DT_KEY
  20160515    20160521
  20160516    20160521
  20160517    20160521
  20160518    20160521
  20160519    20160521
  20160520    20160521
  20160521    20160521
  20160601    20160607
  20160602    20160607
  20160603    20160607
  20160604    20160607
  </code></pre>
  
  <p>and so on </p>
  
  <p>So far I am able to below query but its giving only latest week information </p>
  
  <pre><code>select * from 
  (
  SELECT WK_DT_KEY
      ,ITEM
      ,sum(PRCHS_QTY) OVER (
          PARTITION BY WK_DT_KEY
          ,ITEM
          ORDER BY WK_DT_KEY
              ,ITEM
          ) AS PRCHS_QTY
      ,ROW_NUMBER() OVER (
          PARTITION BY ITEM order by a.dt_key desc
          ) AS RNK
  FROM fct_itm a
      ,DIM_DT DIM_DT
  WHERE a.dt_key &lt;= '20170207'
      AND DIM_DT.DT_key = a.dt_key
  )
  where RNK = 1;
  </code></pre>
  
  <p>I want to avoid procedural approach or connect by clause. 
  I will be grateful any help.</p>