title: Creating a SQL Stored Procedure to aggregate data from multiple tables to create End of Month report
body: <p>Currently I am using MS Sql2000, though we are discussing upgrading this to 2005 (if that affects anything, Im guessing that it is pretty standard SQL that I need)</p>
  
  <p>One of our products Tracks Sales from various departments around the country. 
  Currently, I run 3 Almost identical Stored Procedures in Query Analyzer, and then copy the results into excel, and spend an hour manipulating it all onto one spreadsheet.</p>
  
  <p>What I'd like to do is to come up with a solution for combining all my stored procedures so that I end up with a single, combined Dataset I can just drop into excel and mail to the interested parties.</p>
  
  <p>The solution I started to come up with involved a temp table, where I would first pull in all the sites, and then in turn update this with the results from each of the queries I currently run independently, but I got into all kinds of trouble, so I figured better to start again, and to ask for guidance and help from the off.</p>
  
  <p>SQL is kind of paraphrased so please dont pick me up on simple syntax errors :-)</p>
  
  <p>A much simplified set of data would be;
      table site {
      id,    location}</p>
  
  <pre><code>table salesEnquiry {siteId, custoemrId, 
  DepositDate, BalancePaidDate, Status} 
  --Status can be (0,1,2,3 for not started, deposit, completed,cancelled
  </code></pre>
  
  <p>And from that, I am thinking along the lines of;</p>
  
  <pre><code>--Set up the Whole Table
  declare @tmp TABLE (id, Site, Deposits, Completed, Cancelled) 
  
  insert into @tmp (id, site)
  select id, site from site
  
  update @tmp
  set Deposits = (select count(customerId) from salesEnquiry
      where DepositPaidDate &gt;= @startOfMonth and DepositPaidDate &lt;= @endOfMonth and status = 1)  
  
  ** This is the point I get lost I think, as Im not sure how to relate total number of deposits taken against the tmpId's as not all departments will take a deposit each month.
  </code></pre>
  
  <p>I would then repeat the above methodology for Completed and Cancelled Customers so I have a final table like below.</p>
  
  <pre><code>SiteId    Location    TotalDeposits    TotalCompleted    TotalCancelled    
    1          a             3               0                  1
    2          b             0               0                  0
    3          c             1               17                 0
  </code></pre>
  
  <p>edited to add sample data + Outcome.
  ** Sorry, cant figure the formatting **</p>
  
  <p>Ok, hope this helps.
      site
      id   location
      1    a
      2    b
      3    c</p>
  
  <pre><code>salesEnquiry
  id   custId   deposit      complete     status
  1    1        10/05/2011   null         1
  2    2        11/04/2011   11/05/2011   2
  1    3        12/05/2011   null         1
  1    4        13/05/2011   13/05/2011   2
  3    5        14/05/2011   null         3
  3    6        13/02/2011   13/05/2011   3
  </code></pre>
  
  <p>would give</p>
  
  <pre><code>SiteId Location Deposits Completed Cancelled
  1        a       2          1        0
  2        b       0          1        0
  3        c       0          0        2
  </code></pre>