-- =============================================
-- Auth:	bdill
-- Date:	2015-05-29
-- Desc:	Gets back record counts by day of audit table.  Requires @TableName to have a DATE[TIME] field called "TransDate"
--          Requires Utility.dbo.DimDates table to exist and be populated
-- Upd:     2017-02-21 (bdill) added @StartDate parameter to optionally limit the starting point of the query.
-- Upd:		2019-10-29 (bdill) added @SchemaName and @TransDateColName to make it less hard coded
-- =============================================
ALTER PROCEDURE [dbo].[adm_AuditRecordCountsByDay_lst]
	  @DatabaseName VARCHAR(100) = 'MyDatabase_QA'
	 , @TableName VARCHAR(100) = 'Users_Audit'
	 , @SchemaName VARCHAR(100) = 'dbo'
	 , @StartDate DATETIME = NULL  -- Null will use Min(TransDate)
	 , @TransDateColNmae VARCHAR(100) = 'TransDate'
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @sql NVARCHAR(MAX)
	 
	IF OBJECT_ID('tempdb..#tmpDailyCounts') IS NOT NULL
		DROP TABLE #tmpDailyCounts
	CREATE TABLE #tmpDailyCounts ( [Date] DATE NOT NULL, Records INT NULL)

	-- =============================================================================
	DECLARE @MinTransDate DATE
	DECLARE @MaxTransDate DATE

	SET @sql = N'SET @MinTransDate = ( SELECT MIN(' + @TransDateColNmae + ') FROM ' + @DatabaseName + '.' + @SchemaName + '.' + @TableName + ')'
	EXEC sys.sp_executesql @sql, N'@MinTransDate DATE OUT', @MinTransDate OUT

	SET @sql = N'SET @MaxTransDate = ( SELECT MAX(' + @TransDateColNmae + ') FROM ' + @DatabaseName + '.' + @SchemaName + '.' + @TableName + ')'
	EXEC sys.sp_executesql @sql, N'@MaxTransDate DATE OUT', @MaxTransDate OUT
	
	DECLARE @CurrDate DATE = ISNULL(@StartDate, @MinTransDate)
	DECLARE @CurrDate_Plus1 DATE = DATEADD(DAY, 1, @CurrDate)
	-- =============================================================================

	WHILE @CurrDate <= @MaxTransDate
	BEGIN
		SET @sql = N'INSERT INTO #tmpDailyCounts ( Date, Records ) 
		SELECT CONVERT(DATE, ''' + CONVERT(VARCHAR(20), @CurrDate) + ''')
			, COUNT(*) 
		FROM ' + @DatabaseName + '.' + @SchemaName + '.' + @TableName + ' WITH (NOLOCK)	
		WHERE ' + @TransDateColNmae + ' >= ''' + CONVERT(VARCHAR(20), @CurrDate) + ''' AND ' + @TransDateColNmae + ' < DATEADD(DAY, 1, ''' + CONVERT(VARCHAR(20), @CurrDate) + ''')'

		EXEC sys.sp_executesql @sql

		SET @CurrDate = DATEADD(DAY, 1, @CurrDate)
		SET @CurrDate_Plus1 = DATEADD(DAY, 1, @CurrDate)
		PRINT @CurrDate
	END

	SELECT TDC.Date, DD.WeekdayName AS WkDay, TDC.Records
	FROM #tmpDailyCounts AS TDC
	JOIN Utility.dbo.DimDates AS DD ON DD.Date = TDC.Date
END