Well, it's up to you whether you want to store the data 😉

My recommendation was to take the select I provided in my last post to fill a temp table...

Step one (increase performance):

Since you do a self join on your cte you actually perform your three-table-join twice.

Maybe it's more efficient to store the result of your cte into a...

If your question has not been answered yet by either Barry or Matt then it would really help if you could provide a short sample of what you're trying to...

I'm not aware of any transpose function in SQL Server.

So you'd need to UNPIVOT your data to get three columns (state, month and value). Then use one of the links...

Yes and no.

No, since you won't find the keywords.

But yes, since this code's using a little...

@ Jeff: at a second thought I kinda disagree with your clustered index...

Shouldn't it be on (EmployeeID, SalaryDate) instead of RowID?

What would happen with rows like:

SELECT 1, 12, '20050101', 50000...

@andrew:

If you consider using Jeffs solution (which performs much better than mine) I think it's mandatory to read the Running Total / quirky update article[/url] including the discussion.

I think...

There's no reason to apologize, Ron!!

If there's anybody that made a mistake than it will be me, since I didn't describe the usage of my script well enough so I...

@bitbucket:

My code snippet has the following lines at then end:

--UPDATE #SalaryHistory SET #SalaryHistory.salaryamount = cte3.salaryamount

SELECT cte3.*

FROM cte3 INNER JOIN #SalaryHistory ON cte3.rowid=#SalaryHistory.rowid

The UPDATE and the SELECT part are alternatives. The...

@jason:

Wouldn't SQL Server decide on his own whether a temp table is required or not - even without defining global temp tables?

I, personally, try to avoid ##tables, since it cannot...

I'm pretty sure there will be a more elegant solution posted shortly but meanwhile here's what I came up with:

(Note: once you reviewed the result, change the SELECT line to...

Store the results of the following query into a temp and use that table for your inserts.

The [data] column holds the data for your second table in xml format....

Easy stuff first: In order to benefit from the LIKE statement you'd need to change like 'Dav' to like 'Dav%'.

How do you know if you have to use AND or...

I'm a little confused...

If you decide to create a new database you'd need to have a good understanding of the business rules as well as the data model and structure.

Once...

I'd recommend storing the data in an intermediate table and select from there.

Side note: I don't think it's necessary to quote the complete script for every reply...