Calculate Median with 2 group by columns

[bupah]

I am trying to calculate the median for values on a daily basis for multiple categories. I am able to do it for either a certain date for all categories or for a date range for one category, but cannot get it to break them both down. I am using the Count and RowNum calculations in my query to calculate the median values.
I have thousands of values per day for each category, so even creating a temp table by each category is not something I want to have to do if possible.
Sample Data
Category Date Value
1 5/23/2010 3
1 5/23/2010 6
1 5/23/2010 5
1 5/22/2010 8
1 5/21/2010 2
2 5/23/2010 5
2 5/22/2010 9
2 5/22/2010 4
2 5/21/2010 4

I would like the output grouped by Category and Date. Something like this.
Category Date Median
1 5/23 5
1 5/22 8
1 5/21 2
2 5/23 5
2 5/22 6.5
2 5/21 4

Here is a sample query that works for each separate one, but won’t produce the output when both are included.

SELECT X.Category, X.Date, avg(X.Value) FROM
(SELECT A.Category, D.Date, p.Value,
           ROW_NUMBER() OVER(PARTITION BY A.Category ORDER BY p.Value) AS RowNum,
           COUNT(*) OVER(PARTITION BY A.Category) AS Cnt
FROM TABLE1 AS A INNER JOIN
     TABLE2 AS D ON A.ID = D.ID
WHERE D.Date between '20100510' and '20100520' 
AND A.OBJECTID = 135
and A.Category in(‘XXX’ , ‘YYY’)) AS X
WHERE RowNum IN ((Cnt+1)/2, (Cnt+2)/2)
group by Category, Date
 

If I remove Category from the select and group by statements and explicitly pick one value in the where clause it works fine and vice versa for Date, but when both are included it does not work. The query returns data, but does not include all the permutations grouped by both values.

Any ideas?

[deepuv04]

@bupah
hi,
use the date column for partitioning
try this hope will work

SELECT X.Category, X.Date, avg(X.Value) FROM
(    SELECT    A.Category, D.Date, p.Value,
            ROW_NUMBER() OVER(PARTITION BY A.Category,D.DATE ORDER BY p.Value) AS RowNum,
            COUNT(*) OVER(PARTITION BY A.Category,D.DATE) AS Cnt
    FROM    TABLE1 AS A INNER JOIN
            TABLE2 AS D ON A.ID = D.ID
    WHERE    D.Date between '20100510' and '20100520' 
            AND A.OBJECTID = 135
            and A.Category in(‘XXX’ , ‘YYY’)
) AS X
WHERE RowNum IN ((Cnt+1)/2, (Cnt+2)/2)
group by Category, Date
 

remove where clause for date range and category, or change it according