You don't really need a temp table. All you need to do is modify the first CTE in Chris's approach (add WHERE):

;WITH

MyTrans AS (

...

Sounds like an interesting problem.

But you'll need to provide some DDL (CREATE TABLE statements), sample data and a clear picture of the expected results.

That will assure you get a tested...

Duplicate post deleted.

Duplicate post deleted.

I'm no expert, but can I still reply?

What you need is a crosstab query, which is pretty straightforward given a known number of tariffs and the fact that you even...

avssrk (9/20/2012)

---

In Table A , Any number of Classes can be Grouped to a Group

In Table A , Same set of Classes Cannot be Grouped

In Table B...

Chris - Trying out your new approaches in the below test harness (800K rows):

CREATE TABLE #Customer

( CustKey INT

,SSN ...

Actually the OP said he wants to look back 1 year, hence:

DATEDIFF(dd,MIN(TransDate),MAX(TransDate))

So maybe your Tally table takes the performance hit for no reason when you can just use the...

Eugene Elutin (9/20/2012)

---

There is nothing wrong with...

You're welcome.

I know the author well. He's not that skilled but once in awhile gets inspired.:-D

They must be doing those special sex-change...

dwain.c (9/20/2012)

---

ChrisM@Work (9/20/2012)

---

Haha! I had no idea that this query would be any faster...

ChrisM@Work (9/20/2012)

---

Haha! I had no idea that this query would be any faster when...

David Moutray (9/20/2012)

---

Here are the IO/timing results:

----------------- Dwain

Table 'Worktable'. Scan count 368, logical reads 101966, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob...

ChrisM@Work (9/20/2012)

---

Simpler, quicker, same results:

SELECT

t.TransID,

c.SSN,

t.TransDate,

t.Amount

FROM #Customer c

INNER JOIN #TransDtl t

ON t.CustKey = c.CustKey

CROSS APPLY (

SELECT Amount = SUM(ti.Amount)

FROM #TransDtl ti

WHERE t.CustKey = c.CustKey

AND ti.TransDate BETWEEN t.TransDate-1...