Post reply

Pick Records Based On Condition!

  • July 18, 2016 at 9:22 am

    #311841

    Hi,

    I have a scenario,

    EmpId DivisionId Qualification Attempt Qualified

    123456 789 4/29/2016 N

    123456 789 3/1/2016 N

    654321 987 3/29/2016 Y

    654321 987 2/24/2016 N

    654321 987 2/15/2016 N

    654321 987 2/1/2016 N

    What I am trying to get is, if any employee has Qualified (Y) then I want to pick only the 'Y records, if the employee has any 'N' records I want to pick all 'N' records.

    The result should be like,

    EmpId DivisionId Qualification Attempt Qualified

    123456 789 4/29/2016 N

    123456 789 3/1/2016 N

    654321 987 3/29/2016 Y

    Will appreciate if anybody help.

    Thanks!

  • July 18, 2016 at 9:37 am

    #1890640

    This could give you an idea.

    SELECT *

    INTO #SampleData

    FROM(

    SELECT 123456, 789, 'N' UNION ALL

    SELECT 123456, 789, 'N' UNION ALL

    SELECT 654321, 987, 'Y' UNION ALL

    SELECT 654321, 987, 'N' UNION ALL

    SELECT 654321, 987, 'N' UNION ALL

    SELECT 654321, 987, 'N'

    )SampleData(EmpId, DivisionId, Qualified);

    WITH

    cternk AS(

    SELECT *, RANK() OVER(PARTITION BY EmpID, DivisionID ORDER BY Qualified DESC) rnk

    FROM #SampleData

    )

    SELECT EmpId, DivisionId, Qualified

    FROM cternk

    WHERE rnk = 1;

    DROP TABLE #SampleData;

    Note: I had to script out the sample data. When posting questions, please include CREATE TABLE and INSERT statements to post sample data so we don't have to waste time on that and can concentrate in solving the problem.

    Feel free to ask any questions that you might have.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • July 18, 2016 at 1:49 pm

    #1890708

    Thanks! Luis. It's working perfect.

  • July 18, 2016 at 2:13 pm

    #1890717

    Great, just be sure to understand it perfectly. 😉

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply