I pretty much skipped a few previous steps in the query above for simplicity. Assuming I've already ordered the above data set with a previous cte, I need to look...

sorry, been out of office for a few days, but wanted to close the loop, both suggestions of enveloping the case statement with a cast and convert both worked as...

great responses and is what I need.  Really appreciate it. Some is over my head at the moment, but working as expected. If I had another request to count the...

this is great, Ken. Works as planned, however it's taking about an hour to complete. Anyway I can possibly optimize this? Thanks again for your help.

I appreciate your help..this worked great! Thank you.

tried starting out with the SQL below, but it's not evaluating each UserID/Row...only bringing back the "ordered" date in T2 obviously. 

select t1.*,
       (select t2.Date

thank you..

DCDate is actually Discharge Date. All visits have discharge dates, but in the query I provided the DCDispCode = 20 is actually the code for diseased.

They could have...

thanks so much for your response Lowell. If, for example, a person who dies on Jan 1, 2015, I would need a count of visits from Jan 1, 2014 through...

Lol..thanks for the replies. I appreciate the help I get from this forum. I'm a data analyst who's very new to the SQL world, so half the advice the gentleman...

this works great!..thanks so much for your help!

yes, if in the F.AttPhysKey, the same key can be in the F.SurgeonKey and yes, I'd like the row. Thanks!

thank you for your responses and link. I'll remember that for future questions I might have. They pseudo code worked fine..thanks for the advice.

this is basically the code...this join... left join UserView.vwEncounterDiagnosis d on d.VisitId=a.VisitId...will give me multiple rows per patient

i might need help with the diagnosis order/code statements in the where code...

thank you for the code..I'll give that a try. Do you have better suggestion for the sum? I'm trying to add all the department code charges, and if there were...

ahh, nevermind..answer was a few questions below...