How to get the group as first wire_open and wire

So this is the data what i want to output is which is matched with blue line and the desire output photo i have attached with it please have a look and get the desired result using a sql query

CodePudding user response：

Test this:

WITH
cte AS (
    SELECT *, SUM(delay_ping_type = 'wire_close') OVER (ORDER BY last_updated_on DESC) group_no
    FROM source_table
)
SELECT MIN(last_updated_on) wire_open, 
       MAX(last_updated_on) wire_close 
FROM cte
-- WHERE group_no > 0   -- removes final open without close
GROUP BY group_no
-- HAVING wire_open < wire_close  -- removes close without matched open
-- HAVING MIN(last_updated_on) < MAX(last_updated_on) -- the same for PostgreSQL
;

https://www.db-fiddle.com/f/njuPYFYug87jTWKHHv6yRK/2 (thanks to @lemon for the fiddle).

CodePudding user response：

Here's an example which may perform better than using windowed functions with group by. DBFiddle here: https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/5094

Updated: to fix issue where the first open rec was excluded instead of the 2nd duplicate.

PostgreSQL:

select r.last_updated_on as wire_open
    --get next wire_close record
    , (
        select r2.last_updated_on 
        from tblTest r2 
        where r2.last_updated_on > r.last_updated_on 
        and r2.delay_ping_type = 'wire_close' 
        order by r2.last_updated_on
        FETCH FIRST 1 ROW ONLY
       ) as  wire_closed
from tblTest r
where r.delay_ping_type = 'wire_open'
    --exclude duplicate wire_open records 
    --(exclude where this row is open and the preceeding row is open)
    AND NOT EXISTS(
                select 1 
                from (
                    SELECT dup.delay_ping_type 
                    from tblTest dup 
                    where dup.last_updated_on < r.last_updated_on 
                    order by dup.last_updated_on desc
                    FETCH FIRST 1 ROW ONLY
                    ) nextRec 
                where nextRec.delay_ping_type = 'wire_open'
                )
;