SELECT TOP ( 100  )
      Cgi.CHARGE_BATCH_ID
    , Cgi.CHARGE_ITEM_ID
    , Cgi.REVENUE_SITE_ID
   FROM
      dbo.CHG_ITEM AS Cgi
   WHERE
      Cgi.CHARGE_BATCH_ID IS NOT NULL AND
      Cgi.STATUS = 'POSTED'
   ORDER BY
      Cgi.CHARGE_BATCH_ID
    , Cgi.CHARGE_ITEM_ID
    , Cgi.REVENUE_SITE_ID

CHARGE_BATCH_ID     CHARGE_ITEM_ID    REVENUE_SITE_ID
1	                    15929	            1000
1	                    16930	            1000
10	                    17186	            2000
[color=#033]10	                    18187	            5000[/color]
10	                    22188	            2000
22	                    14189 	            3000
22	                    21190 	            3000
22	                    20191	            3000
100	                    30282	            4000
100	                    30283 	            4000
100	                    11284	            4000

CREATE TABLE CHG_ITEM(
	CHARGE_BATCH_ID     int,
	CHARGE_ITEM_ID		int,
	REVENUE_SITE_ID		int)
INSERT INTO CHG_ITEM(CHARGE_BATCH_ID,     CHARGE_ITEM_ID,    REVENUE_SITE_ID)
VALUES
(1, 15929, 1000),
(1, 16930, 1000),
(10, 17186, 2000),
(10, 18187, 5000),
(10, 22188,	2000),
(22, 14189, 3000),
(22, 21190,	3000),
(22, 20191,	3000),
(100, 30282, 4000),
(100, 30283, 4000),
(100, 11284, 4000);
WITH CTE AS(
SELECT 
	Cgi.CHARGE_BATCH_ID
	, Cgi.CHARGE_ITEM_ID
	, CASE WHEN COUNT( REVENUE_SITE_ID) OVER( PARTITION BY Cgi.CHARGE_BATCH_ID) <> COUNT( REVENUE_SITE_ID) OVER( PARTITION BY Cgi.CHARGE_BATCH_ID, Cgi.REVENUE_SITE_ID)
		AND COUNT( REVENUE_SITE_ID) OVER( PARTITION BY Cgi.CHARGE_BATCH_ID, Cgi.REVENUE_SITE_ID) = 1
			THEN Cgi.REVENUE_SITE_ID END AS REVENUE_SITE_ID
FROM
  dbo.CHG_ITEM AS Cgi
--WHERE
--   Cgi.CHARGE_BATCH_ID IS NOT NULL AND
--   Cgi.STATUS = 'POSTED'
)
SELECT CHARGE_BATCH_ID
	, CHARGE_ITEM_ID
	, REVENUE_SITE_ID
FROM CTE
WHERE REVENUE_SITE_ID IS NOT NULL
   ORDER BY
      CHARGE_BATCH_ID
	, CHARGE_ITEM_ID
	, REVENUE_SITE_ID

SELECT DISTINCT TOP ( 1000 )
      c.CLAIM_NUMBER
    --, cgi.CHARGE_ITEM_ID
    --, Cgi.REVENUE_SITE_ID AS ChgItemRev
    --, Ard.REVENUE_SITE_ID AS ClaimRev
   FROM
      dbo.CLAIM AS c
   JOIN 
      dbo.AR_DETAIL AS Ard
   ON c.SYS_ID = Ard.CLAIM_SYS_ID
   JOIN 
      dbo.CHARGE_ON_CLAIM AS coc
   ON coc.CLAIM_SYS_ID = c.SYS_ID
   JOIN 
      dbo.CHG_ITEM AS Cgi
   ON coc.CHG_ITEM_SYS_ID = Cgi.SYS_ID
   WHERE
      c.STATUS = 'A' AND
      Cgi.STATUS = 'POSTED' AND
      Cgi.REVENUE_SITE_ID <> Ard.REVENUE_SITE_ID --not correct logic per last minute change control ticket
      -- new logic: Compare the charge item revenue sites to each other (not to the AR detail rev site) 
   ORDER BY
      C.CLAIM_NUMBER

DECLARE @ci VARCHAR(15)
DECLARE @cb VARCHAR(15)
DECLARE @rev VARCHAR(10)
DECLARE @rowNum INT
DECLARE @maxrows INT 

SELECT DISTINCT
      @ci = CHARGE_ITEM_ID
    , @cb = CHARGE_BATCH_ID
    , @rev = REVENUE_SITE_ID
   FROM
      dbo.CHG_ITEM
   WHERE
      dbo.CHG_ITEM.CHARGE_BATCH_ID IS NOT NULL AND
      dbo.CHG_ITEM.STATUS = 'POSTED'
SELECT
      @maxrows = COUNT(*)
   FROM
      dbo.CHG_ITEM
   WHERE
      dbo.CHG_ITEM.CHARGE_BATCH_ID IS NOT NULL AND
      dbo.CHG_ITEM.STATUS = 'POSTED'
      
WHILE @rowNum < @maxrows 
   BEGIN
      SET @rowNum = @rowNum + 1
      PRINT ( 'Distinct Rows:' + @ci )
      SELECT DISTINCT
            @ci = CHARGE_ITEM_ID
          , @cb = CHARGE_BATCH_ID
          , @rev = REVENUE_SITE_ID
         FROM
            dbo.CHG_ITEM
         WHERE
            dbo.CHG_ITEM.REVENUE_SITE_ID <> @rev
   END