Author |
Topic |
pcock
Starting Member
12 Posts |
Posted - 2008-08-12 : 01:34:05
|
Hi All,I have a queryselect CustomerID, TransDateTime, Amountfrom TransactionsCash1, 2008-08-04 08:24:51.003, 2401, 2008-08-05 12:25:04.007, 5001, 2008-08-06 10:25:49.010, 1302, 2008-08-07 14:02:18.497, 3403, 2008-08-08 18:52:00.583, 9704, 2008-08-09 09:58:40.537, 5305, 2008-08-10 22:47:15.403, 280How do I'd to create a view to produce a weekly summary (mon - sun) like the following?count(CustomerID), sum(Amount), week_start, week_end7, 2990, 2008-08-04, 2008-08-10Thanks |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-08-12 : 01:51:09
|
[code]DECLARE @TransactionsCash TABLE( CustomerID int, TransDateTime datetime, Amount int)INSERT INTO @TransactionsCashSELECT 1, '2008-08-04 08:24:51.003', 240 UNION ALLSELECT 1, '2008-08-05 12:25:04.007', 500 UNION ALLSELECT 1, '2008-08-06 10:25:49.010', 130 UNION ALLSELECT 2, '2008-08-07 14:02:18.497', 340 UNION ALLSELECT 3, '2008-08-08 18:52:00.583', 970 UNION ALLSELECT 4, '2008-08-09 09:58:40.537', 530 UNION ALLSELECT 5, '2008-08-10 22:47:15.403', 280SELECT Cnt, Amt, week_start, week_end = DATEADD(DAY, 6, week_start)FROM( SELECT Cnt = COUNT(*), Amt = SUM(Amount), week_start = DATEADD(week, DATEDIFF(week, 0, TransDateTime), 0) FROM @TransactionsCash GROUP BY DATEADD(week, DATEDIFF(week, 0, TransDateTime), 0)) a/*Cnt Amt week_start week_end ----------- ----------- ----------- ----------- 6 2710 2008-08-04 2008-08-10 1 280 2008-08-11 2008-08-17 (2 row(s) affected)*/[/code] KH[spoiler]Time is always against us[/spoiler] |
|
|
pcock
Starting Member
12 Posts |
Posted - 2008-08-12 : 02:54:59
|
Hi Again,Thanks for the quick response, how do I now group activity that occurred on the 2008-08-10 to the week before? I like the summary to begin from (Monday 00:00:00 - Sunday 23:59:59)so it should show something like :Cnt Amt week_start week_end ----------- ----------- ----------- ----------- 7 2990 2008-08-04 2008-08-10 Thanks |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-08-12 : 03:01:30
|
[code]SELECT Cnt, Amt, week_start = DATEADD(DAY, 0, week_no * 7), week_end = DATEADD(DAY, 7, week_no * 7)FROM( SELECT Cnt = COUNT(*), Amt = SUM(Amount), week_no = DATEDIFF(DAY, 0, TransDateTime) / 7 FROM @TransactionsCash GROUP BY DATEDIFF(DAY, 0, TransDateTime) / 7 ) a[/code] KH[spoiler]Time is always against us[/spoiler] |
|
|
pcock
Starting Member
12 Posts |
Posted - 2008-08-12 : 18:46:48
|
Thank you!Works like a charm =) |
|
|
|
|
|