locked
how to get the specific conditions data in SQL Table RRS feed

  • Question

  • Hi Experts,

    I am have a SQL query where I want to get the values of all "Approved" after the "Resubmit" in  CH.Approval_Outcome .

    My query as below

    SELECT DISTINCT CH.UserFullName
    , CH.UserTitle
    , CD.ModifiedDate
    , CH.Approval_Outcome
    , CH.CommentNo
    FROM  CD
    INNER JOIN CH ON CH.FK_Capex_Details = CD.PK_Capex_Details
    WHERE  (CD.CapexNo = '1205')
    ORDER BY CH.CommentNo

    I am getting output as below

    But I want like below

    How can i achieve this??. I want to get values after capturing the last "Resubmit ".


    Dasari

    Tuesday, March 20, 2012 2:50 AM

Answers

  • ;with cte as (select ModifiedDate, max(CommentNo) as LastReSubmit from Info

    WHERE Approval_Outcome = 'Resubmit'

    GROUP BY ModifiedDate)

    select I.* from Info I inner join cte on I.ModifiedDate = cte.ModifiedDate where I.CommentNo > cte.LastReSubmit and I.Approval_Outcome = 'Approved'


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Jeevan Dasari Tuesday, March 20, 2012 3:22 AM
    Tuesday, March 20, 2012 3:06 AM