SQLTeam.com | Weblogs | Forums

Finding The Median


#1

Hi Everyone,

I am able to pull the average numbers using the datediff function but since SQL 2008 has no median internal function
this has got me confused
I read a few examples on calculating the median but since I need to use datediff and an alias to produce the numbers I am unable to use the ROW_NUMBER partition examples.

SELECT alosmlos.CUSTOMER_ID, DATEDIFF(DD, alosmlos.ADMIT_DATE, alosmlos.TERMINATION_DATE)+1 AS ALOS

FROM
alosmlos

WHERE
(alosmlos.TYPE IN ('F','D')) AND
(alosmlos.CUSTOMER_TYPE = 'A') AND
(alosmlos.CUSTOMER_CLASS IN('HO','CC','PH')) AND
(alosmlos.DATASET_NAME = 'Business Name') AND
(alosmlos.TERMINATION_DATE BETWEEN CONVERT(DATETIME, '2015-07-01', 120) AND CONVERT(DATETIME, '2015-07-31', 120))

Any clues would be appreciated

Thanks,

M


#2

here


#3

#4

Worked out great used a #temp table to retrieve the values
thanks such a great read!