Query

• jagjitsingh

  SSC Eights!

  Points: 945

  More actions

  August 16, 2021 at 7:39 am

  Go to Answer

  #3920260

  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

• Phil Parkin

  SSC Guru

  Points: 245322

  More actions

  August 16, 2021 at 8:23 am

  Answer

  #3920264

  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.