SQL_Enthusiast (1/29/2013)
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.
It's happened to the best of us. Don't sweat it too much. Just remember we can't see as clearly what's important and what's not, but if it isn't consumable, we can't use it for testing to see what you are getting.
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.
So, to clarify this a bit for me, in the example table built by Luis above which I'm going to use for all references, CHARGE_BATCH_ID could be NULL? Also, STATUS is in what table? It's not shown, neither is Claim.
For the final piece, in the sample set Luis built from (I assume) what you provided before there are no repeated Charge_Item_IDs, so what's the connector for a Charge_item_id to connect to another, or is the dataset incomplete?
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.
Without a dataset describing what you're explaining here, I'm afraid I'm lost in figuring out what to code against.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA