adm_AuditRecordCountsByDay_lst.sql

-- =============================================
-- 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