Query

  • Hi

    I have 3 records

    DocNo      Tax Rate

    1                 5

    2                10

    2                12

    2                 10

    3                 12

    3                  12

    Result should be like below. I want only those Document No which have more than 1 Tax Rate

    2                10

    2                 12

    Thanks

  • It would help if you would provide consumable data next time (check my code to see how this is done).

    DROP TABLE IF EXISTS #SomeTab;

    CREATE TABLE #SomeTab
    (
    DocNo INT NOT NULL
    ,TaxRate INT NOT NULL
    );

    INSERT #SomeTab
    (
    DocNo
    ,TaxRate
    )
    VALUES
    (1, 5)
    ,(2, 10)
    ,(2, 12)
    ,(2, 10)
    ,(3, 12)
    ,(3, 12);

    SELECT *
    FROM #SomeTab st;

    WITH Counts
    AS (SELECT st.DocNo
    ,ct = COUNT(DISTINCT st.TaxRate)
    FROM #SomeTab st
    GROUP BY st.DocNo
    HAVING COUNT(DISTINCT st.TaxRate) > 1)
    SELECT DISTINCT
    st.DocNo
    ,st.TaxRate
    FROM #SomeTab st
    JOIN Counts
    ON Counts.DocNo = st.DocNo;

    • This reply was modified 3 years, 4 months ago by  Phil Parkin. Reason: Slight code improvement

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

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

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