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 2 months ago by  Phil Parkin. Reason: Slight code improvement

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

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

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