I'm looking to query event history (project with lots of tasks and subtasks) to where it only pulls the first and last event on a given task's history and none of the ones in between.

Right now, I can't find another way to compare originating event based IDs (original task:subtask pair) and what the IDs currently are (sometimes they change based on what happens to the tasks when they're moving between queues).

This is the main chunk of query I'm working on.

The current "completed_at" strings I have trust issues with, so I want to change to using work_time (basically an in depth audit log), but I only want the MIN and MAX time.

Is this something I can do without it being an actual column?

select
    pt._id as t_id,
    ps._id as st_id,
    pt.completed_at as t_completed_at,
    pt.first_completed_at as t_first_completed_at,
    ps.completed_at as ps_completed_at,
    pt.times_redone,
    ps.is_recalled_subtask as ps_recalled,
    ps.status,
    ps.review_level,
    pt.customer_review_status,
    pt.customer_review_comments,
    array_size(ps.response:annotations),
    ps.subtask_version,
    vfwa.FIX_ATTEMPT,
    vfwa.work_time as time_attempt_was_completed

CodePudding user response：

You can use

Setup:

create table stock_price_history (company text, price_date date, price int);
insert into stock_price_history values
    ('ABCD', '2020-10-01', 50),
    ('XYZ' , '2020-10-01', 89),
    ('ABCD', '2020-10-02', 36),
    ('XYZ' , '2020-10-02', 24),
    ('ABCD', '2020-10-03', 39),
    ('XYZ' , '2020-10-03', 37),
    ('ABCD', '2020-10-04', 42),
    ('XYZ' , '2020-10-04', 63),
    ('ABCD', '2020-10-05', 30),
    ('XYZ' , '2020-10-05', 65),
    ('ABCD', '2020-10-06', 47),
    ('XYZ' , '2020-10-06', 56),
    ('ABCD', '2020-10-07', 71),
    ('XYZ' , '2020-10-07', 50),
    ('ABCD', '2020-10-08', 80),
    ('XYZ' , '2020-10-08', 54),
    ('ABCD', '2020-10-09', 75),
    ('XYZ' , '2020-10-09', 30),
    ('ABCD', '2020-10-10', 63),
    ('XYZ' , '2020-10-10', 32);