title: SQL Query to calculate median and group by
body: <p>I have following table. </p>
  
  <pre><code>DECLARE @TBL_RESULT Table
  (   
      ID varchar(10),
      CreateDate DateTime,
      PEOPLE_CODE_ID varchar(10), 
      CONVERSION_DATE DateTime,
      CAMPUS varchar(20),
      DAYS_TOOK int   
  );
  </code></pre>
  
  <p>This table has records from January 01,2013 to date of all the leads that were received and converted. </p>
  
  <p>I initially needed to find the Median time it took to convert leads that arrived in last 10 weeks and group them by Campus  I was able to do that Using the SQL Query below</p>
  
  <pre><code>WITH    CTE_RESULT
            AS ( SELECT   *
                 FROM     @TBL_RESULT
                 WHERE    CreateDate &gt; DATEADD(WEEK, -10, GETDATE())
               )
      SELECT  Campus ,
              AVG(DAYS_TOOK) AS MedianTime
      FROM    ( SELECT    CAMPUS ,
                          Days_Took ,
                          ROW_NUMBER() OVER ( PARTITION BY Campus ORDER BY Days_Took ASC ) AS AgeRank ,
                          COUNT(*) OVER ( PARTITION BY CAMPUS ) AS CampusCount
                FROM      CTE_RESULT
              ) x
      WHERE   x.AgeRank IN ( x.CampusCount / 2 + 1, ( x.CampusCount + 1 ) / 2 )
      GROUP BY x.Campus   
  </code></pre>
  
  <p>I now need to plot this trend on a graph i.e. find records the previous 10 weeks buckets and plot the median on a line chart - where each line is one campus. (Grouped by campus)</p>
  
  <p>Is cursor my only option? where I will find the leads of first 10 week starting from Jan 01, do the above SQL query to get median, push it to a temp table and then find the next 10 weeks and so on.</p>
  
  <p>Or is there anything better i can do?</p>