Michael Valentine Jones (12/3/2012)

---

A calendar table is not really needed for this:

select

YearMonth = dateadd(mm,datediff(mm,0,[recorddate]),0),

MyValueSum = sum([myvalue])

from

#a

group by

dateadd(mm,datediff(mm,0,[recorddate]),0)

order by

dateadd(mm,datediff(mm,0,[recorddate]),0)

Results:

YearMonth ...

ScottPletcher (11/30/2012)

---

capnhector (11/30/2012)

---

ScottPletcher (11/30/2012)

---

Eugene Elutin (11/30/2012)

---

ScottPletcher (11/30/2012)

---

...

In the interests of being thorough, I added a Message to my proposed solution back to them:

SELECT

...as above...,

...

ScottPletcher (11/30/2012)

---

Eugene Elutin (11/30/2012)

---

ScottPletcher (11/30/2012)

---

...

In the interests of being thorough, I added a Message to my proposed solution back to them:

SELECT

...as above...,

CASE...

Steven Willis (11/30/2012)

---

No loops, no cursors, no cte

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

DROP TABLE #TempTable

CREATE TABLE #TempTable

(

InvoiceId INT...

Jeff Moden (11/29/2012)

---

capnhector (11/29/2012)

---

EDIT: This is also for me to play with a couple of things and get more experience with them. so it may not be fast...

Have a look at ROW_NUMBER() OVER() and Common Table Expressions. These will allow you to accomplish what you need to do. If you need any help let us...

I have a slight rewrite that works. might not be better than any thing else but will eat as many sets with as many items in the set as...

Jeff Moden (11/27/2012)

---

CELKO (11/27/2012)

---

Create a million row FizzBuzz table. FizzBuzz isn't a comment. It's the name of a very common problem based on a child's game, is...

Alan.B (11/28/2012)

---

I am trying to understand why went with a loop vs a set-based approach. I posted a more set...

What i came up with was to turn it into an adjacency list hierarchy and then we can recourse through that. of course i could be completly off base...

If there is a row (3,14) would all your sample data then be returned because of (4,14) and (5,14)? from your description i think that is correct. just trying...

you can accomplish this using a pivot table or cross tab. Jeff Moden has a great 2 part series of articles on this

http://www.sqlservercentral.com/articles/T-SQL/63681/ Part1

http://www.sqlservercentral.com/articles/Crosstab/65048/ Part 2

so i came up with the following which is completly dynamic.

DECLARE @sql NVARCHAR(MAX) = ''

DECLARE @PivotColumn NVARCHAR(MAX) = ''

DECLARE @Start DATE = '2012-11-12'

DECLARE @End...

we did not include the unique constraint and do not foreign key to the accounts table on CustomerID, AccountID from the orders table. (all tables changed to reflect the...

pdanes (11/27/2012)

---

GSquared (11/27/2012)

---

pdanes (11/27/2012)

---

There doesn't seem to be any clear consensus on all this, so I guess I'll have to build a bunch of test cases and see what comes...