How to reduce execution time of median calculations

  • 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

  • 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/

  • 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.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Thank you. I'll work on it

  • 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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • 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