World's most complicated SQL

Today, I declared that the SQL query I’ve been working on is the most complicated and difficult one I’ve ever done.

I will attempt to explain it to those who are curious. I work in the health insurance industry, and most of my work involves the payment part of the process. You go to the doctor or get lab work done, that provider submits a claim to a health insurance company, and the claim is processed by the system to pay the provider and assess any cost-share for the member. My role centers around the system that pays the claims.

Some services require a pre-authorization, which is essentially permission required from the insurance company prior to performing the service and submitting a claim. Ideally, the authorization is entered correctly so that when a claim is submitted and processed, the system will auto-match the claim to the authorization record. Of course, you can perform the service without authorization and submit the claim, but that claim is going to get stopped in the system. This is called a claim PEND (short for suspend). A claim may be pended because an authorization is not found or an authorization may exist but does not match the claim based on specific criteria. My job is to figure out why my client has so many claims in PEND status for prior authorization.

There could be one-to-many relationships between a claim and a particular authorization. The matching criteria are the member, provider, dates, and specific services (with lots of exceptions). I am creating some sort of fuzzy logic that can put each unique claim into a bucket to indicate why it did not auto-match to an authorization. It is tough, and somehow I have the hubris to believe I can do it.