I have following table, which apart from other attributes contains:

• ID - unique identifier
• Column1
• Column2
• Column3
• CreatedDate - when the record has been created (based on ETL)
• UpdatedDate - until when the record has been valid

Since there are other attributes apart from the 3 columns, which are being tracked for historical values, there might be cases, where there are multiple rows with the same values for all three columns for the same ID, but different timestamps in [CreatedDate] / [UpdatedDate]. Thus, the data may look like:

I would like to keep the unique values for all 3 columns in correct sequence, hence the ideal output should look like:

The below code works fine, if there is just one column, but it does not work for multiple columns as it returns all unique rows.

select ID, Column1, Column2, Column3,  min(createddate), max(updateddate)
from (select t.*,
             sum(case when prev_updatedate >= createddate then 0 else 1 end) over (partition by ID order by createddate) as grp
      from (select h.*,
                   max(updateddate) over (partition by ID order by createddate rows between unbounded preceding and 1 preceding) as prev_updatedate
            from #history h
           ) h
     ) h
group by ID, Column1, Column2, Column3, grp;

Any ideas how to fix it, please?

CodePudding user response：

You can try to use ROW_NUMBER window function to make the gap with your logic then you might get a grouping for gaps-and-islands

SELECT ID,Column1,Column2,Column3, min(createddate) CreatedDate, max(updateddate) UpdatedDate
FROM ( 
  select *,
      ROW_NUMBER() over (partition by ID order by createddate) - 
      ROW_NUMBER() over (partition by ID,Column1,Column2,Column3 order by createddate) grp
  from history
) t1
GROUP BY grp,ID,Column1,Column2,Column3
ORDER BY CreatedDate DESC

sqlfiddle