Let's say, for simplicity sake, I have the following table:
Here's a sample response I want:
"2020-01-01T01:00:00": 25, #this is from adding records with ids: 2 and 3
"2020-01-01T03:00:00": 55 #this is from adding records with ids: 3,4,5 and 6
I want to write an SQL query that gets the total (sum) amount of all unique p_id's grouped by the hour. The p_id chosen is the record with the latest date. So for example, the first value in the response above doesn't include id 1 because the record with id 2 has the same p_id and the date on that record is later. The one tricky thing is I want to include the summation of all the amount per p_id if their date is before the hour presented. So for example, in the second value of the response (with key "2020-01-01T03:00:00"), even though id 3 has a datestamp in a different hour, it's the latest of that p_id (2) and therefore gets included when summing the amount in "2020-01-01T03:00:00". But since id 6 has p_id 1, record with id 2 doesn't get included, since record with id 6 overrides that p_id. Any help would be greatly appreciated!