Using the new data:
SQL_Enthusiast (1/29/2013)
For exampleCLAIM 100 had 2 CHARGE_ITEM_ID records and CLAIM 101 had 2 CHARGE_ITEM_ID records
CLAIM 100 - CHARGE_ITEM_ID 5000 & 50550 have REVENUE_SITE_ID of 1000 and 1000 - SKIP this record
CLAIM 101 - CHARGE_ITEM_ID 25362 & 31025 have REVENUE_SITE_ID of 1000 and 1001 - Capture/Return this record to the result set.
but still shooting in the dark.
WITH CTE AS(
SELECT claim,
charge_item_id,
revenue_site_id,
AVG( CAST( revenue_site_id AS decimal( 18, 8))) OVER( PARTITION BY claim) avg_revenue_site_id
FROM ( VALUES( 100, 5000, 1000),
( 100, 50550, 1000),
( 101, 25362, 1000),
( 101, 31025, 1001)) AS Claims(CLAIM,CHARGE_ITEM_ID,REVENUE_SITE_ID)
)
SELECT claim,
charge_item_id,
revenue_site_id
FROM CTE
WHERE revenue_site_id <> avg_revenue_site_id