egerencher (9/19/2012)
Here are the results I need:Trans#------SSN---------Date---------Amount
1--------123456789----01/01/12---------$600
2--------123456789----01/02/12---------$500
3--------123456789----01/03/12----------$10
4--------987654321----04/01/12---------$600
5--------987654321----04/02/12---------$600
6--------987654321----04/03/12---------$100
Firstly, I'd like to question why trans# 3 and 6 appear in your expected results. The total charges for period 02-03 Jan is $510 for 123456789 and for 02-03 Apr is $700 for 987654321.
Secondly, the next time you post, you should help us to help you by providing some DDL and readily consumable test data like this (check the link in bitbucket's signature line for more info):
DECLARE @Trans TABLE
(Trans# INT IDENTITY, CustKey INT, Date DATETIME, Amount MONEY)
INSERT INTO @Trans
SELECT 12345,'01/01/12',$600 UNION ALL SELECT 12345,'01/02/12',$500
UNION ALL SELECT 67890,'01/03/12',$10 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 @Cust TABLE
(CustKey INT, SSN VARCHAR(9))
INSERT INTO @Cust
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'
If I'm right about the expected results and ignoring any performance considerations of this approach, you may be able to get the transaction list as follows:
DECLARE @StartDT DATETIME = '2012-09-20'
,@DaysBack INT = 365
;WITH Tally (n) AS (
SELECT TOP (@DaysBack) 1+@DaysBack-ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns),
MyTrans AS (
SELECT Trans#, c.CustKey, SSN, Date, Amount
FROM @Trans t
INNER JOIN @Cust c ON c.CustKey = t.Custkey)
SELECT DISTINCT Trans#, SSN, Date, Amount
FROM (
SELECT StartDT, EndDT=DATEADD(day, 1, StartDT), Trans#, SSN, Date, 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 Date >= StartDt AND Date <= DATEADD(day, 1, StartDT)
) b
WHERE Charges >= 1000
I have chosen to use a Tally table but a Calendar table would work just as well.
Edit: Oh yes. And let's hope for the sake of privacy and legality, those SSNs are encrypted!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St