I admit, I was frustrated yesterday and I took it out on the forum. It just appears that with little effort on a post, I get the "best practices" post and with spending an hour putting every possible detail in place, I get the best practices post.
Anyway, my appologies.
I gave up on this query and moved on to other projects. Here I am, day two and I circled back and I'm still stuck. I tried some other things, including an Excel Pivot, but that ran all night on 1.2 million records, so that's not sustainable.
Let me try a little harder to explain the problem/goal here. I have millions of records in a CHG_ITEM table. Each record will have a REVENUE_SITE_ID and "could have" a CHARGE_BATCH_ID. If the STATUS = 'POSTED' is true, then the record "will have" a CHARGE_BATCH_ID. Starting at CLAIM, I need every claim number returned where ALL of the CHARGE_ITEM_ID records DO NOT HAVE the same REVENUE_SITE_ID.
For example
CLAIM 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.
Keep "looping" through the table until all CHG_ITEM rows have been reviewed.
Please tell me if you need anything else... I will be more than happy to research it and provide it. Thank you.
Here's code on the table joins
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
Here's other code I tried, but it also failed.
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