WITH SourceData AS (

    --Table of sets over which we want to produce multiple quartiles.
    SELECT sales.duration AS Measure,
           CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, sales.Start_Time) - 5, sales.Start_Time)), 101) As GroupID
	FROM Workflow_Data AS sales
	WHERE sales.client_mnemonic = 'XXXX_XX'and
	   sales.Definition in 
	   (
	   'Meds Rec - Discharge',
	   'Meds Rec - Transfer',
	   'Meds Rec - Admission'
	   ) and datepart(dw, sales.Start_Time) not in (1,7)

)
--Aggregate into a single record for each group, using MAX to select the non-null 
--detail value for each column.
SELECT GroupID,
    (Max(Q1NextVal) - MAX(Q1Val)) * Max(Q1Frac) + Max(Q1Val) Quartile1,
    (Max(MidVal1) + Max(MidVal2)) / 2 Median,
    (Max(Q3NextVal) - MAX(Q3Val)) * Max(Q3Frac) + Max(Q3Val) Quartile3,
        Max(measure) maxMeasure,
        Min(measure) minMeasure

--Save into a separate table if desired.
--INTO dbo.PreCalculatedQuartiles
FROM (
    --Expose the detail values for only the records at the index values 
    --generated by the summary subquery. All other values are left as NULL.
    SELECT detail.GroupID,
        CASE WHEN RowNum = Q1Idx THEN Measure ELSE NULL END Q1Val,
        CASE WHEN RowNum = Q1Idx + 1 THEN Measure ELSE NULL END Q1NextVal,
        CASE WHEN RowNum = Q3Idx THEN Measure ELSE NULL END Q3Val,
        CASE WHEN RowNum = Q3Idx + 1 THEN Measure ELSE NULL END Q3NextVal,
        Q1Frac,
        Q3Frac,
        CASE WHEN RowNum = MidPt1 THEN Measure ELSE NULL END MidVal1,
        CASE WHEN RowNum = MidPt2 THEN Measure ELSE NULL END MidVal2,
        Detail.measure
    FROM
        --Calculate a row number sorted by measure for each group.
        (SELECT *,  ROW_NUMBER() OVER (PARTITION BY GroupID ORDER BY Measure) RowNum
        FROM SourceData) AS detail

    INNER JOIN (
        --Summarize to find index numbers and fractions we need to use to locate 
        --the values at the quartile points.
        SELECT GroupID, 
            FLOOR((COUNT(*) + 3) / 4.0) Q1Idx,
            ((COUNT(*) + 3) / 4.0) - FLOOR((COUNT(*) + 3) / 4.0) Q1Frac,
            (COUNT(*) + 1) / 2 AS MidPt1,
            (COUNT(*) + 2) / 2 AS Midpt2,
            FLOOR((COUNT(*) * 3 + 1) / 4.0) Q3Idx,
            ((COUNT(*) * 3 + 1) / 4.0) - FLOOR((COUNT(*) * 3 + 1) / 4.0) Q3Frac
        FROM SourceData
        GROUP BY GroupID
        HAVING COUNT(*) > 1
    
    ) AS summary ON detail.GroupID  = summary.GroupID 

) AS combined
GROUP BY GroupID;