I have data which has readings from various meters. I want the sum of Meter Readings between a time interval (IntervalPeriod). The table often gets updated with latest Meter Reading. The new reading will have same Meter ID and IntervalPeriod. The row with highest Version number but same Meter ID, IntervalPeriod represents latest reading.
I wish to find the sum of Meter Reading Column for a MeterID, between an Time interval. For a Meter ID, if the IntervalPeriod value is same, it should consider that row which has highest Version value. What will be the SQL Query for the same?
Sample Data: Example: Sum of Meter Reading for Meter ID = 101 between 05-09-2022 06:00:00 to 05-09-2022 08:00:00
| Row | Meter ID | IntervalPeriod | Meter Reading | Version |
|---|---|---|---|---|
| 1 | 101 | 05-08-2022 06:40:00 | 20 | 1 |
| 2 | 101 | 05-08-2022 07:30:00 | 76 | 1 |
| 3 | 101 | 05-08-2022 07:45:00 | 25 | 1 |
| 4 | 102 | 05-08-2022 08:00:00 | 31 | 1 |
| 5 | 101 | 05-08-2022 08:00:00 | 45 | 1 |
| 6 | 101 | 05-08-2022 08:00:00 | 23 | 2 |
| 7 | 103 | 05-08-2022 07:30:00 | 43 | 1 |
| 8 | 101 | 05-08-2022 07:45:00 | 10 | 2 |
| 9 | 101 | 05-08-2022 07:45:00 | 32 | 3 |
| 10 | 102 | 05-08-2022 09:00:00 | 44 | 1 |
Example: Sum of Meter Reading for Meter ID = 101 between 05-09-2022 06:00:00 to 05-09-2022 08:00:00
Expected result: 151
(row 6 highest version for same Meter ID and IntervalPeriod, hence is picked over row 5)
(row 9 has highest version for same Meter ID and IntervalPeriod, hence it is picked over row 3 and 8)
| Row | Meter ID | IntervalPeriod | Meter Reading | Version |
|---|---|---|---|---|
| 1 | 101 | 05-08-2022 06:40:00 | 20 | 1 |
| 2 | 101 | 05-08-2022 07:30:00 | 76 | 1 |
| 9 | 101 | 05-08-2022 07:45:00 | 32 | 3 |
| 6 | 101 | 05-08-2022 08:00:00 | 23 | 2 |
