Here's what I would use.

;with cteTally AS

(

SELECT row_number() OVER(order BY v1.number) AS N FROM master..spt_values v1

WHERE type ='P' AND number<365

),

cteCalendar AS

(

SELECT dateadd(dd,N-1,'20100101') AS day_

FROM cteTally

)

,cteHrPerDay AS

(

SELECT dateadd(hh,N-1,day_)...

I didn't question Phils solution as "having a problem"...

I might have misinterpreted / overreacted though.

Probably caused by some bad private news I received shortly before... Shouldn't have posted in that...

It seems like Gus...

That's a slightly different requirement...

What I would do is to start with a calendar table holding days and hours.

Then I would use this table in a left join on Watts_Hour...

It seems like the time to find the root cause of poor performance and the "fix" was well spent!

Glad I could help! 😀

Alvin Ramard (1/5/2010)

---

Lynn Pettis (1/5/2010)

---

CirquedeSQLeil (1/5/2010)

---

Lynn Pettis (1/5/2010)

---

Actually, nothing earlier indicated that it was urgent, so more like ...

I have a problem:

http://www.sqlservercentral.com/Forums/Topic834653-338-1.aspx

And then, three weeks later, I am going...

Yeap.

It's the same like it is for any...

kramaswamy (1/5/2010)

---

Wow - that works amazingly well! Took a 45 minute operation down to 5 seconds!

Can you explain to me how this is working? I'm a bit fuzzy on the...

kramaswamy (1/5/2010)

---

Actually, the size of the string is about that, yes. Some of the larger subsets have over 1 million characters in them.

So, yeah, they are storing a ton of...

It seems like we need the OP to clarify what he's looking for...

If UNION will be used, there wouldn't be any difference whether it took the data from table 1 or table 2: both have to be identical. How would you know...

dbuendiab (1/5/2010)

---

I think you have already several good answers, or at last the kind I would have suggested before this one. Despite of this, and only as a alternative to...

winston Smith (1/5/2010)

---

... as there are strict rules about changes that go into production and how they have to go through Test systems first, so they are also included...

Do you have an index on your table that is comparable to the one I would use?

And if so, are the timings taken before or after applying the index?

Important side...

What have you tried so far?

Did you check the examples in BOL (Books Online, the SQL Server help system installed together with SQL Server)?

You might want to look for "Sorting...