title: The fastest way to fill table using data from other tables
body: <p>I've read the conception behind functional programing and that makes me reconsider my way of doing things. </p>
  
  <p>For example, there is a table:</p>
  
  <pre><code> - Client, Date, Trial, Full
   - Client1, 14.11.2012, 1, 1
   - Client1, 06.02.2013, NULL, 1
   - Client1, 27.03.2013, NULL, 1
   - Client1, 15.05.2013, NULL, 1
  </code></pre>
  
  <p>The table contains millions records and half a million clients. My goal is to transform this data to something like a status of client:</p>
  
  <pre><code> - Client, Date, Status
   - Client1, 14.11.2012, 'Mixed'
   - Client1, 01.12.2012, 'Unprocessed'
   - Client1, 01.01.2012, 'Unprocessed'
   - Client1, 13.01.2013, 'Slept'
   - Client1, 01.02.2013, 'Slept'
   - Client1, 06.02.2013, 'Processed'
   - Client1, 01.03.2013, 'Unprocessed'
   - Client1, 27.03.2013, 'Processed'
   - Client1, 01.04.2013, 'Unprocessed'
   - Client1, 01.05.2013, 'Unprocessed'
   - Client1, 15.05.2013, 'Processed'
   - Client1, 01.06.2013, 'Unprocessed'
   - Client1, 01.07.2013, 'Unprocessed'
   - Client1, 23.07.2013, 'Slept'
   - Client1, 01.08.2013, 'Slept'
   - Client1, 01.09.2013, 'Slept'
   - Client1, 01.10.2013, 'Slept'
   - Client1, 01.11.2013, 'Slept'
   - Client1, 01.12.2013, 'Slept'
   - Client1, 01.01.2014, 'Slept'
   - Client1, 10.01.2014, 'Left'
  </code></pre>
  
  <p>The short algorithm of the transformation is:</p>
  
  <ol>
  <li>If it's the first row and Trial = 1 and Full = 1 then Status = 'Mixed'</li>
  <li>If there is no data for a first day of month then Status = 'Unprocessed'</li>
  <li>If 60 days passed and there is no records containing Full = 1 then Status = 'Slept'</li>
  <li>If 240 days passed and there is no records containing Full = 1 then Status = 'Left'</li>
  <li>If there is a first day of month and previous Status = 'Slept' then
  Status = 'Slept</li>
  </ol>
  
  <p>There are a lot of cases I skipped, because the algorithm isn't issue, but tools.</p>
  
  <p>In order to transform data within SQL I used the following expressions:</p>
  
  <ul>
  <li>row_number() over (partition by [Client] order by [Date] asc)</li>
  <li>lag([Date],1) over (partition by [Client] order by [Date] desc)</li>
  <li>dateadd(day,1,eomonth([Date]))</li>
  <li>recursion</li>
  <li>etc</li>
  </ul>
  
  <p>I have feeling that it can't be the fastest way to transform data, also multi-treading (put every client in separate tread) may be very helpful, not sure how good sql at that. My execution plan is really huge after big number of extra cases.</p>
  
  <p>So, my question is what tool is the best to transform this data like that? Probably, programming language can handle that the way better?</p>
  
  <p><strong>Update</strong>: I prepared the requested SQL code. Feel free to find any issue:
  <a href="http://pastebin.com/3nCdfquG" rel="nofollow">http://pastebin.com/3nCdfquG</a></p>