That's a lengthy code. Instead of including a whole set of columns in a group by, I used a correlated subquery with CROSS APPLY. You can do the same with...

This is a possible solution. Be sure to understand how does it work and ask any questions that you have.

SELECT Reference,

PARSENAME( r.dotted, 3) + '.'...

To give the example of LEAD() as proposed by LinksUp.

SELECT categoryID,

EffectiveDate AS StartDate,

DATEADD( dd, -1, LEAD(EffectiveDate) OVER( PARTITION BY categoryID ORDER...

AFAIK, you need an additional step as the condition differs for the "NOT MATCHED BY Source".

You could do it like this:

MERGE #Grantors as Target

USING #tempTors as Source

ON (Target.EntryNumber...

I don't understand. You say you need to divide the result of a CASE statement, but the result is a string. What are your expected results? A CTE or a...

Steve Jones - SSC Editor (7/28/2014)

---

Luis Cazares (7/28/2014)

---

Semicolon is not a separator, it's a terminator. The proof of that is the MERGE statement which needs to be ended with a...

Do you have unicode values in your file? If you don't you could clear the unicode checkbox in the flat file connection. If you do, you need to change the...

You might not need a convert, you need to check all the values are valid dates.

I feel the problem is with TERM.[Actual Terminated Date]

The Dixie Flatline (7/28/2014)

---

I respect and admire all you guys for your professionalism, and often restraint, in dealing with all manner of questions. Steve does a great...

Didn't realize that this was for reporting services until I was posting. Maybe this T-SQL code might give you an idea.

DECLARE @Seconds int = -223

SELECT CASE WHEN @Seconds <...

I'm just guessing here, but maybe a cross tabs approach is what you need to read the table just once.

Example:

SELECT x.Somecolumns,

MAX( CASE WHEN OrderOfDiagnosisInEpisode = 1...

Just wanted to note that you won't see much difference with 5 rows. Anything will run fast with 5 rows. That's why performance tests usually start at least with 1000...

Redefine 'expert' level as...

Welcome to the forums.

It's considered a nice way to ask for help if you post your data in a consumable format. As you're new, I did it for you, but...

If you're using SQL Server 2012, the easiest method (not the fastest) would be something like this:

select a.acctnum,

a.dates,

a.transtype,

b.balance,

...