I cracked it!

I spent almost the full day today working on one SQL report. I thought I had finished it yesterday, but my client sent me a question about record counts not adding up, and I had to go back to the drawing board. That's what I get for playing fast and loose.

I'm not sure why this particular report was so challenging. I understood the requirements and was familiar with the data and the tables involved. I suppose the challenge was carving the data into three sets based on specific criteria. The criteria involved the presence or absence of data, so it would be easy to tag records in multiple buckets if you don't account for that. 

I even went to ChatGPT for help at one point, and it suggested the same code that I used yesterday. I was on my own for this one.

I finally took a step back to consider the big picture. My task was to identify records where certain criteria were met or not met. This involved tricky logic with EXISTS vs. NOT EXISTS. My solution involved tagging the records in play and putting them in a temp table so that I could refer to them in other queries. It all came together this afternoon, and I sent the report off with a satisfying email to close out the week.