April 7, 2015 at 9:06 am
I wrote a procedure to calculate median:
============================
ALTER proc [dbo].[sp_CalculateMedianTimeInDepartmentMinutes]
@StartDate date
,@EndDate date
as
--== Check if count is even or odd
declare @modulo int
select @modulo = (Select COUNT(*)%2 from ED_data where AdmitDateTime between @StartDate and @EndDate )
--=== Get Median
Select case @modulo
when 1 -- odd
then --==> pick max value of top 50% ==> it includes one over 50%
(SELECT MAX(TimeInDepartmentMinutes) FROM
(SELECT TOP 50 PERCENT TimeInDepartmentMinutes
FROM ED_data where AdmitDateTime between @StartDate and @EndDate ORDER BY TimeInDepartmentMinutes) AS m1)
else -- even
(
SELECT
(
(
SELECT MAX(TimeInDepartmentMinutes) FROM
(SELECT TOP 50 PERCENT TimeInDepartmentMinutes
FROM ED_data where AdmitDateTime between @StartDate and @EndDate
ORDER BY 1--==>
) AS m1
)
+
(
SELECT MIN(TimeInDepartmentMinutes)
FROM
(SELECT TOP 50 PERCENT TimeInDepartmentMinutes FROM ED_data where AdmitDateTime between @StartDate and @EndDate
ORDER BY 1 DESC --==>
)
AS m2) )
/ 2 AS Median
)
end
===============
My fellow developer is using this code to calcuate a madians in many columns (see below). The problem is that it takes about 2 minutes to execute this code. Is there a way to reduce the time of execution?
I attach also a sample of the view
==============
ALTER PROCEDURE [dbo].[sp_ED_Measures]
@StartDate date,
@EndDate date,
@Hospital varchar(5)
AS
BEGIN
SET NOCOUNT ON;
declare @modulo int
declare @admitted int
declare @LWBS int
declare @Obs int
declare @psyc int
declare @Visits int
select @modulo = (Select COUNT(*)%2 from dbo.vw_ED_Time_Calc where ArrivalDateTime between @StartDate and @EndDate and HospitalName = @Hospital )
select @admitted = (Select COUNT(Visitnumber) from dbo.vw_ED_Time_Calc where Admitted = 'yes' and ArrivalDateTime between @StartDate and @EndDate and HospitalName = @Hospital )
select @LWBS = (Select COUNT(Visitnumber) from dbo.vw_ED_Time_Calc where DispositionString in ('Refuse Treatment - LWOT' ,'Refused Treatment - Elopement', 'Refused Treatment - LWOT',
'Left without treatment') and ArrivalDateTime between @StartDate and @EndDate and HospitalName = @Hospital )
select @Obs = (Select COUNT(Visitnumber) from dbo.vw_ED_Time_Calc where Observation = 'yes' and ArrivalDateTime between @StartDate and @EndDate and HospitalName = @Hospital )
select @psyc = (Select COUNT(Visitnumber) from dbo.vw_ED_Time_Calc where Psyc ='yes' and ArrivalDateTime between @StartDate and @EndDate and HospitalName = @Hospital )
select @Visits = (Select COUNT(Visitnumber) from dbo.vw_ED_Time_Calc where ArrivalDateTime between @StartDate and @EndDate and HospitalName = @Hospital )
Select Distinct @Hospital as HospitalName, @StartDate as StartDate, @EndDate as EndDate, @Visits as #EDVisits,
@admitted as #Admitted, @LWBS as #LWBS, @Obs as #OBS, @psyc as #PSYC,
--=== Get Medians ==========
-- LOS All patients
case @modulo
when 1 then
(SELECT MAX([TimeInDepartmentMinutes]) FROM
(SELECT TOP 50 PERCENT [TimeInDepartmentMinutes] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital
ORDER BY [TimeInDepartmentMinutes]) AS Median)
else -- even
(SELECT
( (SELECT MAX([TimeInDepartmentMinutes]) FROM
(SELECT TOP 50 PERCENT [TimeInDepartmentMinutes]
FROM dbo.vw_ED_Time_Calc where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital
ORDER BY 1--==>
) AS m1)
+ (
SELECT MIN([TimeInDepartmentMinutes]) FROM
(SELECT TOP 50 PERCENT [TimeInDepartmentMinutes] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital
ORDER BY 1 DESC --==>
) AS m2)
) / 2 AS Me)
end AS Median_LOS_All_Patients,
-- LOS All Psyc Patients
case @modulo
when 1 then
(SELECT MAX([TimeInDepartmentMinutes]) FROM
(SELECT TOP 50 PERCENT [TimeInDepartmentMinutes] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and Psyc ='yes' and HospitalName = @Hospital
ORDER BY [TimeInDepartmentMinutes]) AS Median)
else -- even
(SELECT
( (SELECT MAX([TimeInDepartmentMinutes]) FROM
(SELECT TOP 50 PERCENT [TimeInDepartmentMinutes]
FROM dbo.vw_ED_Time_Calc where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and Psyc ='yes' and HospitalName = @Hospital
ORDER BY 1--==>
) AS m1)
+ (
SELECT MIN([TimeInDepartmentMinutes]) FROM
(SELECT TOP 50 PERCENT [TimeInDepartmentMinutes] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and Psyc ='yes' and HospitalName = @Hospital
ORDER BY 1 DESC --==>
) AS m2)
) / 2 AS Me)
end AS Median_LOS_All_Psyc_Patients,
-- LOS All Non-Psyc Patient
case @modulo
when 1 then
(SELECT MAX([TimeInDepartmentMinutes]) FROM
(SELECT TOP 50 PERCENT [TimeInDepartmentMinutes] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and Psyc is null and HospitalName = @Hospital
ORDER BY [TimeInDepartmentMinutes]) AS Median)
else -- even
(SELECT
( (SELECT MAX([TimeInDepartmentMinutes]) FROM
(SELECT TOP 50 PERCENT [TimeInDepartmentMinutes]
FROM dbo.vw_ED_Time_Calc where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and Psyc is null and HospitalName = @Hospital
ORDER BY 1--==>
) AS m1)
+ (
SELECT MIN([TimeInDepartmentMinutes]) FROM
(SELECT TOP 50 PERCENT [TimeInDepartmentMinutes] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and Psyc is null and HospitalName = @Hospital
ORDER BY 1 DESC --==>
) AS m2)
) / 2 AS Me)
end AS Median_LOS_All_NonPsyc_Patients,
-- LOS TR Psyc Patients
case @modulo
when 1 then
(SELECT MAX([TimeInDepartmentMinutes]) FROM
(SELECT TOP 50 PERCENT [TimeInDepartmentMinutes] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and Psyc ='yes' and DispositionString like 'Discharg%'and HospitalName = @Hospital
ORDER BY [TimeInDepartmentMinutes]) AS Median)
else -- even
(SELECT
( (SELECT MAX([TimeInDepartmentMinutes]) FROM
(SELECT TOP 50 PERCENT [TimeInDepartmentMinutes]
FROM dbo.vw_ED_Time_Calc where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and Psyc ='yes' and DispositionString like 'Discharg%'and HospitalName = @Hospital
ORDER BY 1--==>
) AS m1)
+ (
SELECT MIN([TimeInDepartmentMinutes]) FROM
(SELECT TOP 50 PERCENT [TimeInDepartmentMinutes] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and Psyc ='yes'and DispositionString like 'Discharg%'and HospitalName = @Hospital
ORDER BY 1 DESC --==>
) AS m2)
) / 2 AS Me)
end AS Median_LOS_TR_Psyc_Patients,
-- LOS All Non-Psyc Patients
case @modulo
when 1 then
(SELECT MAX([TimeInDepartmentMinutes]) FROM
(SELECT TOP 50 PERCENT [TimeInDepartmentMinutes] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and Psyc is null and DispositionString like 'Discharg%' and HospitalName = @Hospital
ORDER BY [TimeInDepartmentMinutes]) AS Median)
else -- even
(SELECT
( (SELECT MAX([TimeInDepartmentMinutes]) FROM
(SELECT TOP 50 PERCENT [TimeInDepartmentMinutes]
FROM dbo.vw_ED_Time_Calc where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and Psyc is null and DispositionString like 'Discharg%'and HospitalName = @Hospital
ORDER BY 1--==>
) AS m1)
+ (
SELECT MIN([TimeInDepartmentMinutes]) FROM
(SELECT TOP 50 PERCENT [TimeInDepartmentMinutes] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and Psyc is null and DispositionString like 'Discharg%' and HospitalName = @Hospital
ORDER BY 1 DESC --==>
) AS m2)
) / 2 AS Me)
end AS Median_LOS_TR_NonPsyc_Patients,
------------ ADMITTED MEDIANS-------------
--ArrivaltoTriageAdm
case @modulo
when 1 then
(SELECT MAX([ArrivalToTriageTime]) FROM
(SELECT TOP 50 PERCENT [ArrivalToTriageTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and Admitted = 'Yes' and ArrivalDateTime <= TriageDateTime
ORDER BY [ArrivalToTriageTime]) AS Median)
else -- even
(SELECT
( (SELECT MAX([ArrivalToTriageTime]) FROM
(SELECT TOP 50 PERCENT [ArrivalToTriageTime]
FROM dbo.vw_ED_Time_Calc where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and Admitted = 'Yes' and ArrivalDateTime <= TriageDateTime
ORDER BY 1--==>
) AS m1)
+ (
SELECT MIN([ArrivalToTriageTime]) FROM
(SELECT TOP 50 PERCENT [ArrivalToTriageTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and Admitted = 'Yes' and ArrivalDateTime <= TriageDateTime
ORDER BY 1 DESC --==>
) AS m2)
) / 2 AS Me)
end AS Median_ArrivaltoTriage_Adm,
--Triage to Room for Admitted Patients
case @modulo
when 1 then
(SELECT MAX([TriagetoRoomTime]) FROM
(SELECT TOP 50 PERCENT [TriagetoRoomTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and Admitted = 'Yes' and TriageDateTime<=InitialTransferToRoomDateTime
ORDER BY [TriagetoRoomTime]) AS Median)
else -- even
(SELECT
( (SELECT MAX([TriagetoRoomTime]) FROM
(SELECT TOP 50 PERCENT [TriagetoRoomTime]
FROM dbo.vw_ED_Time_Calc where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and Admitted = 'Yes' and TriageDateTime<=InitialTransferToRoomDateTime
ORDER BY 1--==>
) AS m1)
+ (
SELECT MIN([TriagetoRoomTime]) FROM
(SELECT TOP 50 PERCENT [TriagetoRoomTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and Admitted = 'Yes' and TriageDateTime<=InitialTransferToRoomDateTime
ORDER BY 1 DESC --==>
) AS m2)
) / 2 AS Me)
end AS Median_TriagetoRoom_Adm,
--Room to Doctor for ED Admitted patients
case @modulo
when 1 then
(SELECT MAX([RoomtoDoctorTime]) FROM
(SELECT TOP 50 PERCENT [RoomtoDoctorTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and Admitted = 'Yes' and InitialTransferToRoomDateTime <= InitialEncounterWithDoctorDateTime
ORDER BY [RoomtoDoctorTime]) AS Median)
else -- even
(SELECT
( (SELECT MAX([RoomtoDoctorTime]) FROM
(SELECT TOP 50 PERCENT [RoomtoDoctorTime]
FROM dbo.vw_ED_Time_Calc where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and Admitted = 'Yes' and InitialTransferToRoomDateTime <= InitialEncounterWithDoctorDateTime
ORDER BY 1--==>
) AS m1)
+ (
SELECT MIN([RoomtoDoctorTime]) FROM
(SELECT TOP 50 PERCENT [RoomtoDoctorTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and Admitted = 'Yes' and InitialTransferToRoomDateTime <= InitialEncounterWithDoctorDateTime
ORDER BY 1 DESC --==>
) AS m2)
) / 2 AS Me)
end AS Median_RoomtoDoctor_Adm,
--Doctor to Disposition for ED Admitted patients
case @modulo
when 1 then
(SELECT MAX([DoctortoDispositionTime]) FROM
(SELECT TOP 50 PERCENT [DoctortoDispositionTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and Admitted = 'Yes' and InitialEncounterWithDoctorDateTime <=FlaggedDispositionDateTime
ORDER BY [DoctortoDispositionTime]) AS Median)
else -- even
(SELECT
( (SELECT MAX(DoctortoDispositionTime) FROM
(SELECT TOP 50 PERCENT [DoctortoDispositionTime]
FROM dbo.vw_ED_Time_Calc where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and Admitted = 'Yes' and InitialEncounterWithDoctorDateTime <=FlaggedDispositionDateTime
ORDER BY 1--==>
) AS m1)
+ (
SELECT MIN([DoctortoDispositionTime]) FROM
(SELECT TOP 50 PERCENT [DoctortoDispositionTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and Admitted = 'Yes' and InitialEncounterWithDoctorDateTime <=FlaggedDispositionDateTime
ORDER BY 1 DESC --==>
) AS m2)
) / 2 AS Me)
end AS Median_DoctortoDisposition_Adm,
-- Disposition to Departure for ED Admitted patients
case @modulo
when 1 then
(SELECT MAX([DispositionDecisiontoDepartureTime]) FROM
(SELECT TOP 50 PERCENT [DispositionDecisiontoDepartureTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and Admitted = 'Yes' and FlaggedDispositionDateTime <= DisposeDateTime
ORDER BY [DispositionDecisiontoDepartureTime]) AS Median)
else -- even
(SELECT
( (SELECT MAX([DispositionDecisiontoDepartureTime]) FROM
(SELECT TOP 50 PERCENT [DispositionDecisiontoDepartureTime]
FROM dbo.vw_ED_Time_Calc where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and Admitted = 'Yes' and FlaggedDispositionDateTime <= DisposeDateTime
ORDER BY 1--==>
) AS m1)
+ (
SELECT MIN([DispositionDecisiontoDepartureTime]) FROM
(SELECT TOP 50 PERCENT [DispositionDecisiontoDepartureTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and Admitted = 'Yes' and FlaggedDispositionDateTime <= DisposeDateTime
ORDER BY 1 DESC --==>
) AS m2)
) / 2 AS Me)
end AS Median_DispositiontoDepart_Adm_ED2b,
--Arrival to Physician admitted
case @modulo
when 1 then
(SELECT MAX([ArrivalToDoctorTime]) FROM
(SELECT TOP 50 PERCENT [ArrivalToDoctorTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and Admitted = 'Yes'
ORDER BY [ArrivalToDoctorTime]) AS Median)
else -- even
(SELECT
( (SELECT MAX([ArrivalToDoctorTime]) FROM
(SELECT TOP 50 PERCENT [ArrivalToDoctorTime]
FROM dbo.vw_ED_Time_Calc where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and Admitted = 'Yes'
ORDER BY 1--==>
) AS m1)
+ (
SELECT MIN([ArrivalToDoctorTime]) FROM
(SELECT TOP 50 PERCENT [ArrivalToDoctorTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and Admitted = 'Yes'
ORDER BY 1 DESC --==>
) AS m2)
) / 2 AS Me)
end AS Median_ArrivaltoDoctor_Adm,
--Arrival to Decisionto Admit ED Admitted patients
case @modulo
when 1 then
(SELECT MAX([ArrivalToDispositionDecisionTime]) FROM
(SELECT TOP 50 PERCENT [ArrivalToDispositionDecisionTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and Admitted = 'Yes'
ORDER BY [ArrivalToDispositionDecisionTime]) AS Median)
else -- even
(SELECT
( (SELECT MAX([ArrivalToDispositionDecisionTime]) FROM
(SELECT TOP 50 PERCENT [ArrivalToDispositionDecisionTime]
FROM dbo.vw_ED_Time_Calc where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and Admitted = 'Yes'
ORDER BY 1--==>
) AS m1)
+ (
SELECT MIN([ArrivalToDispositionDecisionTime]) FROM
(SELECT TOP 50 PERCENT [ArrivalToDispositionDecisionTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and Admitted = 'Yes'
ORDER BY 1 DESC --==>
) AS m2)
) / 2 AS Me)
end AS Median_ArrivaltoDecisiontoAdmit_Adm_ED1b_minus_ED2b,
--Admit Decision to departure ED Admitted Psyc patients
case @modulo
when 1 then
(SELECT MAX([DispositionDecisiontoDepartureTime]) FROM
(SELECT TOP 50 PERCENT [DispositionDecisiontoDepartureTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Adm%' and Psyc = 'yes' and FlaggedDispositionDateTime <= DisposeDateTime
ORDER BY [DispositionDecisiontoDepartureTime]) AS Median)
else -- even
(SELECT
( (SELECT MAX([DispositionDecisiontoDepartureTime]) FROM
(SELECT TOP 50 PERCENT [DispositionDecisiontoDepartureTime]
FROM dbo.vw_ED_Time_Calc where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Adm%' and Psyc = 'yes'and FlaggedDispositionDateTime <= DisposeDateTime
ORDER BY 1--==>
) AS m1)
+ (
SELECT MIN([DispositionDecisiontoDepartureTime]) FROM
(SELECT TOP 50 PERCENT [DispositionDecisiontoDepartureTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Adm%' and Psyc = 'yes'and FlaggedDispositionDateTime <= DisposeDateTime
ORDER BY 1 DESC --==>
) AS m2)
) / 2 AS Me)
end AS Median_AdmitDecisiontoDepart_Adm_Psyc_ED2c,
--Arrival to Depart ED Admitted patients
case @modulo
when 1 -- odd
then --==> pick max value of top 50% ==> it includes one over 50%
(SELECT MAX([ArrivalToDepartDateTime]) FROM
(SELECT TOP 50 PERCENT [ArrivalToDepartDateTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and Admitted = 'Yes'
ORDER BY [ArrivalToDepartDateTime]) AS Median)
else -- even
(SELECT
( (SELECT MAX([ArrivalToDepartDateTime]) FROM
(SELECT TOP 50 PERCENT [ArrivalToDepartDateTime]
FROM dbo.vw_ED_Time_Calc where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and Admitted = 'Yes'
ORDER BY 1--==>
) AS m1)
+ (
SELECT MIN([ArrivalToDepartDateTime]) FROM
(SELECT TOP 50 PERCENT [ArrivalToDepartDateTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and Admitted = 'Yes'
ORDER BY 1 DESC --==>
) AS m2)
) / 2 AS Me)
end AS Median_ArrivaltoDepart_Adm_ED1b,
--CalculateArrivaltoDepartAdmPsyc
case @modulo
when 1 -- odd
then --==> pick max value of top 50% ==> it includes one over 50%
(SELECT MAX([ArrivalToDepartDateTime]) FROM
(SELECT TOP 50 PERCENT [ArrivalToDepartDateTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Adm%' and Psyc = 'yes'
ORDER BY [ArrivalToDepartDateTime]) AS Median)
else -- even
(SELECT
( (SELECT MAX([ArrivalToDepartDateTime]) FROM
(SELECT TOP 50 PERCENT [ArrivalToDepartDateTime]
FROM dbo.vw_ED_Time_Calc where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Adm%' and Psyc = 'yes'
ORDER BY 1--==>
) AS m1)
+ (
SELECT MIN([ArrivalToDepartDateTime]) FROM
(SELECT TOP 50 PERCENT [ArrivalToDepartDateTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Adm%' and Psyc = 'yes'
ORDER BY 1 DESC --==>
) AS m2)
) / 2 AS Me)
end AS Median_ArrivaltoDepart_Adm_Psyc_ED1c,
----TREAT & RELEASE MEDIANS-----
--CalculateArrivaltoTriageTR
case @modulo
when 1 then
(SELECT MAX([ArrivalToTriageTime]) FROM
(SELECT TOP 50 PERCENT [ArrivalToTriageTime]
FROM dbo.vw_ED_Time_Calc where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Discharge%' and ArrivalDateTime<=TriageDateTime
ORDER BY ArrivalToTriageTime) AS Median)
else -- even
(SELECT
( (SELECT MAX(ArrivalToTriageTime) FROM
(SELECT TOP 50 PERCENT ArrivalToTriageTime
FROM dbo.vw_ED_Time_Calc where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Discharge%'and ArrivalDateTime<=TriageDateTime
ORDER BY 1--==>
) AS m1)
+ (
SELECT MIN(ArrivalToTriageTime) FROM
(SELECT TOP 50 PERCENT ArrivalToTriageTime FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Discharge%' and ArrivalDateTime<=TriageDateTime
ORDER BY 1 DESC --==>
) AS m2)
) / 2 AS Me)
end AS Median_ArrivaltoTriage_TR,
--CalculateTriagetoRoomTR
case @modulo
when 1 then
(SELECT MAX([TriagetoRoomTime]) FROM
(SELECT TOP 50 PERCENT [TriagetoRoomTime]
FROM dbo.vw_ED_Time_Calc where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Discharge%' and TriageDateTime<=InitialTransferToRoomDateTime
ORDER BY [TriagetoRoomTime]) AS Median)
else -- even
(SELECT
( (SELECT MAX([TriagetoRoomTime]) FROM
(SELECT TOP 50 PERCENT [TriagetoRoomTime]
FROM dbo.vw_ED_Time_Calc where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Discharge%'and TriageDateTime<=InitialTransferToRoomDateTime
ORDER BY 1--==>
) AS m1)
+ (
SELECT MIN([TriagetoRoomTime]) FROM
(SELECT TOP 50 PERCENT [TriagetoRoomTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Discharge%' and TriageDateTime<=InitialTransferToRoomDateTime
ORDER BY 1 DESC --==>
) AS m2)
) / 2 AS Me)
end AS Median_TriagetoRoom_TR,
--Room to Doctor for ED T/R patients
case @modulo
when 1 then
(SELECT MAX([RoomtoDoctorTime]) FROM
(SELECT TOP 50 PERCENT [RoomtoDoctorTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Discharge%'and InitialTransferToRoomDateTime <= InitialEncounterWithDoctorDateTime
ORDER BY [RoomtoDoctorTime]) AS Median)
else -- even
(SELECT
( (SELECT MAX([RoomtoDoctorTime]) FROM
(SELECT TOP 50 PERCENT [RoomtoDoctorTime]
FROM dbo.vw_ED_Time_Calc where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Discharge%' and InitialTransferToRoomDateTime <= InitialEncounterWithDoctorDateTime
ORDER BY 1--==>
) AS m1)
+ (
SELECT MIN([RoomtoDoctorTime]) FROM
(SELECT TOP 50 PERCENT [RoomtoDoctorTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Discharge%' and InitialTransferToRoomDateTime <= InitialEncounterWithDoctorDateTime
ORDER BY 1 DESC --==>
) AS m2)
) / 2 AS Me)
end AS Median_RoomtoDoctor_TR,
--Doctor to Disposition for ED T/R patients
case @modulo
when 1 then
(SELECT MAX([DoctortoDispositionTime]) FROM
(SELECT TOP 50 PERCENT [DoctortoDispositionTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Discharge%' and InitialEncounterWithDoctorDateTime<=FlaggedDispositionDateTime
ORDER BY [DoctortoDispositionTime]) AS Median)
else -- even
(SELECT
( (SELECT MAX(DoctortoDispositionTime) FROM
(SELECT TOP 50 PERCENT [DoctortoDispositionTime]
FROM dbo.vw_ED_Time_Calc where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Discharge%' and InitialEncounterWithDoctorDateTime<=FlaggedDispositionDateTime
ORDER BY 1--==>
) AS m1)
+ (
SELECT MIN([DoctortoDispositionTime]) FROM
(SELECT TOP 50 PERCENT [DoctortoDispositionTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Discharge%' and InitialEncounterWithDoctorDateTime<=FlaggedDispositionDateTime
ORDER BY 1 DESC --==>
) AS m2)
) / 2 AS Me)
end AS Median_DoctortoDisposition_TR,
-- Disposition to Departure for ED T/R patients
case @modulo
when 1 then
(SELECT MAX([DispositionDecisiontoDepartureTime]) FROM
(SELECT TOP 50 PERCENT [DispositionDecisiontoDepartureTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Discharge%' and FlaggedDispositionDateTime<=DisposeDateTime
ORDER BY [DispositionDecisiontoDepartureTime]) AS Median)
else -- even
(SELECT
( (SELECT MAX([DispositionDecisiontoDepartureTime]) FROM
(SELECT TOP 50 PERCENT [DispositionDecisiontoDepartureTime]
FROM dbo.vw_ED_Time_Calc where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Discharge%' and FlaggedDispositionDateTime<=DisposeDateTime
ORDER BY 1--==>
) AS m1)
+ (
SELECT MIN([DispositionDecisiontoDepartureTime]) FROM
(SELECT TOP 50 PERCENT [DispositionDecisiontoDepartureTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Discharge%' and FlaggedDispositionDateTime<=DisposeDateTime
ORDER BY 1 DESC --==>
) AS m2)
) / 2 AS Me)
end AS Median_DispositiontoDepart_TR,
--ArrivaltoPhysicianTR_OP20
case @modulo
when 1 then
(SELECT MAX([ArrivalToDoctorTime]) FROM
(SELECT TOP 50 PERCENT [ArrivalToDoctorTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Discharge%' and ArrivalDateTime<=InitialEncounterWithDoctorDateTime
ORDER BY [ArrivalToDoctorTime]) AS Median)
else -- even
(SELECT
( (SELECT MAX([ArrivalToDoctorTime]) FROM
(SELECT TOP 50 PERCENT [ArrivalToDoctorTime]
FROM dbo.vw_ED_Time_Calc where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Discharge%'and ArrivalDateTime<=InitialEncounterWithDoctorDateTime
ORDER BY 1--==>
) AS m1)
+ (
SELECT MIN([ArrivalToDoctorTime]) FROM
(SELECT TOP 50 PERCENT [ArrivalToDoctorTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Discharge%' and ArrivalDateTime<=InitialEncounterWithDoctorDateTime
ORDER BY 1 DESC --==>
) AS m2)
) / 2 AS Me)
end AS Median_ArrivaltoDoctor_TR_OP20,
--ArrivaltoPhysicianTRPsyc
case @modulo
when 1 then
(SELECT MAX([ArrivalToDoctorTime]) FROM
(SELECT TOP 50 PERCENT [ArrivalToDoctorTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Discharge%' and Psyc ='yes'and ArrivalDateTime<=InitialEncounterWithDoctorDateTime
ORDER BY [ArrivalToDoctorTime]) AS Median)
else -- even
(SELECT
( (SELECT MAX([ArrivalToDoctorTime]) FROM
(SELECT TOP 50 PERCENT [ArrivalToDoctorTime]
FROM dbo.vw_ED_Time_Calc where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Discharge%'and Psyc ='yes'and ArrivalDateTime<=InitialEncounterWithDoctorDateTime
ORDER BY 1--==>
) AS m1)
+ (
SELECT MIN([ArrivalToDoctorTime]) FROM
(SELECT TOP 50 PERCENT [ArrivalToDoctorTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Discharge%' and Psyc ='yes'and ArrivalDateTime<=InitialEncounterWithDoctorDateTime
ORDER BY 1 DESC --==>
) AS m2)
) / 2 AS Me)
end AS Median_ArrivaltoDoctor_TR_Psyc,
--ArrivaltoPhysicianTRNon-Psyc
case @modulo
when 1 then
(SELECT MAX([ArrivalToDoctorTime]) FROM
(SELECT TOP 50 PERCENT [ArrivalToDoctorTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Discharge%' and Psyc is null and ArrivalDateTime<=InitialEncounterWithDoctorDateTime
ORDER BY [ArrivalToDoctorTime]) AS Median)
else -- even
(SELECT
( (SELECT MAX([ArrivalToDoctorTime]) FROM
(SELECT TOP 50 PERCENT [ArrivalToDoctorTime]
FROM dbo.vw_ED_Time_Calc where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Discharge%'and Psyc is null and ArrivalDateTime<=InitialEncounterWithDoctorDateTime
ORDER BY 1--==>
) AS m1)
+ (
SELECT MIN([ArrivalToDoctorTime]) FROM
(SELECT TOP 50 PERCENT [ArrivalToDoctorTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Discharge%' and Psyc is null and ArrivalDateTime<=InitialEncounterWithDoctorDateTime
ORDER BY 1 DESC --==>
) AS m2)
) / 2 AS Me)
end AS Median_ArrivaltoDoctor_TR_NotPsyc,
--ArrivaltoDepartTR
case @modulo
when 1 then
(SELECT MAX([ArrivalToDepartDateTime]) FROM
(SELECT TOP 50 PERCENT [ArrivalToDepartDateTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Discharge%' --and Psyc = 'Yes'
ORDER BY [ArrivalToDepartDateTime]) AS Median)
else -- even
(SELECT
( (SELECT MAX([ArrivalToDepartDateTime]) FROM
(SELECT TOP 50 PERCENT [ArrivalToDepartDateTime]
FROM dbo.vw_ED_Time_Calc where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Discharge%'--and Psyc = 'Yes'
ORDER BY 1--==>
) AS m1)
+ (
SELECT MIN([ArrivalToDepartDateTime]) FROM
(SELECT TOP 50 PERCENT [ArrivalToDepartDateTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Discharge%' --and Psyc = 'Yes'
ORDER BY 1 DESC --==>
) AS m2)
) / 2 AS Me)
end AS Median_ArrivaltoDepart_TR_OP18b,
--eArrivaltoDepartTRPsyc
case @modulo
when 1 then
(SELECT MAX([ArrivalToDepartDateTime]) FROM
(SELECT TOP 50 PERCENT [ArrivalToDepartDateTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Discharge%' and Psyc = 'Yes'
ORDER BY [ArrivalToDepartDateTime]) AS Median)
else -- even
(SELECT
( (SELECT MAX([ArrivalToDepartDateTime]) FROM
(SELECT TOP 50 PERCENT [ArrivalToDepartDateTime]
FROM dbo.vw_ED_Time_Calc where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Discharge%'and Psyc = 'Yes'
ORDER BY 1--==>
) AS m1)
+ (
SELECT MIN([ArrivalToDepartDateTime]) FROM
(SELECT TOP 50 PERCENT [ArrivalToDepartDateTime] FROM dbo.vw_ED_Time_Calc
where ArrivalDateTime >= @StartDate and ArrivalDateTime <= @EndDate and HospitalName = @Hospital and DispositionString like 'Discharge%' and Psyc = 'Yes'
ORDER BY 1 DESC --==>
) AS m2)
) / 2 AS Me)
end AS Median_ArrivaltoDepart_TR_Psyc_OP18c
from dbo.vw_ED_Time_Calc
group by HospitalName
END
April 7, 2015 at 10:43 am
I don't have time to re-write this, but...
1. The 6 selects to load the variables can be one select using case statements
SELECT
@modulo = COUNT(*) % 2,
@admitted = SUM(CASE
WHEN Admitted = 'yes'
THEN 1
ELSE 0
END),
@LWBS = SUM(CASE
WHEN DispositionString IN ('Refuse Treatment - LWOT', 'Refused Treatment - Elopement', 'Refused Treatment - LWOT', 'Left without treatment')
THEN 1
ELSE 0
END),
@Obs = SUM(CASE
WHEN Observation = 'yes'
THEN 1
ELSE 0
END),
@psyc = SUM(CASE
WHEN Psyc = 'yes'
THEN 1
ELSE 0
END),
@Visits = COUNT(Visitnumber)
FROM dbo.vw_ED_Time_Calc
WHERE ArrivalDateTime BETWEEN @StartDate AND @EndDate
AND HospitalName = @Hospital
2. DISTINCT is not needed when you are using the GROUP BY clause. Remove DISTINCT and see what happens.
3. It looks like your developer Googled median and saw this article:
http://blogs.lessthandot.com/index.php/datamgmt/datadesign/calculating-mean-median-and-mode-with-sq/
He probably should read this article, which describes the use of CTE's, OVER, and ROW_ NUMBER.
http://sqlmag.com/t-sql/calculating-median-gets-simpler-sql-server-2005
4. If anything in #3 doesn;t help, then remove the sub selects from inline code to sub-selects in the FROM. Use left joins.
Your select then becomes:
SELECT
CASE WHEN @Modulo = 1 THEN OddAllPatients.Median ELSE EvenAllPatients.Median END,
CASE WHEN @Modulo = 1 THEN OddAllPsycPatients.Median ELSE EvenAllPsycPatients.Median END,
CASE WHEN @Modulo = 1 THEN OddAllNONPsycPatients.Median ELSE EvenAllNONPsycPatients.Median END,
And so forth...
I am really busy today, I may be able to attack this later this evening.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 7, 2015 at 1:14 pm
Dwain Camps wrote a great article awhile back: The Performance of the T-SQL Window Functions[/url]
The article is very 2012-centric but discusses how to calculate a median in 2008 as well.
-- Itzik Ben-Gan 2001
April 7, 2015 at 1:48 pm
Thank you. I'll work on it
April 7, 2015 at 7:04 pm
Alan.B (4/7/2015)
Dwain Camps wrote a great article awhile back: The Performance of the T-SQL Window Functions[/url]The article is very 2012-centric but discusses how to calculate a median in 2008 as well.
Thanks for the plug Alan. Glad to see someone reads the stuff I write.
The article he links to is followed up by SQL MVP Aaron Bertrand and he probably does a better job with some of the SQL 2012 solutions.
Best approaches for grouped median
If you are working with a heap (no indexing), I have come up with a faster solution than my previous one (the fastest solution for a heap in Aaron's article) that I plan to post to my blog today. You can press the BLOG button at the bottom of my signature in about 24 hours to see that.
Edit: Note that Aaron in his article calls mine a SQL 2005 solution, which is not quite accurate. CROSS APPLY VALUES as I used it is only valid in SQL 2008, however the equivalent SELECT/UNION ALL SELECT will work in SQL 2005.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 9, 2015 at 3:57 pm
Off the top of my head, I would use a temp table here, something like below. I don't have time to finish all the median values in the SELECT, but I think I did enough as examples. I can't test the query or check the query plan, but I believe it will be reasonably efficient since it should avoid any actual sorting on the median computations. I don't have time now to re-write how the median rows are computed, although I can think of a couple of better ways -- I'm hoping the code below is good enough compared to the run time you are currently getting.
ALTER PROCEDURE [dbo].[sp_ED_Measures]
@StartDate date,
@EndDate date,
@Hospital varchar(5)
AS
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#vw_ED_Time_Calc') IS NOT NULL
DROP TABLE #vw_ED_Time_Calc
CREATE TABLE #vw_ED_Time_Calc (
--ident int IDENTITY(1, 1) NOT NULL,
Visitnumber int NOT NULL,
TimeInDepartmentMinutes int NOT NULL,
Admitted_Matched tinyint NOT NULL,
LWBS_Matched tinyint NOT NULL,
Observation_Matched tinyint NOT NULL,
Psyc_Matched tinyint NOT NULL
)
CREATE CLUSTERED INDEX #vw_ED_Time_Calc__CL ON #vw_ED_Time_Calc ( TimeInDepartmentMinutes );
declare @modulo int
declare @admitted int
declare @LWBS int
declare @Obs int
declare @psyc int
declare @Visits int
INSERT INTO #vw_ED_Time_Calc
SELECT
Visitnumber,
TimeInDepartmentMinutes,
CASE WHEN Admitted = 'yes' THEN 1 ELSE 0 END AS Admitted_Matched,
CASE WHEN DispositionString IN (
'Left without treatment',
'Refused Treatment - Elopement',
'Refuse Treatment - LWOT',
'Refused Treatment - LWOT' )
THEN 1 ELSE 0 END AS LWBS_Matched,
CASE WHEN Observation = 'yes' THEN 1 ELSE 0 END AS Observation_Matched,
CASE WHEN Psyc = 'yes' THEN 1 ELSE 0 END AS Psyc_Matched
FROM dbo.vw_ED_Time_Calc
WHERE
ArrivalDateTime >= @StartDate and
ArrivalDateTime < DATEADD(DAY, 1, @EndDate) and
HospitalName = @Hospital
ORDER BY
TimeInDepartmentMinutes
SET @Visits = @@ROWCOUNT
SET @modulo = @Visits % 2
SELECT
@admitted = SUM(Admitted_Matched),
@LWBS = SUM(LWBS_Matched),
@Obs = SUM(Observation_Matched),
@psyc = SUM(Psyc_Matched)
FROM #vw_ED_Time_Calc
Select @Hospital as HospitalName, @StartDate as StartDate, @EndDate as EndDate, @Visits as #EDVisits,
@admitted as #Admitted, @LWBS as #LWBS, @Obs as #OBS, @psyc as #PSYC,
--=== Get Medians ==========
-- LOS All patients
case @modulo
when 1 then -- odd
(SELECT MAX([TimeInDepartmentMinutes]) FROM
(SELECT TOP 50 PERCENT [TimeInDepartmentMinutes] FROM #vw_ED_Time_Calc
ORDER BY [TimeInDepartmentMinutes]) AS Median)
else -- even
(SELECT
( (SELECT MAX([TimeInDepartmentMinutes]) FROM
(SELECT TOP 50 PERCENT [TimeInDepartmentMinutes]
FROM #vw_ED_Time_Calc
ORDER BY 1--==>
) AS m1)
+ (
SELECT MIN([TimeInDepartmentMinutes]) FROM
(SELECT TOP 50 PERCENT [TimeInDepartmentMinutes] FROM #vw_ED_Time_Calc
ORDER BY 1 DESC --==>
) AS m2)
) / 2 AS Me)
end AS Median_LOS_All_Patients,
-- LOS All Psyc Patients
case @modulo
when 1 then
(SELECT MAX([TimeInDepartmentMinutes]) FROM
(SELECT TOP 50 PERCENT [TimeInDepartmentMinutes] FROM #vw_ED_Time_Calc
WHERE Psyc_Matched = 1
ORDER BY [TimeInDepartmentMinutes]) AS Median)
else -- even
(SELECT
( (SELECT MAX([TimeInDepartmentMinutes]) FROM
(SELECT TOP 50 PERCENT [TimeInDepartmentMinutes]
FROM #vw_ED_Time_Calc
WHERE Psyc_Matched = 1
ORDER BY 1--==>
) AS m1)
+ (
SELECT MIN([TimeInDepartmentMinutes]) FROM
(SELECT TOP 50 PERCENT [TimeInDepartmentMinutes] FROM #vw_ED_Time_Calc
WHERE Psyc_Matched = 1
ORDER BY 1 DESC --==>
) AS m2)
) / 2 AS Me)
end AS Median_LOS_All_Psyc_Patients,
-- LOS All Non-Psyc Patient
case @modulo
when 1 then
(SELECT MAX([TimeInDepartmentMinutes]) FROM
(SELECT TOP 50 PERCENT [TimeInDepartmentMinutes] FROM #vw_ED_Time_Calc
WHERE Psyc_Matched = 0
ORDER BY [TimeInDepartmentMinutes]) AS Median)
else -- even
(SELECT
( (SELECT MAX([TimeInDepartmentMinutes]) FROM
(SELECT TOP 50 PERCENT [TimeInDepartmentMinutes]
FROM #vw_ED_Time_Calc
WHERE Psyc_Matched = 0
ORDER BY 1--==>
) AS m1)
+ (
SELECT MIN([TimeInDepartmentMinutes]) FROM
(SELECT TOP 50 PERCENT [TimeInDepartmentMinutes] FROM #vw_ED_Time_Calc
WHERE Psyc_Matched = 0
ORDER BY 1 DESC --==>
) AS m2)
) / 2 AS Me)
end AS Median_LOS_All_NonPsyc_Patients,
--...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply