It ain't pretty, but it's a start. This will run faster than doing it with a cursor or a while loop.

You'll probably need to create another cte...

Maynor, looks like you need to PIVOT. There is another recent thread on that subject. Check it out.

Left Joins or WHERE NOT EXISTS are the ways to go. Here's why.

I ran a little experiment using the LEFT JOIN technique versus EXCEPT. The...

CREATE TABLE #Conditional

(

StartDate datetime,

value float,

x int

)

INSERT INTO #Conditional

VALUES ('08/10/2008 01:48', 3, 0) -- starting out with a zero value in the new "x" column

etc

--------------------------------

declare @b-2...

Jack's right. Good eye!

For what it's worth, I always use the left join format too. It's simple to remember, fast to execute, and easy...

Well, to start with the outputs are different. Exists returns a true/false value, whereas except returns a set of 0-n rows. Why would you...

This is not an optimal table design for doing what you want in SQL. It would be easier if your table was like this:

userName ...

From the looks of things, all but two of the subqueries in your WHERE clause are used only to determine if a column has a match in another table. ...

Shame on you.

You are taking what should be three different columns and making one column out of them. Keeping date and sequence separate in your db...

If you simply truncate a table, indexes still exist but are empty. They are reloaded when you reload the underlying table.

Yes, building or loading indexes takes work, which...

Okay... so much for theory. I ran the code at the bottom (without DBCC FREEPROCCACHE or DBCC DROPCLEANBUFFERS) and saw no significant IO differences against the base...

My bad. I didn't catch that the middle post was also from you. I see how using "AA" without a term qualification got you where...

Hey Craig:

By definition, in your joins, "A" only produces a set of rows for Fall 2008, just as "A2" can only return Summer 2008. As...

I've seen it done both ways. No best answer, it really depends on whether or not there is a hard and fast rule that says if-exists-then-update/if-not-exists-then-insert. ...

Nobody has unlimited budgets, so price is always a limiting factor. Therefore I cast my vote on the price/performance side of the house. Not that he needs...