SELECT case.id, case.resolution, case.timestamp, class.value
FROM closed_cases AS case
LEFT JOIN (select c.*,
(select min(timestamp)
from classifications c1
where c1.timestamp > c.timestamp) timeend
from classifications c) AS class
ON case.timestamp >= class.timestamp and
(case.timestamp < class.timeend or class.timeend IS NULL)
WHERE case.timestamp BETWEEN $1 AND $2;
Here is what the above code is Doing:
1. It's creating a table called 'closed_cases' that contains all of the data from the 'cases' table that has a 'status' of 'Closed'.
2. It's creating a table called 'classifications' that contains all of the data from the 'events' table that has a 'type' of 'classification'.
3. It's joining the 'closed_cases' table with the 'classifications' table on the 'timestamp' column.
4. It's selecting the 'id', 'resolution', 'timestamp', and 'value' columns from the joined table.
5. It's filtering the data to only include rows where the 'timestamp' is between the two dates that are passed into the query (in this case, 1/1/2016 and 1/31/2016).
The output of this query should be a table with the following columns:
| id | resolution | timestamp | value |
|----|------------|-----------|-------|
| 1 | Fixed | 1/1/2016 | Bug |
| 2 | Won't Fix | 1/2/2016 | Task |
| 3 | Fixed | 1/3/2016 | Bug |
| 4 | Won't Fix | 1/4/2016 | Task |
| 5 | Fixed | 1/5/2016 | Bug |
| 6 | Won't Fix | 1/6/2016 | Task |
| 7 | Fixed | 1/7/2016 | Bug |
| 8 | Won't Fix | 1/8/2016 | Task |
| 9 | Fixed | 1/9/2016 | Bug |
| 10 | Won't Fix | 1/10/2016 | Task |
| 11 | Fixed | 1/11/2016 | Bug |
| 12 | Won't Fix | 1/12/2016 | Task |
| 13 | Fixed | 1/13/2016 | Bug |
| 14 | Won't Fix | 1/14/2016 | Task |
| 15 | Fixed | 1/15/2016 | Bug |
| 16 | Won't Fix | 1/16/2016 | Task |
|