USE AdventureWorks2012
GO

/* Drop the table if it exists so we can recreate it at different sizes */
IF OBJECT_ID(N'HumanResources.EmployeePayHistoryEnlarged') IS NOT NULL
BEGIN
	DROP TABLE [HumanResources].[EmployeePayHistoryEnlarged];
END
GO

/* Create the table using the script from the original EmployeePayHistory table */
CREATE TABLE [HumanResources].[EmployeePayHistoryEnlarged](
	[BusinessEntityID] [int] NOT NULL,
	[RateChangeDate] [datetime] NOT NULL,
	[Rate] [money] NOT NULL,
	[PayFrequency] [tinyint] NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_EmployeePayHistoryEnlarged_BusinessEntityID_RateChangeDate] PRIMARY KEY CLUSTERED 
(	[BusinessEntityID] ASC,
	[RateChangeDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

/* Add the constraints so it mimics the EmployeePayHistory table in function */
ALTER TABLE [HumanResources].[EmployeePayHistoryEnlarged] 
ADD CONSTRAINT [DF_EmployeePayHistoryEnlarged_ModifiedDate]
DEFAULT (getdate()) FOR [ModifiedDate]

ALTER TABLE [HumanResources].[EmployeePayHistoryEnlarged] 
WITH CHECK 
ADD CONSTRAINT [FK_EmployeePayHistoryEnlarged_Employee_BusinessEntityID] 
FOREIGN KEY([BusinessEntityID])
REFERENCES [HumanResources].[Employee] ([BusinessEntityID])

ALTER TABLE [HumanResources].[EmployeePayHistoryEnlarged] 
CHECK CONSTRAINT [FK_EmployeePayHistoryEnlarged_Employee_BusinessEntityID]

ALTER TABLE [HumanResources].[EmployeePayHistoryEnlarged] 
WITH CHECK 
ADD CONSTRAINT [CK_EmployeePayHistoryEnlarged_PayFrequency] 
CHECK (([PayFrequency]=(2) OR [PayFrequency]=(1)))

ALTER TABLE [HumanResources].[EmployeePayHistoryEnlarged] 
CHECK CONSTRAINT [CK_EmployeePayHistoryEnlarged_PayFrequency]

ALTER TABLE [HumanResources].[EmployeePayHistoryEnlarged] 
WITH CHECK 
ADD CONSTRAINT [CK_EmployeePayHistoryEnlarged_Rate] 
CHECK (([Rate]>=(6.50) AND [Rate]<=(200.00)))
ALTER TABLE [HumanResources].[EmployeePayHistoryEnlarged] 
CHECK CONSTRAINT [CK_EmployeePayHistoryEnlarged_Rate]

GO

/* First insert the standard rows duplicating EmployeePayHistory */
INSERT INTO HumanResources.EmployeePayHistoryEnlarged 
	(BusinessEntityID, RateChangeDate, Rate, PayFrequency, ModifiedDate)
SELECT BusinessEntityID, RateChangeDate, Rate, PayFrequency, ModifiedDate
FROM HumanResources.EmployeePayHistory;
GO

/* Second insert to scale the table size as needed.
	312 rows		-		Comment out
	11,916 rows		-		Change the GO to be GO 40
	116,316 rows	-		Change the GO to be GO 400
	1,160,316 rows	-		Change the GO to be GO 4000  */
INSERT INTO HumanResources.EmployeePayHistoryEnlarged 
	(BusinessEntityID, RateChangeDate, Rate, PayFrequency, ModifiedDate)
SELECT 
	BusinessEntityID, RateChangeDate, Rate, PayFrequency, ModifiedDate
FROM
(
	SELECT BusinessEntityID, RateChangeDate, Rate, PayFrequency, ModifiedDate,
			ROW_NUMBER() OVER (PARTITION BY BusinessEntityID ORDER BY NEWID()) AS Ranking
	FROM
	(
		SELECT 
			BusinessEntityID, DATEADD(dd, number, RateChangeDate) AS RateChangeDate,
			Rate, PayFrequency, ModifiedDate
		FROM(
			SELECT BusinessEntityID, RateChangeDate, Rate, PayFrequency, ModifiedDate
			FROM
				(
				SELECT BusinessEntityID, RateChangeDate, Rate, PayFrequency, ModifiedDate,
					ROW_NUMBER() OVER (PARTITION BY BusinessEntityID ORDER BY RateChangeDate DESC) AS RevisionID
				FROM HumanResources.EmployeePayHistoryEnlarged
				) AS tab
			WHERE tab.RevisionID = 1
			) AS tab2
		CROSS APPLY 
			(
			SELECT number
			FROM master.dbo.spt_values
			WHERE type = N'P'
			  AND number < 60
			) AS tab3
		-- Check to ensure that we don't duplicate the same date for a BusinessEntityID
		WHERE NOT EXISTS (	SELECT 1 
							FROM HumanResources.EmployeePayHistoryEnlarged AS hist
							WHERE tab2.BusinessEntityID = hist.BusinessEntityID
							  AND DATEADD(dd, tab3.number, tab2.RateChangeDate) = hist.RateChangeDate )
	) AS tab4
) AS tab5
WHERE Ranking = 1;
GO 40


/* Drop the problem view before creating it */
IF OBJECT_ID(N'EmployeeCurrentPayData') IS NOT NULL
BEGIN
	DROP VIEW dbo.EmployeeCurrentPayData;
END
GO

/* Create the problem view for the tests */
CREATE VIEW dbo.EmployeeCurrentPayData
AS
WITH PayChanges AS(
SELECT
	per.BusinessEntityID, 
	per.FirstName, 
	per.LastName, 
	per.MiddleName, 
	emp.JobTitle, 
	pay.Rate, 
	pay.PayFrequency,
	ROW_NUMBER() OVER (PARTITION BY per.BusinessEntityID, JobTitle, PayFrequency ORDER BY pay.RateChangeDate DESC) AS RevisionID
FROM Person.Person AS per
INNER JOIN HumanResources.Employee AS emp 
	ON per.BusinessEntityID = emp.BusinessEntityID
LEFT JOIN HumanResources.EmployeePayHistoryEnlarged AS pay
	ON emp.BusinessEntityID = pay.BusinessEntityID)
SELECT
	BusinessEntityID, 
	FirstName, 
	LastName, 
	MiddleName, 
	JobTitle, 
	Rate, 
	PayFrequency
FROM PayChanges
WHERE RevisionID = 1;
GO

/* Drop the stored procedure using the view before creating it */
IF OBJECT_ID(N'GetEmployeeCurrentPay_View') IS NOT NULL
BEGIN
	DROP PROCEDURE dbo.GetEmployeeCurrentPay_View;
END
GO

/* Create the stored procedure that uses the view to tes the problem */
CREATE PROCEDURE dbo.GetEmployeeCurrentPay_View (@BusinessEntityID INT)
AS
BEGIN
	SELECT * 
	FROM dbo.EmployeeCurrentPayData
	WHERE BusinessEntityID = @BusinessEntityID;
END
GO

/* Drop the stored procedure that eliminates the view before creating */
IF OBJECT_ID(N'GetEmployeeCurrentPay_NoView') IS NOT NULL
BEGIN
	DROP PROCEDURE dbo.GetEmployeeCurrentPay_NoView;
END
GO

/* Create the stored procedure that eliminates the view for testing */
CREATE PROCEDURE dbo.GetEmployeeCurrentPay_NoView (@BusinessEntityID INT)
AS
BEGIN
	WITH PayChanges AS(
	SELECT
		per.BusinessEntityID, 
		per.FirstName, 
		per.LastName, 
		per.MiddleName, 
		emp.JobTitle, 
		pay.Rate, 
		pay.PayFrequency,
		ROW_NUMBER() OVER (PARTITION BY per.BusinessEntityID, JobTitle, PayFrequency ORDER BY pay.RateChangeDate DESC) AS RevisionID
	FROM Person.Person AS per
	INNER JOIN HumanResources.Employee AS emp 
		ON per.BusinessEntityID = emp.BusinessEntityID
	LEFT JOIN HumanResources.EmployeePayHistoryEnlarged AS pay
		ON emp.BusinessEntityID = pay.BusinessEntityID
	WHERE per.BusinessEntityID = @BusinessEntityID)
	SELECT
		BusinessEntityID, 
		FirstName, 
		LastName, 
		MiddleName, 
		JobTitle, 
		Rate, 
		PayFrequency
	FROM PayChanges
	WHERE RevisionID = 1;
END

GO

-- Run the tests of both procedures
SET STATISTICS IO ON
SET STATISTICS TIME ON

DECLARE @BusinessEntityID INT = 250;

EXECUTE dbo.GetEmployeeCurrentPay_View @BusinessEntityID;
EXECUTE dbo.GetEmployeeCurrentPay_NoView @BusinessEntityID;


SET STATISTICS IO OFF
SET STATISTICS TIME OFF