SHARE
TWEET

adm_AuditRecordCountsByDay_lst.sql

bdill Oct 30th, 2019 (edited) 149 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- =============================================
  2. -- Auth:    bdill
  3. -- Date:    2015-05-29
  4. -- Desc:    Gets back record counts by day of audit table.  Requires @TableName to have a DATE[TIME] field called "TransDate"
  5. --          Requires Utility.dbo.DimDates table to exist and be populated
  6. -- Upd:     2017-02-21 (bdill) added @StartDate parameter to optionally limit the starting point of the query.
  7. -- Upd:     2019-10-29 (bdill) added @SchemaName and @TransDateColName to make it less hard coded
  8. -- =============================================
  9. ALTER PROCEDURE [dbo].[adm_AuditRecordCountsByDay_lst]
  10.       @DatabaseName VARCHAR(100) = 'MyDatabase_QA'
  11.      , @TableName VARCHAR(100) = 'Users_Audit'
  12.      , @SchemaName VARCHAR(100) = 'dbo'
  13.      , @StartDate DATETIME = NULL  -- Null will use Min(TransDate)
  14.      , @TransDateColNmae VARCHAR(100) = 'TransDate'
  15. AS
  16. BEGIN
  17.     SET NOCOUNT ON;
  18.     DECLARE @sql NVARCHAR(MAX)
  19.      
  20.     IF OBJECT_ID('tempdb..#tmpDailyCounts') IS NOT NULL
  21.         DROP TABLE #tmpDailyCounts
  22.     CREATE TABLE #tmpDailyCounts ( [Date] DATE NOT NULL, Records INT NULL)
  23.  
  24.     -- =============================================================================
  25.     DECLARE @MinTransDate DATE
  26.     DECLARE @MaxTransDate DATE
  27.  
  28.     SET @sql = N'SET @MinTransDate = ( SELECT MIN(' + @TransDateColNmae + ') FROM ' + @DatabaseName + '.' + @SchemaName + '.' + @TableName + ')'
  29.     EXEC sys.sp_executesql @sql, N'@MinTransDate DATE OUT', @MinTransDate OUT
  30.  
  31.     SET @sql = N'SET @MaxTransDate = ( SELECT MAX(' + @TransDateColNmae + ') FROM ' + @DatabaseName + '.' + @SchemaName + '.' + @TableName + ')'
  32.     EXEC sys.sp_executesql @sql, N'@MaxTransDate DATE OUT', @MaxTransDate OUT
  33.    
  34.     DECLARE @CurrDate DATE = ISNULL(@StartDate, @MinTransDate)
  35.     DECLARE @CurrDate_Plus1 DATE = DATEADD(DAY, 1, @CurrDate)
  36.     -- =============================================================================
  37.  
  38.     WHILE @CurrDate <= @MaxTransDate
  39.     BEGIN
  40.         SET @sql = N'INSERT INTO #tmpDailyCounts ( Date, Records )
  41.         SELECT CONVERT(DATE, ''' + CONVERT(VARCHAR(20), @CurrDate) + ''')
  42.             , COUNT(*)
  43.         FROM ' + @DatabaseName + '.' + @SchemaName + '.' + @TableName + ' WITH (NOLOCK)
  44.         WHERE ' + @TransDateColNmae + ' >= ''' + CONVERT(VARCHAR(20), @CurrDate) + ''' AND ' + @TransDateColNmae + ' < DATEADD(DAY, 1, ''' + CONVERT(VARCHAR(20), @CurrDate) + ''')'
  45.  
  46.         EXEC sys.sp_executesql @sql
  47.  
  48.         SET @CurrDate = DATEADD(DAY, 1, @CurrDate)
  49.         SET @CurrDate_Plus1 = DATEADD(DAY, 1, @CurrDate)
  50.         PRINT @CurrDate
  51.     END
  52.  
  53.     SELECT TDC.Date, DD.WeekdayName AS WkDay, TDC.Records
  54.     FROM #tmpDailyCounts AS TDC
  55.     JOIN Utility.dbo.DimDates AS DD ON DD.Date = TDC.Date
  56. END
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top