Given the following table with millions of log entries. Each log entry can be of kind = 0 (clearing) or kind = 1 (event). Clearing means that all preceding events are taken care of - so they can be grouped (in a view). All subsequent events must be grouped by their status and the latest date they occured.
WHILE or temp-tables are note really a solution with the size of this table. I looked into the sql group by only rows which are in sequence but couldn't figure out a way to adapt it to the given problem.
Edit 1: Rearanged rows to highlight clearing
The given data table (ordered by RelationId, ValidFrom DESC)
Id|RelationId|Kind|StatusId| ValidFrom
--------------------------------------
10| 1| 1| 14|2015-01-06
9 | 1| 1| 14|2015-01-05
8 | 1| 1| 15|2015-01-05
7 | 1| 0| 12|2015-01-04 (clearing id 6,4)
6 | 1| 1| 13|2015-01-03 (.. cleared with id 7)
4 | 1| 1| 13|2015-01-02 (.. cleared with id 7)
3 | 2| 1| 19|2015-01-02
1 | 2| 0| 18|2015-01-01 (no clearing)
5 | 3| 0| 18|2015-01-03 (clearing id 2)
2 | 3| 1| 20|2015-01-02 (.. cleared with id 5)
--------------------------------------
The disired output is given in the table below. As you can see, the event count does not take the clearing row into account.
Edit 2: Added question to clearify
Question: What would be an efficent SQL query that outputs the following results?
RelationId|StatusId|Count|MaxValidFrom
--------------------------------------
1 | 14| 2| 2015-01-06
1 | 15| 1| 2015-01-05
1 | 12| 2| 2015-01-04
2 | 19| 1| 2015-01-02
3 | 18| 1| 2015-01-03
--------------------------------------
The SQL Server used is SQL 2008 R2 Enterprise.
Thanks for your help!
