IF EXISTS
(
	SELECT * FROM tempdb.dbo.sysobjects
	WHERE id = object_id(N'[tempdb].dbo.[##tmpNames]')
)
DROP TABLE ##tmpNames ;
GO
CREATE TABLE ##tmpNames
(
 pkAutoNum INT IDENTITY(1, 1)
 ,sFName VARCHAR(20)
 ,sLName VARCHAR(20)
 ,sClient CHAR(1)
)
GO
IF EXISTS
(
	SELECT * FROM tempdb.dbo.sysobjects
	WHERE id = object_id(N'[tempdb].dbo.[##tmpDetails]')
)
DROP TABLE ##tmpDetails ;
GO
CREATE TABLE ##tmpDetails
(
 pkAutoNum INT IDENTITY(1, 1)
 ,iFKNames TINYINT
 ,sDept VARCHAR(10)
 ,iHours TINYINT
 ,sDate VARCHAR(30)
 ,dtDate SMALLDATETIME
)
GO
IF EXISTS
(
	SELECT * FROM tempdb.dbo.sysobjects
	WHERE id = object_id(N'[tempdb].dbo.[#tmpData]')
)
DROP TABLE #tmpData ;
GO
CREATE TABLE #tmpData
(
 ID INT IDENTITY(1,1)
 ,SomeData VARCHAR(100)
 ,SomeDate DATETIME
)
GO
INSERT INTO ##tmpNames
(
 sFName
 ,sLName
 ,sClient
 )
SELECT 
sFName
,sLName
,sClient = LEFT(B.sLName,1)
FROM
(
	SELECT sFName = 'Fred' UNION
	SELECT sFName = 'Wilma' UNION
	SELECT sFName = 'Barney' UNION
	SELECT sFName = 'Betty' UNION
	SELECT sFName = 'Al' UNION
	SELECT sFName = 'Peggy' UNION
	SELECT sFName = 'Frasier' UNION
	SELECT sFName = 'Niles' UNION
	SELECT sFName = 'Homer' UNION
	SELECT sFName = 'Marge' UNION
	SELECT sFName = 'Hawkeye' UNION
	SELECT sFName = 'Trapper' UNION
	SELECT sFName = 'Sam' UNION
	SELECT sFName = 'Diane' UNION
	SELECT sFName = 'Rebecca'
) A
	CROSS JOIN
(
	SELECT sLName = 'Flintstone' UNION
	SELECT sLName = 'Rubble' UNION
	SELECT sLName = 'Bundy' UNION
	SELECT sLName = 'Branch' UNION
	SELECT sLName = 'Simpson' UNION
	SELECT sLName = 'Jackson' UNION
	SELECT sLName = 'Franks' UNION
	SELECT sLName = 'Chambers' UNION
	SELECT sLName = 'Smith' UNION
	SELECT sLName = 'Jones'
) B
GO
DECLARE
@RowCount INT
,@RowString VARCHAR(10)
,@Random INT
,@Upper INT
,@Lower INT
,@InsertDate DATETIME
;
SELECT
@Lower = -730
,@Upper = -1
,@RowCount = 0
;
--WHILE @RowCount < 1000000  --One Million Rows
WHILE @RowCount < 100000  --One Hundred Thousand Rows
--WHILE @RowCount < 10000  --Ten Thousand Rows
BEGIN
	SET @RowString = CAST(@RowCount AS VARCHAR(10))
	;
	SET @Random = ROUND(((@Upper - @Lower - 1) * RAND() + @Lower), 0)
	;
	SET @InsertDate = DATEADD(dd, @Random, GETDATE())
	;
	INSERT INTO #tmpData
	(
		SomeData
		,SomeDate
	)
	VALUES
	(
		REPLICATE('0', 10 - DATALENGTH(@RowString)) + @RowString
		,DATEADD(dd, 1, @InsertDate)
	)
	;
	SET @RowCount = @RowCount + 1
	;
END
--SELECT TOP 1000 * FROM #tmpData ;
--SELECT * FROM ##tmpNames ;
GO

--CREATES 1,500,000 Rows in Details For 100,000 Data Rows
--CREATES 150,000 Rows in Details For 10,000 Data Rows
INSERT INTO ##tmpDetails
(
	iFKNames
	,sDept
	,iHours
	,sDate
	,dtDate
)
SELECT 
--TOP 1000
iFKName = N.pkAutoNum
,sDept =
	CASE
		WHEN RIGHT(D.ID,1) IN (1,3,5,7) THEN 'Finance'
		WHEN RIGHT(D.ID,1) IN (2,4,6,8) THEN 'Management'
		ELSE 'IT'
	END
,iHours = SUBSTRING(D.SomeData,8,1)
,sDate = CONVERT(VARCHAR(30),D.SomeDate,113)
,dtDate = D.SomeDate
FROM
##tmpNames N
INNER JOIN
#tmpData D
	ON RIGHT(N.pkAutoNum,1) = RIGHT(D.ID,1)
;
GO
DROP TABLE #tmpData ;
GO


---------------------------------------------------------------
DBCC FREEPROCCACHE ;  --Clear Procedure Cache
GO
DBCC FREESYSTEMCACHE('ALL') ;  --Clear Plan Cache
GO
SET STATISTICS IO ON ;
GO
SET STATISTICS TIME ON ;
GO

SELECT TOP 1 MaxDate_OrderBy = dtDate FROM ##tmpDetails ORDER BY dtDate DESC
;
SELECT MaxDate_Max = MAX(dtDate) FROM ##tmpDetails
;
---------------------------------------------------------------


SET STATISTICS IO OFF ;
GO
SET STATISTICS TIME OFF ;
GO
CREATE NONCLUSTERED INDEX ncidxTD_dtDate ON ##tmpDetails(dtDate) ;
GO

--DROP INDEX ncidxTD_dtDate ON ##tmpDetails ;


---------------------------------------------------------------
DBCC FREEPROCCACHE ;  --Clear Procedure Cache
GO
DBCC FREESYSTEMCACHE('ALL') ;  --Clear Plan Cache
GO
SET STATISTICS IO ON ;
GO
SET STATISTICS TIME ON ;
GO

SELECT TOP 1 MaxDate_OrderBy = dtDate FROM ##tmpDetails ORDER BY dtDate DESC
;
SELECT MaxDate_Max = MAX(dtDate) FROM ##tmpDetails
;
---------------------------------------------------------------