SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Calculating the Median in SQL Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

archana23
Yak Posting Veteran

74 Posts

Posted - 12/05/2013 :  13:55:03  Show Profile  Reply with Quote
Hi,

I have table which has columns with Nurse , PatientName, ArrivalDate,DepartDate,DifferenceTime(Min)

so i am calculating median from this table as



SET @Median =
(
SELECT
(
(
SELECT
MAX(DifferenceTime)
FROM
(
SELECT
TOP 50 PERCENT DifferenceTime
FROM
PatTable
ORDER BY
DifferenceTime
)
AS BottomHalf
)
+
(
SELECT
MIN(DifferenceTime)
FROM
(
SELECT
TOP 50 PERCENT DifferenceTime
FROM
PatTable
ORDER BY
DifferenceTime DESC
)
AS TopHalf
)
)
/ 2
)

I am getting median from above expression.

but i need to calculate median per nurse

How can i do this?

can any one please help me on this?

Thanks,

Archana

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/05/2013 :  14:01:28  Show Profile  Reply with Quote
Which version are you using? If its 2012, you can use PERCENTILE_DISC function for this

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

archana23
Yak Posting Veteran

74 Posts

Posted - 12/05/2013 :  14:15:51  Show Profile  Reply with Quote
Thanks visakh16 for your reply.

We are using SQL server 2008 version..

Archana
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 12/05/2013 :  14:47:15  Show Profile  Reply with Quote
try this and see if it is an improvement or not. It will work with 2008:

;with PatTable (nurse, DifferenceTime)
as
(
       select 1, 1 union all
       select 1, 60 union all
       select 1, 61 union all
       select 2, 10 union all
       select 2, 50 union all
       select 2, 60 union all
       select 2, 61
)

select nurse
       ,avg(DifferenceTime) as median 
from   (
       select row_number() over (partition by nurse order by DifferenceTime asc) as asc_srt
	       , row_number() over (partition by nurse order by DifferenceTime desc) as desc_srt
	       , DifferenceTime
              , nurse
	from   PatTable
       ) as t
where asc_srt - desc_srt between -1 and 1
group by nurse

OUTPUT:
nurse       median
----------- -----------
1           60
2           55


EDIT:
added some sample data with output

Be One with the Optimizer
TG

Edited by - TG on 12/05/2013 14:50:42
Go to Top of Page

archana23
Yak Posting Veteran

74 Posts

Posted - 12/05/2013 :  15:35:22  Show Profile  Reply with Quote
Its working.

Thanks TG.

Archana
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 12/05/2013 :  16:20:09  Show Profile  Reply with Quote
you're welcome - thanks for reporting back.

Be One with the Optimizer
TG
Go to Top of Page

archana23
Yak Posting Veteran

74 Posts

Posted - 12/05/2013 :  16:24:39  Show Profile  Reply with Quote
Hi TG,
I have one more question ,I need show my result as like this

Nurse PatientCount AvgOfDifferenceTime MedianOfDifferenceTime

Tina 2 112.20 112.2

Deena 3 162.80 150


So for this first 3 fields i am getting from my query as like this

select Nurse , COUNT(DISTINCT PatientName) As PatientCount,
convert(numeric(6,2),SUM(DifferenceTime) * 1.0/COUNT(DISTINCT PatientName)) AS AvgOfDifferenceTime

and i am getting last field called MedianOfDifferenceTime from your above query

select nurse
,avg(DifferenceTime) as median
from (
select row_number() over (partition by nurse order by DifferenceTime asc) as asc_srt
, row_number() over (partition by nurse order by DifferenceTime desc) as desc_srt
, DifferenceTime
, nurse
from PatTable
) as t
where asc_srt - desc_srt between -1 and 1
group by nurse

Can you please combine these 2 queries to get the result as above??

Sorry to ask you simple questions but i am new to this SQL server :(

Thank you

Edited by - archana23 on 12/05/2013 16:33:58
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 12/05/2013 :  16:44:48  Show Profile  Reply with Quote
here's one way which assumes you have a column called [patientid]

;with PatTable (nurse, DifferenceTime, patientid)
as
(
       select 1, 1, 10 union all
       select 1, 60, 11 union all
       select 1, 61, 12 union all
       select 2, 10, 13 union all
       select 2, 50, 13 union all
       select 2, 60, 14 union all
       select 2, 61, 15
)

select Nurse 
       ,PatientCount = count(distinct patientid) --if a patient can be in multiple rows
       ,PatientCount = count(*)                  --if each row is one different patient
       ,TotalDifferenceTime = sum(DifferenceTime) 
       ,AvgOfDifferenceTime = avg(DifferenceTime) 
       ,MedianOfDifferenceTime = avg(case when asc_srt - desc_srt between -1 and 1 then DifferenceTime else null end)
from   (
       select row_number() over (partition by nurse order by DifferenceTime asc) as asc_srt
	       , row_number() over (partition by nurse order by DifferenceTime desc) as desc_srt
	       , DifferenceTime
              , nurse
              , patientid
	from   PatTable
       ) as t
group by nurse

OUTPUT:
Nurse       PatientCount PatientCount TotalDifferenceTime AvgOfDifferenceTime MedianOfDifferenceTime
----------- ------------ ------------ ------------------- ------------------- ----------------------
1           3            3            122                 40                  60
2           3            4            181                 45                  55


EDIT:
added output (again)

Be One with the Optimizer
TG

Edited by - TG on 12/05/2013 16:45:32
Go to Top of Page

archana23
Yak Posting Veteran

74 Posts

Posted - 12/05/2013 :  17:04:40  Show Profile  Reply with Quote
Perfect !!!! Thank you so much TG. Appreciated for your help...

Archana
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30208 Posts

Posted - 12/06/2013 :  04:45:25  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Beware that double ROW_NUMBER() can return the wrong result!
See http://www.sqltopia.com/?page_id=62



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000