Detecting change week over week in SQL table

• DesNorton

  SSC-Insane

  Points: 24387

  More actions

  January 10, 2018 at 12:43 am

  #1975225

  I missed the ADDED snapshots

  This should take care of Added, Removed, and Changed snapshots
  WITH cteLead AS (
SELECT [ID], [SnapshotDate], [ProjectName], [ProjectValue]
  --, [PrevVal] = LAG([ProjectValue]) OVER (PARTITION BY [ProjectName] ORDER BY [SnapshotDate])
  --, [MinDate] = MIN([SnapshotDate]) OVER ()
  --, [NextVal] = LEAD([ProjectValue]) OVER (PARTITION BY [ProjectName] ORDER BY [SnapshotDate])
  --, [MaxDate] = MAX([SnapshotDate]) OVER ()
  , [ChangeType] = CASE WHEN LAG([ProjectValue]) OVER (PARTITION BY [ProjectName] ORDER BY [SnapshotDate]) IS NULL
         AND [SnapshotDate] > MIN([SnapshotDate]) OVER () THEN 'ADDED'
        WHEN LEAD([ProjectValue]) OVER (PARTITION BY [ProjectName] ORDER BY [SnapshotDate]) IS NULL
         AND [SnapshotDate] < MAX([SnapshotDate]) OVER () THEN 'REMOVED'
        WHEN [ProjectValue] != LAG([ProjectValue]) OVER (PARTITION BY [ProjectName] ORDER BY [SnapshotDate]) THEN 'CHANGED'
       END
FROM dbo.Snapshots
)
SELECT *
FROM cteLead
WHERE ChangeType IS NOT NULL;


  ---

  How to post data/code on a forum to get the best help.