title: Extracting data from MS SQL Server-2008 referring multiple tables
body: <p>I have had asked a similar question <a href="https://stackoverflow.com/questions/28853364/matching-data-from-three-tables-in-sql-server-2008/28855348?noredirect=1#comment46247073_28855348">here</a> and have got help from <a href="https://stackoverflow.com/users/1175077/jpw">jpw</a> who helped me with the query. The situation here remains same but only a bit more detail added. I have four tables. Sample structure for three of them is given below:
  <img src="https://i.stack.imgur.com/NDQHt.png" alt="enter image description here"></p>
  
  <p>I have been helped to form query which goes as below:</p>
  
  <pre><code>select 
      d.LOTQty, 
      ApprovedQty = count(d.SerialNo),
      d.DispatchDate,
      Installed = count(a.SerialNo) + count(r.SerialNo)
  from 
      Despatch d 
  left join 
      Activation a 
       on d.SerialNo= a.SerialNo
      and d.DispatchDate &lt;= a.ActivationDate 
      and d.LOTQty = a.LOTQty
  left join 
      Replaced r 
        on d.SerialNo= r.SerialNo
       and d.DispatchDate &lt;= r.ActivationDate
       and (a.ActivationDate is null or a.ActivationDate &lt; d.DispatchDate)
  where 
      d.LOTQty = 15
  group by 
      d.LOTQty, d.DispatchDate, d.STBModel
  </code></pre>
  
  <p>For understanding sake, above query match <strong><code>Despatch</code></strong> table's <code>SerialNo</code> with <strong><code>Activation</code></strong> table. If match found it checks for Date difference. If <code>DespatchDate &lt; ActivationDate</code> only those numbers are considered while others(which didn't match or whose <code>DispatchDate &gt; ActivationDate</code>) are matched with Replaced with similar date criteria. So at the end we find 9 matches i.e 7 from <strong><code>Activation</code></strong> and 2 from <strong><code>Replaced</code></strong> as below:</p>
  
  <pre><code>LotQty | ApprovedQty | DispatchDate | Installed
    15   |      10     |   2013-8-7   |    9
  </code></pre>
  
  <p>I want to display two more columns in here i.e <strong><code>DOA</code></strong> and <strong><code>Bounce</code></strong> like this:  </p>
  
  <pre><code>LotQty | ApprovedQty | DispatchDate | Installed | DOA | Bounce
    15   |      10     |   2013-8-7   |    9      |  2  |   4
  </code></pre>
  
  <p>DOA and Bounce should be calculated with difference between 4th table i.e <strong><code>Failed</code></strong> table's <code>FailedDate</code> and the above 9 matched SerialNo's respective Activation/Record date(henceforth termed as act_rec_date). <strong><code>Failed</code></strong> table and Intermediate 9 matched SerialNo's structure is shown below:</p>
  
  <p><img src="https://i.stack.imgur.com/Risvd.png" alt="enter image description here"></p>
  
  <p><strong><code>Intermediate</code></strong> table doesn't physically exist. It is just for reference and to provide more clarity. <strong><code>Intermediate</code></strong> table contain those <code>SerialNo</code>, which were matched with <strong><code>Activation</code></strong> and <strong><code>Replaced</code></strong> table. The <code>act_rec_Date</code> field is correspondingly matched Activation/Record Date.  </p>
  
  <ol>
  <li><strong>DOA &amp; Bounce</strong> = We should match all the 9 resultant SerialNo's(i.e Intermediate table) with <strong><code>Failed</code></strong> table. If matched, calculate difference between <code>FailedDate</code> and <code>act_rec_date</code>. If difference is <strong><code>(0 to &lt;=10 days)</code></strong> then count it under <code>DOA</code> and if difference is <strong><code>(&gt;10 days to &lt;=180 days)</code></strong> then count it under <code>Bounce</code>. From <strong><code>Failed</code></strong> we find 6 matches out of which Product1,2 falls in <code>DOA</code> as difference between act_rec_Date is 0 and Product7,8,9 &amp; 10 falls under <code>Bounce</code> as their difference is <code>89 | 54 | 61 | 61</code>. So as shown above <strong><code>DOA = 2</code></strong> and <strong><code>Bounce = 4</code></strong></li>
  </ol>
  
  <p>I want to build a query which could give me DOA and Bounce as well. I tried creating a temp table and dumping the resultant SerialNo's and act_rec_Date into it. Next I tried to match temp table and Failed table. I couldn't get it working and further more it took around 7 minutes to even execute the query. </p>
  
  <p>P.S- My Actual tables contain around 50k to 100k data entries.</p>