What I need is to simpify this table.. In the original table, there are (potentially) many transactions on one day, hence the hh:mm:ss in the date. What I want is in the resulting table: when there are more than 1 transactions on one day, I need to keep the most recent transaction for that day, with the corresponding reserve amount. Days can then lose the hh:mm:ss extensions, so I get one final transaction per whole day.
Again, I think this should be accomplished with max() and group by.. But how? I did a few attempts, but couldn't get it to work.
See example below:
Original:
| %DossierNr | %ClaimVlgnr | Datum | Reserve |
| 990019914 | 990019914-1 | 2016-06-06 20:25:31 | € 239.419,78 |
| 990019914 | 990019914-1 | 2016-06-06 20:21:01 | € 240.938,57 |
| 990019914 | 990019914-1 | 2016-06-06 20:14:14 | € 242.325,84 |
| 990019914 | 990019914-1 | 2016-06-06 19:56:03 | € 282.325,84 |
| 990019914 | 990019914-1 | 2016-06-06 19:52:32 | € 283.006,66 |
| 990019914 | 990019914-1 | 2016-04-17 21:34:13 | € 285.000,85 |
| 990019914 | 990019914-1 | 2016-04-04 11:10:47 | € 286.000,85 |
| 990019914 | 990019914-1 | 2016-03-30 21:02:23 | € 291.500,00 |
| 990019914 | 990019914-1 | 2016-03-04 09:16:06 | € 295.500,00 |
| 990019914 | 990019914-1 | 2016-03-02 14:10:48 | € 300.000,00 |
| 990019914 | 990019914-1 | 2016-02-12 08:55:16 | € 11.150,29 |
| 990019914 | 990019914-1 | 2015-04-16 10:26:52 | € 14.500,00 |
Desired result:
| %DossierNr | %ClaimVlgnr | Datum | Reserve |
| 990019914 | 990019914-1 | 2016-06-06 | € 239.419,78 |
| 990019914 | 990019914-1 | 2016-04-17 | € 285.000,85 |
| 990019914 | 990019914-1 | 2016-04-04 | € 286.000,85 |
| 990019914 | 990019914-1 | 2016-03-30 | € 291.500,00 |
| 990019914 | 990019914-1 | 2016-03-04 | € 295.500,00 |
| 990019914 | 990019914-1 | 2016-03-02 | € 300.000,00 |
| 990019914 | 990019914-1 | 2016-02-12 | € 11.150,29 |
| 990019914 | 990019914-1 | 2015-04-16 | € 14.500,00 |
