Vfrdtyky

Please can you help with a problem I am having, I believe is related to the gaps and islands problem in T-SQL. I am using SQL Server 2014.

I am trying to identify count the number of consecutive occurrences of a table/index combination using a date column to distinguish between the broken chains.

Please see the below T-SQL to demonstrate what I am trying to achieve, in particular how do I calculate the Rnk column which for demo purposes I have manually hard-coded?

CREATE TABLE #test (RowID INT IDENTITY(1,1), FileDate DATE, TableName VARCHAR(100), IndexName VARCHAR(100), Rnk INT)



INSERT INTO #test (FileDate, TableName, IndexName, Rnk) 

VALUES

('2015-10-31', 't1', 'idx1', 1),

('2015-10-30', 't1', 'idx1', 2),



('2015-10-27', 't1', 'idx1', 1),

('2015-10-26', 't1', 'idx1', 2),

('2015-10-25', 't1', 'idx1', 3),



('2015-10-23', 't1', 'idx1', 1),

('2015-10-22', 't1', 'idx1', 2),

('2015-10-21', 't1', 'idx1', 3),

('2015-10-20', 't1', 'idx1', 4),

('2015-10-19', 't1', 'idx1', 5),

('2015-10-15', 't1', 'idx1', 1),

('2015-10-13', 't1', 'idx1', 1),

('2015-10-10', 't1', 'idx1', 1),

('2015-10-09', 't1', 'idx1', 2),



('2015-10-27', 't3', 'idx13', 1),

('2015-10-26', 't3', 'idx13', 2),

('2015-10-25', 't3', 'idx15', 1),

('2015-10-24', 't3', 'idx15', 2),

('2015-10-21', 't3', 'idx13', 1)



SELECT * FROM #test 



DROP TABLE #test

In the screenshot I've attached, the portion of results highlighted would show I want the Rnk column to sequence the consecutive appearance of t1/idx between 2015-10-27 - 2015-10-25, but reset the number for the next appearance at 2015-10-23 through to 2015-10-19.

Can anyone assist me please?

Thanks.

Subtract a sequence of numbers from the date -- and the groups you have identified will have a constant value. Then you can use row_number():

select t.*,

       row_number() over (partition by tablename, indexname,

                                       dateadd(day, - seqnum, filedate)

                          order by filedate desc

                         ) as rnk

from (select t.*,

             row_number() over (partition by tablename, indexname order by filedate) as seqnum

      from t

     ) t

I would use cumulative approach :

select t.FileDate, t.TableName, t.IndexName,

       row_number() over (partition by tablename, indexname, grp order by rowid)

from (select t.*, sum(case when gap > 1 then 1 else 0 end) over (partition by tablename, indexname order by rowid) as grp

      from (select t.*, 

                   isnull(datediff(day, filedate, lag(filedate) over (partition by tablename, indexname order by rowid)), 1) as gap

            from #test t

           ) t

     ) t;

Similar to the answer from Yogesh, who beat me to it.

(hint: don't expect to be faster when typing an answer on your phone)

SELECT 

RowID, FileDate, TableName, IndexName, 

ROW_NUMBER() OVER (PARTITION BY TableName, IndexName,  DateRank ORDER BY FileDate DESC) AS Rnk

FROM

(

  SELECT *,

  SUM(DateGap) OVER (PARTITION BY TableName, IndexName ORDER BY FileDate DESC) AS DateRank

  FROM

  (

      SELECT RowID, FileDate, TableName, IndexName,

      --  Rnk as ExpRnk,

      CASE WHEN DATEDIFF(DAY, FileDate, LAG(FileDate) OVER (PARTITION BY TableName, IndexName ORDER BY FileDate DESC)) <= 1 THEN 0 ELSE 1 END AS DateGap

      FROM #Test

  ) q1

) q2

ORDER BY RowID;