title: Finding Median of Dates in SQL Server 2008
body: <p>I am looking of a way to take the MEDIAN of a bunch of start and end dates (LOTS AND LOTS of dates). However, it would be specific to various "invoice numbers." See sample data below.</p>
  
  <pre><code> invoice_no   invoice start date      invoice end date
   4006            11/14/2001               12/15/2004
   20071  11/29/2001               02/01/2003
   19893  11/30/2001               12/02/2001
   19894  11/30/2001               12/04/2001
   004             10/22/2002               10/31/2002
  </code></pre>
  
  <p>Find median between start and end date.</p>
  
  <p>The median would just be whatever is between start and end date for that specific invoice_no. However, in an effort to filter data as much as possible. I realized I can also do WHERE STATUS &lt;> 'REJECTED' and it should also help keep a lot of uncessary dates out. Also, I only wanted to filter between a few months so I added the BETWEEN DATETIME in as well. </p>
  
  <p>Code so far:
         <pre>  WITH
      tmp AS
      (
          SELECT invoice_no,
                  invoice_start_date, invoice_end_date, check_date, status_code,
                  cast(count(*) OVER (PARTITION BY invoice_no) as float) AS total,
                  row_number() OVER (PARTITION BY invoice_no ORDER BY invoice_start_date, invoice_end_date, check_date) AS rn
          FROM    INVOICE_HEADER INNER JOIN
                        INVOICE_HEADER_CUSTOM ON INVOICE_HEADER.invoice_id = INVOICE_HEADER_CUSTOM.invoice_id</p>
  
  <pre><code>                  WHERE status_code &lt;&gt; 'REJECTED' AND 
  </code></pre>
  
  <p>Check_Date BETWEEN CONVERT(DATETIME, '2014-12-01 00:00:00', 102) AND CONVERT(DATETIME, '2014-12-31 00:00:00', 102)
      )</p>
  
  <code>    SELECT *
      FROM tmp
      WHERE (total / 2.0 - 1) &lt; rn and rn &lt; (total / 2.0 + 1) 
  </code></pre>