Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- With SourceData AS
- (
- SELECT GroupID, CONVERT( decimal(20, 10), Measure) Measure
- FROM dbo.TEST_MEDIAN
- UNPIVOT(Measure FOR GroupID IN (Computer, Laptop, Mouse)) AS normalized
- )
- SELECT GroupID,
- (Max(Q1LoVal) + Max(Q1HiVal)) / 2 Quartile1,
- (Max(Q2LoVal) + Max(Q2HiVal)) / 2 Median,
- (Max(Q3LoVal) + Max(Q3HiVal)) / 2 Quartile3,
- Min(Measure) Minimum, Max(Measure) Maximum
- FROM (
- SELECT detail.GroupID,
- CASE WHEN RowNum = Q1Lo THEN Measure ELSE NULL END Q1LoVal,
- CASE WHEN RowNum = Q1Hi THEN Measure ELSE NULL END Q1HiVal,
- CASE WHEN RowNum = Q2Lo THEN Measure ELSE NULL END Q2LoVal,
- CASE WHEN RowNum = Q2Hi THEN Measure ELSE NULL END Q2HiVal,
- CASE WHEN RowNum = Q3Lo THEN Measure ELSE NULL END Q3LoVal,
- CASE WHEN RowNum = Q3Hi THEN Measure ELSE NULL END Q3HiVal,
- Measure
- FROM
- (SELECT *, ROW_NUMBER() OVER (PARTITION BY GroupID ORDER BY Measure) RowNum
- FROM SourceData) AS detail
- INNER JOIN (
- SELECT GroupID,
- CEILING(FLOOR(COUNT(*) / 2.0) / 2) Q1Lo,
- CEILING(CEILING(COUNT(*) / 2.0) / 2) Q1Hi,
- (COUNT(*) + 1) / 2 AS Q2Lo,
- (COUNT(*) + 2) / 2 AS Q2Hi,
- (COUNT(*) + 1) - CEILING(CEILING(COUNT(*) / 2.0) / 2) Q3Lo,
- (COUNT(*) + 1) - CEILING(FLOOR(COUNT(*) / 2.0) / 2) Q3Hi,
- COUNT(*) Ct
- FROM SourceData
- GROUP BY GroupID
- HAVING COUNT(*) > 1
- ) AS summary ON detail.GroupID = summary.GroupID
- ) AS combined
- GROUP BY GroupID;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement