MyDoggieJessie (3/27/2014)

---

Perhaps something like this?; WITH VisitTypes AS (

SELECT PatientID, VisitType, ROW_NUMBER() OVER ( PARTITION BY PatientID, VisitType ORDER BY VisitDate) AS Cnt, VisitDate

FROM #visit_info_table

) SELECT TOP...

Maybe something like this:

WITH FirstVisits AS(

SELECT PatientID,

VisitID,

VisitType,

VisitDate,

ROW_NUMBER() OVER( PARTITION BY PatientID, VisitType ORDER BY VisitDate) firstvisit

FROM #visit_info_table

),

RowsCTE AS(

SELECT *,

ROW_NUMBER() OVER(PARTITION BY PatientID ORDER BY VisitDate) rn

FROM FirstVisits

WHERE...

mikesyd (3/27/2014)

---

When and why would it be useful to have ANSI_NULLS on?? Something is either null or it isn't to me. Having the system say "I can't tell...

David Stout (3/27/2014)

---

I do agree the first one is easyer to read. I was pulling some old code and ran into the second way of doing it and I could...

robin.pryor (3/27/2014)

---

Ah but the question "What landed me on this post in the 1st place?"

I'm a SQL DBA and I've never given a crap about schemas. I know what they...

To be clear, both are ANSI compliant, just different versions. The first is the SQL-92 and the second one is SQL-86.

As a best practice I would suggest that you follow...

ANSI_NULLS OFF is on deprecation list, so even if you can set it that way, is a good thing to avoid it.

And regarding the explanation, using ISNULL() on the WHERE...

Welcome to another fun story sponsored by our "DBAs?".

Last night, about 1:30am someone decided it was a good time to disable xp_cmdshell. I'm sure that person read somewhere that disabling...

Trying to explain the logic, I found there was a mistake when the first day of a month is Sunday. If you grab a calendar, it becomes evident. From Tuesday...

Have you tried the import/export wizard? You could use it to create the SSIS package and review it to make sure your destination has the correct structure.

Congratulations Koen!

Thank you for the feedback. It's important that...

You could change the value in the order by to get the desired row for each group.

This is what I understood from your requirement. I agree with Sean and John that you need to post more details. Along with the solution, there's DDL and sample data,...

Be careful when using ISNUMERIC, it can give you unexpected results.

http://www.sqlservercentral.com/articles/ISNUMERIC()/71512/