title: SQL Server building a dynamic calendar from table data and selecting data from other tables
body: <p>I have this calendar table that specifies a date range period for each year. It has data like </p>
  
  <p><strong>TBL1</strong>:</p>
  
  <pre><code>start_date        end_dt        yr    period_num
  ------------------------------------------------
  2015-12-27        2016-01-22    2016    1            
  2015-01-24        2016-02-27    2016    2
  2015-02-28        2016-03-26    2016    3
  ...
  2016-11-27        2016-12-31    2016    12
  2017-01-01        2017-01-01    2017    1
  </code></pre>
  
  <p>Then, I have another table where I have product entries such as</p>
  
  <p><strong>TBL2</strong>:</p>
  
  <pre><code>prod_id        weeknum      wkstartdate     postingDate     qty
  ----------------------------------------------------------------
  1043890         1           2016-01-10      2016-01-18      3
  1043890         2           2016-01-17      2016-01-18      6
  1043890         3           2016-01-24      2016-01-18      2
  1043890         4           2016-01-31      2016-01-18      0
  ...
  1043890         50          2016-12-18      2016-01-18      1
  1043890         51          2016-12-25      2016-01-18      2
  1043890         52          2017-01-01      2016-01-18      7
  </code></pre>
  
  <p>Table AllProd2 is defined here <a href="http://sqlfiddle.com/#!6/5836b/17" rel="nofollow">http://sqlfiddle.com/#!6/5836b/17</a> </p>
  
  <p>It basically  has details about a product and some hierarchy info. TBL2 has data for parent ID only but once I join the tables I need to display the ID with the highest priority number (if there are any child).</p>
  
  <p>What I'd like to do is build a yearly calendar based on the period defined in TBL1. When I run the query, it needs to calculate a year from current week. then capture the data from table 2 for each period and build a result set with product details like this below:</p>
  
  <p>it will need to sum up all qty for a given monthly period that's defined in TBL1</p>
  
  <p>Desired result:</p>
  
  <pre><code>Prod_id     ParentID    Descrip Dept    SubDept Class   SubClass    Data-12/27/15   Data-1/24/16    ....... Data-11/27/16    Data-1/1/17    52wTotal
  1043889     1043890     14 RD   abc     def     xyz     gty             9               7                       3               7           49 (summing up all qty for this product)
  .....
  </code></pre>
  
  <p>I have a current solution that's outdated and uses several stages to do something somewhat similar but hoping to streamline this and make it efficient. Appreciate any help.</p>
  
  <p>Thanks.</p>