/******************************************************
 *
 * Name:         group-by-with-rollup.sql
 *     
 * Design Phase:
 *     Author:   John Miner
 *     Date:     12-21-2012
 *     Blog:     www.craftydba.com
 *
 *     Purpose:  Create a series of articles on
 *               database developer (70-433) topics.
 *
 *     Topic:    Talk about group by with roll up.
 *
 ******************************************************/

-- Use latest Adventure Works DB
USE [AdventureWorks2012_CS]
GO


--
-- For each department, job title, get min, max, avg pay,
-- full time employees.  Optionally add sub & grand totals.
--
; 
WITH Cte_LastRateChange(BusinessEntityID, RateChangeDate) AS
(
    SELECT PAY1.BusinessEntityID, MAX(PAY1.RateChangeDate) 
    FROM [HumanResources].[EmployeePayHistory] PAY1
    GROUP BY PAY1.BusinessEntityID
),
Cte_CurrentPayRate(BusinessEntityID, Rate) AS
(
    SELECT PAY2.BusinessEntityID, PAY2.Rate
    FROM [HumanResources].[EmployeePayHistory] PAY2
    INNER JOIN Cte_LastRateChange PAY3 
	    ON PAY2.BusinessEntityID = PAY3.BusinessEntityID 
	    AND PAY2.RateChangeDate = PAY3.RateChangeDate
)
SELECT 
      DEP1.GroupName
	, EMP1.JobTitle
	, GROUPING(EMP1.JobTitle) AS SumInfo
	, FORMAT(MIN(PAY4.Rate * 2080), 'C', 'en-us') AS MinPay
	, FORMAT(MAX(PAY4.Rate * 2080), 'C', 'en-us') AS MaxPay
	, FORMAT(AVG(PAY4.Rate * 2080), 'C', 'en-us') AS AvgPay
	, COUNT(*) AS FteNo
FROM 
    [HumanResources].[Employee] AS EMP1 
    LEFT JOIN [HumanResources].[EmployeeDepartmentHistory] AS EDH1 
	    ON EMP1.BusinessEntityID = EDH1.BusinessEntityID
    LEFT JOIN [HumanResources].[Department] AS DEP1 
	    ON EDH1.DepartmentID = DEP1.DepartmentID
    LEFT JOIN Cte_CurrentPayRate AS PAY4 
	    ON EMP1.BusinessEntityID = PAY4.BusinessEntityID
WHERE 
    EDH1.EndDate IS NULL
GROUP BY
      DEP1.GroupName
	, EMP1.JobTitle

/*
ORDER BY 
      DEP1.GroupName
	, EMP1.JobTitle
*/

WITH ROLLUP

-- GROUP BY ROLLUP (DEP1.GroupName, EMP1.JobTitle)