February 24, 2013 at 8:49 pm
Hi,
I got a number of transactions for a given day. I’m trying to calculate the sum of transactions for the Previous 7 days (not including the current day)
e.g
CREATE TABLE #Test
(
Code varchar(5),
TransDate date,
NoOfTran INT
)
INSERT INTO #Test
VALUES
('ABC', '2013-02-10', 21),
('ABC', '2013-02-07', 6),
('ABC', '2013-02-06', 5)
Result I am looking for….
Code TransDate Last7DaysNoOfTran
ABC 2013-02-10 11
ABC 2013-02-07 5
ABC 2013-02-06 0
any ideas?
February 24, 2013 at 9:25 pm
Hope this is what you are looking for:
SELECT code, transdate,SumTransLast7days = ISNULL(c.sumoftran, 0) FROM #Test a
CROSS APPLY
(SELECT sumoftran = SUM(nooftran) FROM #Test b
WHERE b.Code = a.code
AND (b.transdate > DATEADD(dd,-7,a.TransDate) and b.TransDate < a.TransDate)
) c
Result:
codetransdateSumTransLast7days
ABC2013-02-1011
ABC2013-02-075
ABC2013-02-060
February 24, 2013 at 9:55 pm
that's great..thank you!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply