Sounds like homework to me...

Anyway... you need to perform an outer self-join based on ID=ID+1.

Use the CASE statement to take care of NULL values and different years.

If those

Give it...

I assume you need a dynamic solution to cover "flexible" date ranges.

In this case I'd recommend you start with reading the "crossTab" article refernced in my signature followed by the...

Do you want to have the result set as one column with separator or do you want one column per date?

Edit: and why do you need to do it on...

Glad I could help 🙂

Two minor fixes: 1) remove the namespace declaration since this is an untyped xml document and 2) remove the "/PROVIDER" from your nodes declaration since this tag is closed before.

--;WITH...

mzak (6/8/2010)

---

...

External environment aside...

If you're concerned about INSERT performance only, adding indexes will cause more writes to occur which has the potential to slow down INSERTs (depends on if your...

To follow tosscrosby's concern:

What would be your expected output for the following sample data? And Why?

INSERT @Sample (PersonID, Month) VALUES (11,'06');

INSERT @Sample (PersonID, Month) VALUES (11,'08');

INSERT @Sample...

I probably would use a CTE to calculate the sum using a case condition like you did. I think the execution plan will be the same but it's easier to...

You could use the FOR XML PATH string concatenation.

For a list of examples, please search this site for "string concatenation xml".

Side note: with 90 visits I'd expect you did run...

and here's the same approach, just without using intermediate tables (I'm using subqueries, or CTE's):

Side note (valid for both solutions provided so far): Those solution will only work if you...

But whatever you do, keep in mind that someone with sa privileges might still be able to change permissions without leaving a trace (or at least with a trace that's...

Thank you for clarification, Gregory! (I shuddered for a moment or two... :hehe: )

Glad it did save "a minute or two" 😀

It was a real pleasure working with you. Come...

gregory.anderson (6/7/2010)

---

Funny you just asked, Lutz. Had the conversion on Saturday. Ran in 1:48. Then entire process KILLED my previous times since I was running on some really beefy servers....

Hi Gregory,

any news regarding the performance on the production hardware?

@jeff: I'm confused...

When talking about the double row_number approach I was referring to the sample code I provided a few posts back (not the "deep dive" approach though).

It's showing a...