David - Really nice explanation of your approach. I have to say mine falls short in that regard.

I think it's important to look at the whole picture though. That includes both IOs and timings over a large test data set. So let's put together a small test harness (about 16,000 transaction rows) and give it a whirl:

CREATE TABLE #Customer

( CustKey INT PRIMARY KEY

,SSN NCHAR(9) NOT NULL

);

CREATE TABLE #TransDtl

( TransID INT IDENTITY PRIMARY KEY

,CustKey NCHAR(9) NOT NULL

,TransDate DATETIME NOT NULL

,Amount MONEY NOT NULL

);

INSERT INTO #Customer

SELECT 12345,'123456789' UNION ALL SELECT 67890,'123456789'

UNION ALL SELECT 98765,'987654321' UNION ALL SELECT 43210,'987654321'

UNION ALL SELECT 13579,'246801357' UNION ALL SELECT 24568,'246801357'

INSERT INTO #TransDtl

SELECT 12345,'01/01/12',$600 UNION ALL SELECT 12345,'01/02/12',$500

UNION ALL SELECT 67890,'01/03/12',$700 UNION ALL SELECT 98765,'04/01/12',$600

UNION ALL SELECT 43210,'04/02/12',$600 UNION ALL SELECT 43210,'04/03/12',$100

UNION ALL SELECT 13579,'04/02/12',$600 UNION ALL SELECT 24568,'04/03/12',$100

DECLARE @StartDT DATETIME = '2012-09-20'

,@DaysBack INT = 365

;WITH Tally (n) AS (

SELECT TOP 2000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM sys.all_columns a CROSS JOIN sys.all_columns b)

INSERT INTO #TransDtl

SELECT CustKey

,TransDate=DATEADD(day, -ABS(CHECKSUM(NEWID())) % 360, @StartDT)

,Amount=ABS(CHECKSUM(NEWID())) % 1000

FROM #TransDtl

CROSS APPLY Tally

-- Holders to capture output

DECLARE @TransID INT

,@SSN NCHAR(9)

,@TransDate DATETIME

,@Amount MONEY

PRINT '----------------- Dwain'

SET STATISTICS TIME, IO ON

;WITH Tally (n) AS (

SELECT TOP (@DaysBack) 1+@DaysBack-ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM sys.all_columns),

MyTrans AS (

SELECT TransID, c.CustKey, SSN, TransDate, Amount

FROM #TransDtl t

INNER JOIN #Customer c ON c.CustKey = t.Custkey)

SELECT DISTINCT @TransID=TransID, @SSN=SSN, @TransDate=TransDate, @Amount=Amount

FROM (

SELECT StartDT, EndDT=DATEADD(day, 1, StartDT), TransID, SSN, TransDate, Amount, CustKey

,Charges=SUM(Amount) OVER (PARTITION BY SSN, StartDT)

FROM Tally a

CROSS APPLY (SELECT StartDT=DATEADD(day, -n, @StartDt)) b

INNER JOIN MyTrans ON TransDate >= StartDt AND TransDate <= DATEADD(day, 1, StartDT)

) b

WHERE Charges >= 1000

SET STATISTICS TIME, IO OFF

PRINT '----------------- David'

SET STATISTICS TIME, IO ON

;WITH CustomerTrans

AS

( SELECT #Customer.SSN AS SSN

,#TransDtl.TransID AS TransID

,#TransDtl.TransDate AS TransDate

,#TransDtl.Amount AS Amount

FROM #Customer

JOIN #TransDtl

ON #Customer.CustKey = #TransDtl.CustKey

),

TransGroup

AS

( SELECT A.SSN AS SSN

,ROW_NUMBER() OVER (PARTITION BY A.SSN ORDER BY A.SSN) AS GroupID

,MIN(A.TransDate) AS StartDate

,MAX(B.TransDate) AS EndDate

FROM CustomerTrans AS A

JOIN CustomerTrans AS B

ON A.SSN = B.SSN

AND DATEDIFF(Day,A.TransDate,B.TransDate) BETWEEN 0 AND 1

GROUP BY A.SSN

),

GroupTotal

AS

( SELECT TransGroup.SSN AS SSN

,TransGroup.GroupID AS TransGroupID

,SUM(CustomerTrans.Amount) AS TransGroupAmount

FROM TransGroup

JOIN CustomerTrans

ON TransGroup.SSN = CustomerTrans.SSN

AND CustomerTrans.TransDate BETWEEN TransGroup.StartDate AND TransGroup.EndDate

GROUP BY TransGroup.SSN

,TransGroup.GroupID

HAVING SUM(CustomerTrans.Amount) >= 1000.00

),

FlaggedCustomer

AS

( SELECT SSN

FROM GroupTotal

GROUP BY SSN

)

SELECT @TransID=CustomerTrans.TransID

,@SSN=FlaggedCustomer.SSN

,@TransDate=CustomerTrans.TransDate

,@Amount=CustomerTrans.Amount

FROM FlaggedCustomer

JOIN CustomerTrans

ON FlaggedCustomer.SSN = CustomerTrans.SSN;

SET STATISTICS TIME, IO OFF

DROP TABLE #TransDtl

DROP TABLE #Customer

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 read-ahead reads 0.

Table '#TransDtl 000000000060'. Scan count 1, logical reads 95, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

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

Table 'syscolpars'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#Customer 00000000005F'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 312 ms, elapsed time = 368 ms.

----------------- David

Table '#Customer 00000000005F'. Scan count 7, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#TransDtl 000000000060'. Scan count 19, logical reads 665, physical reads 0, read-ahead reads 2, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 10, logical reads 534164, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 47565 ms, elapsed time = 41821 ms.

Looks like IOs isn't everything!

Note that I am not saying mine will rip apart the multi-million transaction rows you'd expect in a year's worth of credit card transactions for thousands of customers though. As I said, I thought there might be better performing options available out there.

We still need to hear back from the OP on my comments regarding expected results.