title: Weekly Sales Total in Fact Table using SSIS
body: <p>I have 3 sales data tables that I have converted into 4 Dimension tables and 1 Fact table. </p>
  
  <p>I have everything populated properly, and am wanting a "daily-sales" and "weekly-sales" in my Fact table. </p>
  
  <p>My original 3 tables are as follows is as follows:</p>
  
  <ul>
  <li><p><strong><code>Order</code></strong> (order# (PK), product-id (FK), order-date, total-order, customer# (FK))
  <br>domains are numeric, numeric, Date, Currency,numeric </p></li>
  <li><p><strong><code>Product</code></strong> (product-id (PK), prod-name, unit-cost, manufacture-name)
  <br>domains are numeric, nvarchar, money, nvarchar.</p></li>
  <li><p><strong><code>Customer</code></strong> (customer# (PK), cust-name, address, telephone#)
  <br>domains are Number, nvarchar, nvarchar,  nvarchar </p></li>
  </ul>
  
  <p>The Star Schema of the data warehouse is linked here:</p>
  
  <p><img src="https://i.stack.imgur.com/ejR8R.png" alt="enter image description here"></p>
  
  <p>So I have only 10 records in each table(tiny!), and am just testing concept now. "daily-order" in the Fact table is easily translated from "total-order" in the Order table. My difficulty is getting the weekly totals. I used a derived column and expression "DATEPART("wk",[order-date])" to create my week column in the time dimension. </p>
  
  <p>So I guess my question is, how do I get the weekly-sales from this point? My first guess is to use another derived column after the end of my Lookup sequences used to load my Fact table. My second guess is..... ask for help on stackoverflow. Any help would be appreciated, I will supply any info needed! Thanks in advance!</p>
  
  <p>For the record I tried creating the derived column as I previously described, but could not figure out syntax that would be accepted.... </p>
  
  <p><strong>EDIT</strong>I want to list the weekly-sales column by product level, and as I used DATEPART to derive the week column I do not need last 7 days but rather just a total for each week. My sales data stretches across only 2 consecutive weeks, so my fact table should list 1 total 7 times and a second total 3 times. I can either derive this from the original Order table, or from the tables I have in my DW environment. Preferably the latter. One note is that I am mainly restricted to SSIS and its tools (There is "Execute SQL syntax" tool, so I can still use a query)</p>