0

Anyone know how I can change the Total Median near bottom to show an average of the median instead? For some reason, the Total Median is always 100. Not sure what I should do.

Thanks in advance for any ideas! Current results also below.

WITH CTE AS (
    SELECT DISTINCT c.CaseID AS CaseID,
        DATEDIFF(d, c.CaseAddDt, coip.DispoDt) AS DaysApart
    ,   DATEPART(month,c.CaseAddDt) AS [Month]
    ,   DATEPART(year,c.CaseAddDt) AS [Year]
    ,   CAST(DATEPART(year,c.CaseAddDt) AS varchar) + '|' + CASE WHEN DATEPART(month,c.CaseAddDt) IN (10,11,12) THEN CAST(DATEPART(month,c.CaseAddDt) AS varchar) ELSE '0' + CAST(DATEPART(month,c.CaseAddDt) AS varchar) END AS Srt
    FROM jw50_Case c
    JOIN jw50_CaseInvPers def ON def.CaseID = c.CaseID
    AND def.InvolveTypeMasterCode = 1
    JOIN
        jw50_CountInvPers coip ON coip.CaseID = c.CaseID
        AND coip.CaseInvPersID = def.CaseInvPersID
        AND coip.DispoCode IN ('CODE','CODE')  
        AND coip.CountNum > 0
    OUTER APPLY (
        SELECT TOP 1 caz.CaseAgencyID
        FROM jw50_CaseAgency caz
        WHERE caz.CaseID = c.CaseID
        AND caz.AgencyCode = 'ABC'
        AND caz.NumberTypeCode IN ('i#','in#')) caz
    WHERE
        EXISTS (SELECT 1 FROM jw50_CaseAttributes ca WHERE ca.CaseID = c.CaseID AND ca.CaseAttributeCode = 'oa7')
    AND caz.CaseAgencyID IS NOT NULL
    AND c.CaseStatusCode <> 'AAA'
    AND c.CaseAddDt BETWEEN '01/01/2017' AND '08/01/2017'
    AND c.CaseAddDt <= coip.DispoDt) 

SELECT a.CaseID,
    a.Month
,   a.Year
,   a.DaysApart
,   a.Srt
,   PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY a.DaysApart) OVER (PARTITION BY a.Month, a.Year) AS MonMedian
,   PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY a.DaysApart) OVER (PARTITION BY 1) AS TotalMedian
FROM CTE a

Results: Results Image

jarlh
  • 35,821
  • 8
  • 33
  • 49
ChrisF
  • 1
  • 1
  • this may help. https://stackoverflow.com/questions/1342898/function-to-calculate-median-in-sql-server – Ferdinand Gaspar Aug 31 '17 at 19:49
  • Just a guess (untested) but I don;t think you need `PARTITION BY 1` just leave the brackets empty as in `PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY a.DaysApart) OVER () AS TotalMedian` – Alan Schofield Aug 31 '17 at 20:43
  • You are correct; no need for the PARTITION BY 1, however, I tried the () change but the results are the same as above. I was told the PARTITION BY 1 would give the grand median for entire data set as it's a single non-changing value. When I run report, the total median value is off by a few days. Some of the CaseIDs have more than one beginning date; I'm guessing that could be what is throwing off the total median. – ChrisF Sep 01 '17 at 19:06

0 Answers0