This works as well and probably faster than my first post. I like the windows function approach as well. Probably many ways to skin this cat. I'm waiting for some...

I have been drinking a few beers. Here is my answer but I'd advise for one of the other gents on here to give you a higher performing one as...

Sean Lange (12/1/2015)

---

Let's look at your last query. Reworking this a little bit to make it easier to see it would be something like this.

SELECT *

FROM #Patient P

INNER JOIN #Test...

Thanks Jacob,

Those both work. I have this just now but not sure how performance would be. Aliasing the Patient table into 2 different tables.

SELECT p.*, pot.TestName, cal.TestName

FROM #Patient P

INNER JOIN...

Thanks Sean,

I am trying to get all patients that had both a Potassium and a Calcium test. Which should be all 4 people.

Yes, good insight. I ended up creating a Permanent table with many, many more mappings in it. I also switched to LEFT JOIN so I will catch things missed in...

I did this and it works, however I would like to see if anyone has a better idea or method.

I created a mapping table then did wildcard joins. I'm sure...

Hi Allan,

The format in the date column I am filtering on is like this YYYYMMDD

Your code seems to be working perfectly though in my report query.

Thanks!

Yes sir that is correct. Wow, I love it I knew their was a better way!

Thanks Alan!!

Thanks so much for the feedback !

All of the solutions worked however after seeing John Mitchell-245523 CROSS JOIN solution and testing it for my self I am abandoning the Recursive...

What about NTILE in a Windows function? Would that work in your scenario?

Going to take a look at the execution plans now.

Only two allowable choices 1 or NULL nothing else.

Thank you all for the wonderful information. I should have also relayed more information than I did. It does seem that Jeff touched some on my situation with this table.

Unfortunately...

Using the WHERE with the IN() will require a split function.

I'll get creamed for saying this but you could use a catch all query.

In your query choices '-1' AS Value,...

Jason A. Long (5/30/2015)

---

My default answer for that question, for the last few years, has been to start by picking up a copy on "Microsoft SQL Server 2012 T-SQL Fundamentals"...