title: How to make a start writing this CTE in SQL Server?
body: <p>I have a table which contains data of different elements in a database. This is a count of all elements in the database (which is restored daily, so no DDL/DML triggers possible)</p>
  
  <p>Table looks like this:</p>
  
  <pre><code>LogDate SYSTEM_TABLE    VIEW    SQL_TABLE_VALUED_FUNCTION   DEFAULT_CONSTRAINT  SQL_STORED_PROCEDURE    RULE    FOREIGN_KEY_CONSTRAINT  SERVICE_QUEUE   SQL_INLINE_TABLE_VALUED_FUNCTION    CHECK_CONSTRAINT    USER_TABLE  PRIMARY_KEY_CONSTRAINT  INTERNAL_TABLE  TYPE_TABLE  SQL_TRIGGER SQL_SCALAR_FUNCTION UNIQUE_CONSTRAINT
  20150204    45  253 60  1401    5259    2   784 3   4   95  2141    1604    26  4   16  195 33
  20150203    45  253 60  1401    5259    2   784 3   4   95  2141    1604    16  4   16  195 33
  20150202    45  253 60  1401    5259    2   784 3   4   95  2141    1604    21  4   16  195 33
  20150201    45  253 60  1401    5259    2   784 3   4   95  2141    1604    25  4   16  195 33
  20150131    45  253 60  1401    5259    2   784 3   4   95  2141    1604    21  4   16  195 33
  </code></pre>
  
  <p>What I would like to do is compare the most recent log date (20150204) with the previous logdate (20150203) and see if there are any changes between the elements. This will then fire off an email to the relevant developer for them to investigate (but this section isn't important at hte moment, just highlighting the changes between the logdates for now)</p>
  
  <p>ETA:</p>
  
  <p>It's part of a much larger query that uses temp tables etc:</p>
  
  <pre><code>IF OBJECT_ID('tempdb..#DBTotsTEMP') is not null drop table #DBTotsTEMP
  
  --declare variables
  DECLARE @DynamicPivotQuery as NVARCHAR(MAX)
  DECLARE @ColumnName as NVARCHAR(MAX)
  
  SELECT     RIGHT(date, 4) + RIGHT(LEFT(date, 5), 2) + LEFT(date, 2) AS LogDate, [count] as CNT, type_desc
  INTO            [#DBTotsTEMP]
  FROM         BI_STG.SSRS.MH_DB_Totals
  ORDER BY LogDate DESC
  
  SELECT @ColumnName= ISNULL(@ColumnName + ',', '') + QUOTENAME([type_desc])
  FROM (
          SELECT DISTINCT [type_desc] FROM #DBTotsTEMP) as TypeDescs
  
  SET @DynamicPivotQuery = 
  N'SELECT LogDate, ' + @ColumnName + '
      INTO #MH_DB_Totals
      FROM #DBTotsTEMP
      PIVOT (SUM([CNT])
          FOR [type_desc] in (' + @ColumnName + ')) as PVTTable
          ORDER BY LogDate desc
          select * from #MH_DB_Totals'
  
  EXEC sp_executesql @DynamicPivotQuery
  </code></pre>
  
  <p>and I've got no idea where the CTE part should go, or how to highlight changes in the figures! </p>