Post reply

How to get Median length of stay grouped by YearMonth

  • December 1, 2015 at 11:32 am

    #1843287

    Ok, I confirmed that the problem is ties throwing off the relationship between the ascending and descending row numbers.

    It seems that method is just not safe because of the ordering behavior with respect to ties (it could be fixed by throwing in a unique column as a secondary sort, but the method with COUNT is a bit more intuitive for me anyway); I'll have to force my fingers to forget it and use the COUNT-based one instead for pre-2012 medians 🙂

    This should work regardless of ties:

    WITH Medians

    AS ( SELECT Ascending_RN = ROW_NUMBER() OVER ( PARTITION BY YearMonth ORDER BY LOSInMinutes ASC ) ,

    Count_YearMonth = COUNT(*) OVER ( PARTITION BY YearMonth ) ,

    YearMonth ,

    LOSInMinutes

    FROM #TEST

    )

    SELECT AVG(LOSInMinutes * 1.0) ,

    YearMonth

    FROM Medians

    WHERE Medians.Ascending_RN IN ( ( Count_YearMonth + 1 ) / 2,

    ( Count_YearMonth + 2 ) / 2 )

    GROUP BY Medians.YearMonth

    ORDER BY Medians.YearMonth;

    Cheers!

  • December 1, 2015 at 11:38 am

    #1843289

    Wonderful. I can't thank you enough.

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply