Query for SQL 2012 : Percentile_DISC function for median; sum, min, max, avg queries grouped by month and year. I will also post a version for sql versions lower than 2012 that cannot use percentile. Notes: 1) "DailyDiff" is a computed column using "PMCounts-AMCounts/2" to obtain the GateCount Daily Difference. 2) DB isn't partitioned so I had t…

AVG-MED-MIN-MAX-MONTH-YEAR

SELECT
        Year([Date]) GC_Year, 
          Month([Date]) GC_Month, 
          DATENAME(month,[Date]) Month_Name,
          SUM(DailyDiff) AS GateCounts_MonthlyTotal,
          MIN(DailyDiff) AS GateCounts_MIN,
          MAX(DailyDiff) AS GateCounts_MAX, 
          AVG(DailyDiff) AS  GateCounts_AVG,
          GateCounts_MED=MAX(Median)

        FROM
        (
           SELECT [Date], DailyDiff,
            Median=PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY DailyDiff) OVER (PARTITION BY CAST(MONTH([Date]) AS VARCHAR(2)) + CAST(YEAR([Date]) AS VARCHAR(4)))
           FROM GateCounts 
        ) a

    WHERE [date]>=cast('2015-08-24 00:00:00' as datetime)
    AND [date]<cast('2015-12-30 00:00:00' as datetime)

    GROUP BY  Year([Date]), Month([Date]), DATENAME(month,[Date])

    ORDER BY GC_Year DESC, GC_Month DESC;