U8生产订单审核次数

with processed_wa as (

    SELECT
    VoucherId,
    VoucherType,
    VoucherCode,
    Action,
    OperatorName,
    OperationDate,
    row_number() over(partition by VoucherID order by OperationDate asc) action_r

    from WFAudit wa with(nolock)
    where wa.[VoucherType] = N'MO21'

), filtered_wa as (
    -- Action
    -- 0 提交
    -- 1 同意
    -- 7 弃审
    -- 5 撤销
    -- 后面有5, 7都不要
    SELECT
    t1.*
    FROM processed_wa t1

    where not EXISTS (
        SELECT top 1 1 as ignored
        FROM processed_wa t2
        where t2.VoucherID = t1.VoucherID
        AND t2.VoucherType = t1.VoucherType
        AND t2.Action in (5, 7)
      AND t2.action_r >= t1.action_r
    )

)

SELECT
-- *,
-- count(*) over(partition by [VoucherId]) c
count(*) c
FROM filtered_wa
WHERE VoucherType = 'MO21'
AND filtered_wa.Action = 1
AND [VoucherId] = 1000090696
-- AND [VoucherId] = 1000129275