Finding the Median value from a table, Group By Date SQLServer
<p>I have a complicated problem I am trying to solve. Please bear with me and feel free to ask any questions. I am quite new to SQL and having difficulty with this...</p>

<p>I need to count the median of a group of values. Now the values are not given in a table. The values are derived from a table based on hourly occurrences grouped by date. </p>

<p>Here's the sample table from where data is pooled.</p>

<pre><code>   CREATE TABLE Table22(
   Request_Number BIGINT  NOT NULL
  ,Request_Received_Date DATETIME  NOT NULL
);
INSERT INTO Table22(Request_Number,Request_Received_Date) VALUES (2016311446,'8/9/16 9:56');
INSERT INTO Table22(Request_Number,Request_Received_Date) VALUES (20163612157,'9/6/16 9:17');
INSERT INTO Table22(Request_Number,Request_Received_Date) VALUES (2016384250,'9/12/16 14:52');
INSERT INTO Table22(Request_Number,Request_Received_Date) VALUES (20162920101,'4/19/16 8:11');
INSERT INTO Table22(Request_Number,Request_Received_Date) VALUES (2016418170,'10/6/16 12:28');
INSERT INTO Table22(Request_Number,Request_Received_Date) VALUES (2016392953,'9/6/16 12:39');
INSERT INTO Table22(Request_Number,Request_Received_Date) VALUES (20164123416,'10/6/16 15:05');
INSERT INTO Table22(Request_Number,Request_Received_Date) VALUES (2016335972,'8/9/16 7:49');
INSERT INTO Table22(Request_Number,Request_Received_Date) VALUES (20162622951,'9/6/16 9:57');
INSERT INTO Table22(Request_Number,Request_Received_Date) VALUES (20163913504,'9/6/16 9:47');
INSERT INTO Table22(Request_Number,Request_Received_Date) VALUES (20163211326,'9/6/16 12:38');
INSERT INTO Table22(Request_Number,Request_Received_Date) VALUES (20163610132,'8/30/16 16:34');
INSERT INTO Table22(Request_Number,Request_Received_Date) VALUES (20164119560,'10/6/16 15:53');
INSERT INTO Table22(Request_Number,Request_Received_Date) VALUES (2016334416,'8/10/16 11:06');
INSERT INTO Table22(Request_Number,Request_Received_Date) VALUES (20164320028,'10/6/16 15:27');
INSERT INTO Table22(Request_Number,Request_Received_Date) VALUES (20163515193,'8/24/16 19:50');
INSERT INTO Table22(Request_Number,Request_Received_Date) VALUES (2016159834,'4/19/16 13:21');
INSERT INTO Table22(Request_Number,Request_Received_Date) VALUES (2016178443,'4/19/16 13:05');
</code></pre>

<p>The Table has 2 columns: Request_Number and Request_Received_Date. 
Request_Number is not unique and is kind of irrelevant. I am looking for how many requests are received for a particular date and hourly within that date (24 hours). Every time there is an entry for a date, that is counted as one occurrence (TicketCount). I can use the COUNT statements to count * from Request_received_date and group by date and hour. </p>

<p>I did just that and created a temporary table within my script: </p>

<pre><code>CREATE TABLE #z (ForDate date, OnHour int, TicketCount int)
INSERT INTO #z (ForDate, OnHour, TicketCount)           
SELECT  CAST(Request_received_date as DATE) AS 'ForDate',
                DATEPART(hh, request_received_date) AS 'OnHour', 
                COUNT(*) AS TicketCount /*Hourly Ticket Count Column*/
                FROM Table22
                GROUP BY CAST(request_received_date as DATE), DATEPART(hh, request_received_date)
                ORDER BY ForDate Desc, OnHour ASC

SELECT * FROM #z order by ForDate Desc, OnHour ASC
</code></pre>

<p>Now I am having the hardest time finding the median value of count per day. I have tried many different formula for median calculation and was able to make most them work. Many different examples of median calculation can be found here
<a href="https://sqlperformance.com/2012/08/t-sql-queries/median" rel="nofollow noreferrer">https://sqlperformance.com/2012/08/t-sql-queries/median</a> </p>

<p>I like this piece of script to find median. The script for finding median is simple. But it finds median for all the values of Request_Received_Date. I am unable to find a way to use the group by date clause in here. </p>

<pre><code>DECLARE @Median DECIMAL (12,2); 

SELECT @Median = (
    (SELECT MAX(TicketCount) FROM 
    (SELECT TOP 50 PERCENT TicketCount FROM #z ORDER BY TicketCount) AS BottomHalf)
    +
    (SELECT MIN(TicketCount) FROM 
    (SELECT TOP 50 PERCENT TicketCount FROM #z ORDER BY TicketCount DESC) AS TopHalf))/2; 

SELECT @Median  
</code></pre>

<p>Any help will be really appreciated.</p>

<p>The expected result is something like this: </p>

<pre><code>ForDate   Median
10/6/2016   2
9/12/2016   1
9/6/2016    2.5
8/30/2016   1
8/24/2016   1
8/10/2016   1
8/9/2016    1
4/19/2016   1.5
</code></pre>