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;
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