Query

• jagjitsingh

  Ten Centuries

  Points: 1056

  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: 245813

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

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.