| timestamp | identifier | EDD | ward |
|---|---|---|---|
| 2022-03-04T09:00:00Z | ab1 | 2022-03-06T09:00:00Z | h1 |
| 2022-03-04T11:45:00Z | ab1 | 2022-03-07T09:00:00Z | h1 |
| 2022-03-05T11:45:00Z | ab1 | 2022-03-09T09:00:00Z | h1 |
| 2022-03-06T11:45:00Z | ab1 | 2022-03-09T09:00:00Z | G1 |
| 2022-03-04T11:45:00Z | xy | 2022-03-09T09:00:00Z | A1 |
| 2022-03-04T09:00:00Z | bc | 2022-03-07T09:00:00Z | S1 |
| 2022-03-06T11:45:00Z | abc | 2022-03-14T09:00:00Z | G1 |
| 2022-03-05T09:00:00Z | bc | 2022-03-12T09:00:00Z | S1 |
| 2022-03-07T11:45:00Z | xyz | 2022-03-10T09:00:00Z | Z1 |
| 2022-03-04T11:45:00Z | def | 2022-03-09T09:00:00Z | A1 |
| 2022-03-06T11:45:00Z | def | 2022-03-09T09:00:00Z | R1 |
| 2022-03-07T11:45:00Z | def | 2022-03-09T09:00:00Z | H1 |
For Every change in EDD for an identifier, it should flag 1
Expected output:
| timestamp | identifier | EDD | ward | Flag |
|---|---|---|---|---|
| 2022-03-04T09:00:00Z | ab1 | 2022-03-06T09:00:00Z | h1 | |
| 2022-03-04T11:45:00Z | ab1 | 2022-03-07T09:00:00Z | h1 | 1 |
| 2022-03-05T11:45:00Z | ab1 | 2022-03-09T09:00:00Z | h1 | 1 |
| 2022-03-06T11:45:00Z | ab1 | 2022-03-09T09:00:00Z | G1 | |
| 2022-03-04T11:45:00Z | xy | 2022-03-09T09:00:00Z | A1 | |
| 2022-03-04T09:00:00Z | bc | 2022-03-07T09:00:00Z | S1 | |
| 2022-03-06T11:45:00Z | abc | 2022-03-14T09:00:00Z | G1 | |
| 2022-03-05T09:00:00Z | bc | 2022-03-12T09:00:00Z | S1 | 1 |
| 2022-03-07T11:45:00Z | xyz | 2022-03-10T09:00:00Z | Z1 | |
| 2022-03-04T11:45:00Z | def | 2022-03-09T09:00:00Z | A1 | |
| 2022-03-06T11:45:00Z | def | 2022-03-09T09:00:00Z | R1 | |
| 2022-03-07T11:45:00Z | def | 2022-03-09T09:00:00Z | H1 |
You should use the prev() function:
<Your query>
| extend Flag = iff(EOD != prev(EOD), 1, 0)
Just note that in order for prev() to work, the input to the extend operator should be serialized, for eample, sorted by values in some column. This is because records in Kusto are not ordered.