title: SQL - Creating a historical fact table from latest snapshot data
body: <p>I'm currently working on an interesting problem and though I'd share it to solicit ideas.  </p>
  
  <p>I have a table, lets call it <em>Table One</em>.  Each <em>ID</em> has an <strong>effective date range</strong> implied by <strong>datetimes</strong> (these are actually DATETIME types, even though I've presented them strangely below) <em>Date A</em> and <em>Date B</em>.  So for example, ID 1 was in effect from 1-Nov until 3-Nov.</p>
  
  <p><img src="https://i.stack.imgur.com/Rz6xg.png" alt="enter image description here"></p>
  
  <p>From this table, I'd like to produce a table similar to <em>Target Table</em> (pictured below), where each ID/Effective Date combination is listed row-by-row:</p>
  
  <p><img src="https://i.stack.imgur.com/S533v.png" alt="enter image description here"></p>
  
  <p>To accomplish this, I also have a table containing sequential dates, with the string format for the date, along with a start datetime and an end datetime:</p>
  
  <p><img src="https://i.stack.imgur.com/5RF0M.png" alt="enter image description here"></p>
  
  <p>My experience is mostly with C# and while I could whip up a script to accomplish this, I worry that I would be hacking around what can be accomplished with pure SQL.</p>