I am using SQL to store Log Data. Inside the Log Data there are repeating or duplicate consecutive rows that I would like to condense into a single row. The new row needs to reflect the start and end time of the block of repeating or duplicate consecutive rows as well as the number of times that it repeated. However I still want to distinguish when the rows change.
Example:
TimeStamp | Column A | Column B | Column C | Column D | Column E |
---|---|---|---|---|---|
Time1 | 2 | 2 | 2 | 2 | 2 |
Time2 | 2 | 2 | 2 | 2 | 2 |
Time3 | 9 | 9 | 9 | 9 | 9 |
Time4 | 2 | 2 | 2 | 2 | 2 |
Time5 | 2 | 2 | 2 | 2 | 2 |
Time6 | 8 | 8 | 8 | 8 | 8 |
Time7 | 8 | 8 | 8 | 8 | 8 |
Time8 | 2 | 2 | 2 | 2 | 2 |
Time9 | 2 | 2 | 2 | 2 | 2 |
Time10 | 2 | 2 | 2 | 2 | 2 |
Wanted Result:
StartTime | EndTime | Column A | Column B | Column C | Column D | Column E | NumOccur |
---|---|---|---|---|---|---|---|
Time1 | Time2 | 2 | 2 | 2 | 2 | 2 | 2 |
Time3 | Time3 | 9 | 9 | 9 | 9 | 9 | 1 |
Time4 | Time5 | 2 | 2 | 2 | 2 | 2 | 2 |
Time6 | Time7 | 8 | 8 | 8 | 8 | 8 | 2 |
Time8 | Time10 | 2 | 2 | 2 | 2 | 2 | 3 |
This is Result I’m getting:
StartTime | EndTime | Column A | Column B | Column C | Column D | Column E | NumOccur |
---|---|---|---|---|---|---|---|
Time1 | Time10 | 2 | 2 | 2 | 2 | 2 | 7 |
Time3 | Time3 | 9 | 9 | 9 | 9 | 9 | 1 |
Time6 | Time7 | 8 | 8 | 8 | 8 | 8 | 2 |
I tried this but did not get the result I wanted:
SELECT MIN(TimeStamp) AS StartTime, MIN(TimeStamp) AS EndTime, Column A, Column B, Column C, Column D, Column E, count(*)
FROM table
GROUP BY Column A, Column B, Column C, Column D, Column E,
HAVING COUNT(*) > 1
I also tired implementing a Partition as well but with limited success
SELECT * FROM (
SELECT MIN(TimeStamp) AS StartTime, MIN(TimeStamp) AS EndTime, Column A, Column B, Column C, Column D, Column E
ROW_NUMBER () OVER(Partition by Column A, Column B, Column C, Column D, Column E
ORDER BY TimeStamp) RowNum
FROM table
) d
I attempted using the min and max but it did not work.
Ordnance2171 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.