Recurrences

• toni 667

  Valued Member

  Points: 67

  More actions

  September 18, 2015 at 6:22 am

  #1827700

  Here is the dynamic sql approach:

  It returns the found datetimes including the previous and following rows (if present) using then sample data from the post before.

  DECLARE

  @query AS NVARCHAR(MAX),

  @queryWHERE AS NVARCHAR(MAX),

  @querySELECT AS NVARCHAR(MAX),

  @MinValue AS INT = 5,

  @i AS INT

  SET @query = 'SELECT A.dt, Item AS FieldCompare'

  SET @i = 1

  WHILE @i < @MinValue

  BEGIN

  SET @Query = @Query + ',LEAD(Item, ' + CAST(@i AS NVARCHAR) + ', NULL) OVER (ORDER BY dt) AS FieldCompare' + CAST(@i AS NVARCHAR)

  + ',LEAD(A.dt, ' + CAST(@i AS NVARCHAR) + ', NULL) OVER (ORDER BY dt) AS dt' + CAST(@i AS NVARCHAR)

  SET @queryWHERE = ISNULL(@queryWHERE + + ' AND ', ' WHERE ') + 'A.FieldCompare = A.FieldCompare' + CAST(@i AS NVARCHAR)

  SET @i = @i + 1

  END

  SET @query = @query + ', LAG(A.dt, 1, NULL) OVER (ORDER BY dt) AS dt0, ' +

  'LEAD(A.dt, ' + CAST(@i AS NVARCHAR) + ', NULL) OVER (ORDER BY dt) AS dt' + CAST(@i AS NVARCHAR)

  SET @Query = @Query + ' FROM#tab1 AS A'

  SET @i = 1

  SET @QuerySELECT = 'SELECT A.dt0, A.dt'

  WHILE @i < @MinValue

  BEGIN

  SET @QuerySELECT = @QuerySELECT + ', A.dt' + CAST(@i AS NVARCHAR)

  SET @i = @i + 1

  END

  SET @QuerySELECT = @QuerySELECT + ', A.dt' + CAST(@i AS NVARCHAR)

  SET @Query = @QuerySELECT + ' FROM (' + @query + ') AS A ' + @QueryWHERE

  SET @query = 'SELECT DISTINCT u.dtList ' +

  'FROM('

  + @query

  + ') p UNPIVOT (dtList FOR Dates IN('

  SET @query = @query + 'dt0, dt'

  SET @i = 1

  WHILE @i < @MinValue

  BEGIN

  SET @query = @query + ',dt' + CAST(@i AS NVARCHAR)

  SET @i = @i + 1

  END

  SET @query = @query + ',dt' + CAST(@i AS NVARCHAR) + ')'

  SET @Query = @query + ') AS u;'

  execsp_executesql

  @query