IF OBJECT_ID('tempdb..#OvertimeBudget2013') IS NOT NULL

DROP TABLE #OvertimeBudget2013

CREATE TABLE #OvertimeBudget2013

(

[ID] INT IDENTITY(1,1) NOT NULL

,[Org Level] [nvarchar](50) NULL

,[Overtime Code] [nchar](10) NULL

,[JanuaryBudget] [money] NULL

,[FebruaryBudget] [money] NULL

,[MarchBudget] [money] NULL

,[AprilBudget] [money] NULL

,[MayBudget] [money] NULL

,[JuneBudget] [money] NULL

,[JulyBudget] [money] NULL

,[AugustBudget] [money] NULL

,[SeptemberBudget] [money] NULL

,[OctoberBudget] [money] NULL

,[NovemeberBudget] [money] NULL

,[DecemberBudget] [money] NULL

,[ActualBudget] [money] NULL

,PRIMARY KEY CLUSTERED (ID)

)

INSERT Into #OvertimeBudget2013

([Org Level],[Overtime Code],[JanuaryBudget],[FebruaryBudget],[MarchBudget])

SELECT 'WSIDOE','5204','1579588.23','1578296.23','1964371.53' UNION ALL

SELECT 'WSIOTH','5204','10979.65','11212.00','15404.53' UNION ALL

SELECT 'WSIWMA','5204','121070.56','121070.05','149569.32' UNION ALL

SELECT 'WSIUNY','5204','1287.29','1287.68','1609.60' UNION ALL

SELECT 'WSIZHR','5204','553.85','553.85','692.31' UNION ALL

SELECT 'WSIZSS','5204','96.15','96.15','120.19'

SELECT * FROM #OvertimeBudget2013

IF OBJECT_ID('tempdb..#EmpPers') IS NOT NULL

DROP TABLE #EmpPers

CREATE TABLE #EmpPers (

[ID] INT IDENTITY(1,1) NOT NULL

,[pehPErcontrol] DATETIME NOT NULL

,[eepNameLast] [nvarchar](50) NULL

,[CodDesc] [nvarchar](50) NULL

,[eepNameFirst] [nvarchar](50) NULL

,[eepNameMiddle] [nvarchar](50) NULL

,[eepNameSuffix] [nvarchar](50) NULL

,[Org Level] [nvarchar](50) NULL

,[Overtime Code] [nchar](10) NULL

,[JanuaryActual] [money] NULL

,[FebruaryActual] [money] NULL

,[MarchActual] [money] NULL

,PRIMARY KEY (ID))

;WITH cteEmpData

([pehPErcontrol],[eepNameLast],[CodDesc],[eepNameFirst],[eepNameMiddle],[eepNameSuffix],[Org Level],[Overtime Code],[JanuaryActual],[FebruaryActual],[MarchActual])

AS

(

SELECT GETDATE(),'Bush','1','George','W','Jr.','5204','WSIDOE','157588.23','158296.23','196471.53' UNION ALL

SELECT GETDATE(),'Jones','2','George','T','Sr.','5204','WSIUNY','15758.23','18296.23','19641.53' UNION ALL

SELECT GETDATE(),'Smith','3','April','Mae','','5204','WSIZSS','1578.23','1582.23','19641.53'

)

INSERT INTO #EmpPers

([pehPErcontrol],[eepNameLast],[CodDesc],[eepNameFirst],[eepNameMiddle],[eepNameSuffix],[Org Level],[Overtime Code],[JanuaryActual],[FebruaryActual],[MarchActual])

SELECT

CAST(LEFT(pehPErcontrol,8) AS DATETIME) AS [Period Control Date]

,RTRIM(eepNameLast) + --isnull(' '+rtrim(CodDesc), '') +

', ' + RTRIM(eepNameFirst) + ' ' + COALESCE(SUBSTRING(eepNameMiddle,1,1) + '.','') AS Name

,eepNameLast AS [Last Name]

,ISNULL(eepNameSuffix,'') AS [Suffix]

,eepNameFirst AS [First Name]

,ISNULL(eepNameMiddle,'') AS [Middle Name]

--new

,[overtime code] AS [Overtime Code]

,[Org Level] AS [Org Level]

,[JanuaryActual]

,[FebruaryActual]

,[MarchActual]

FROM

cteEmpData

SELECT * FROM #EmpPers AS ep

SELECT

r.[Org Level]

,ISNULL(r.JanuaryBudget,0) AS JanuaryBudget

,ISNULL(r.JanuaryActual,0) AS JanuaryActual

,ISNULL(SUM(r.JanuaryActual-r.JanuaryBudget) OVER (PARTITION BY r.[Org Level]),0) AS [+/-]

,ISNULL(r.FebruaryBudget,0) AS FebruaryBudget

,ISNULL(r.FebruaryActual,0) AS FebruaryActual

,ISNULL(SUM(r.FebruaryActual-r.FebruaryBudget) OVER (PARTITION BY r.[Org Level]),0) AS [+/-]

,ISNULL(r.MarchBudget,0) AS MarchBudget

,ISNULL(r.MarchActual,0) AS MarchActual

,ISNULL(SUM(r.MarchActual-r.MarchBudget) OVER (PARTITION BY r.[Org Level]),0) AS [+/-]

FROM

(

SELECT

bud.[Org Level]

,SUM(CAST(bud.[JanuaryBudget] AS DECIMAL(9,2))) OVER (PARTITION BY bud.[Org Level]) AS [JanuaryBudget]

,SUM(CAST(bud.[FebruaryBudget] AS DECIMAL(9,2))) OVER (PARTITION BY bud.[Org Level]) AS [FebruaryBudget]

,SUM(CAST(bud.[MarchBudget] AS DECIMAL(9,2))) OVER (PARTITION BY bud.[Org Level]) AS [MarchBudget]

,SUM(CAST(ep.[JanuaryActual] AS DECIMAL(9,2))) OVER (PARTITION BY ep.[Org Level]) AS [JanuaryActual]

,SUM(CAST(ep.[FebruaryActual] AS DECIMAL(9,2))) OVER (PARTITION BY ep.[Org Level]) AS [FebruaryActual]

,SUM(CAST(ep.[MarchActual] AS DECIMAL(9,2))) OVER (PARTITION BY ep.[Org Level]) AS [MarchActual]

FROM

#OvertimeBudget2013 AS bud

LEFT OUTER JOIN

#EmpPers AS ep

ON ep.[Org Level] = bud.[Org Level]

AND ep.[Overtime Code] = bud.[Overtime Code]

) r