/*
Copyright 2004 Danal Technology Inc.
dsullivan@danal.com

CTE examples

*/
use [TSQL]
-- Simple CTE, Syntax
--DROP TABLE Employee
--DROP TABLE Shift

-- tables for examples
CREATE TABLE Shift
(
ShiftID INT PRIMARY KEY NONCLUSTERED,
Name CHAR(5)
)
INSERT INTO Shift VALUES (1, 'Day')
INSERT INTO Shift VALUES (2, 'Swing')
INSERT INTO Shift VALUES (3, 'Mid')


CREATE TABLE Employee
(
Title VARCHAR(30),
EmployeeID int IDENTITY PRIMARY KEY NONCLUSTERED,
BaseRate float,
ShiftID int
)

select * from employee
select * from shift

-- Calculates Median BaseRate by shift
WITH Numbered
as
(
SELECT BaseRate, ShiftID, ROW_NUMBER()
OVER (PARTITION BY ShiftID ORDER BY BaseRate)
as RN FROM employee
),
Middle
AS
(
Select ShiftID, COUNT(*)/2 as RN from employee
GROUP BY ShiftID
)
SELECT N.ShiftID, 
N.BaseRate AS Median from Numbered as N
JOIN Middle AS M ON N.ShiftID = M.ShiftID
AND N.RN = M.RN

-- wrap up in function to make psuedo aggregator
CREATE FUNCTION ShiftMedian(@ShiftID as int)
RETURNS FLOAT
BEGIN
DECLARE @median FLOAT;
WITH Numbered
as
(
SELECT BaseRate, ShiftID, ROW_NUMBER()
OVER (PARTITION BY ShiftID ORDER BY BaseRate)
as RN FROM employee
),
Middle
AS
(
Select ShiftID, COUNT(*)/2 as RN from employee
GROUP BY ShiftID
)
SELECT @median = N.baserate from Numbered as N
JOIN Middle AS M ON N.ShiftID = M.ShiftID
AND N.RN = M.RN
WHERE N.ShiftID = @ShiftID
return @median
END

-- use psuedo aggregate with real aggregate
select AVG(BaseRate) as average,
dbo.ShiftMedian(ShiftID) as median
from employee
group by ShiftID




-- can be use with DML
--TRUNCATE TABLE Employee
-- Create employee table for examples
DECLARE @index INT
SET @index = 100
WHILE @index > 0
BEGIN
SET @index = @index - 1;
-- ; required if not first in batch
-- use RAND to create dummy entries
WITH Parts(title, rate, shift) AS
(
-- 8 different titles
SELECT 'Title' + CAST(ROUND(RAND() * 7, 0) AS VARCHAR(30)),
-- base rate up to 100
Rand() * 100 AS Rate,
-- 3 different shifts
Round(Rand() * 2.5 + .5, 0)
)
INSERT INTO Employee SELECT Title, rate, shift from Parts
END



