selecting a specific date from the data based on a comparison to a different row-CodePudding

I have the below rows of data for one PO. In this situation I need to select the date when the items move into inspection and compare it to today. The emphasized date from the image. I know this is the date I need because the previous row shows zero in inspection 'Insp' when sorted by date. That is the most recent date that parts moved to inspection. Can anyone write a sql that will select the most recent date into inspection and compare it to today to show how many days the parts have been in inspection?

ID  PO  Line    Rec Insp    Rej Transaction_Date
7616    263309  1   0   48  0   9/23/21 7:47 AM
7931    263309  1   0   0   48  10/12/21 7:36 AM
**13643 263309  1   0   42  48  9/21/22 9:39 AM**
13818   263309  1   0   42  0   9/28/22 4:27 PM
13819   263309  1   0   42  48  9/28/22 4:27 PM
13953   263309  1   0   42  0   10/4/22 7:55 AM
13955   263309  1   0   42  42  10/4/22 8:03 AM
13956   263309  1   0   42  0   10/4/22 8:04 AM
14011   263309  1   0   42  0   10/4/22 12:31 PM
14012   263309  1   0   42  0   10/4/22 12:31 PM

I have pulled the minimum date which in most cases is the date I need, but doesn't work if the parts have moved out and back into inspection.

CodePudding user response：

I understand that you are looking for the latest inspection entry for each PO, where an inspection entry is the row that immediately follows a row with 0 in Insp.

We can use lag to retrieve the "last" value of Insp and use that to filter on the inspection entries ; then, row_number() can be used to filter on the latest row per po.

select *
from (
    select t.*,
        row_number() over(partition by po order by transaction_date desc) rn
    from (
        select t.*,
            lag(insp) over(partition by po order by transaction_date desc) lag_insp
        from mytable t
    ) t
    where lag_insp = 0
) t
where rn = 1