Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 SOLVED: weekly summary

Author  Topic 

pcock
Starting Member

12 Posts

Posted - 2008-08-12 : 01:34:05
Hi All,

I have a query

select CustomerID, TransDateTime, Amount
from TransactionsCash

1, 2008-08-04 08:24:51.003, 240
1, 2008-08-05 12:25:04.007, 500
1, 2008-08-06 10:25:49.010, 130
2, 2008-08-07 14:02:18.497, 340
3, 2008-08-08 18:52:00.583, 970
4, 2008-08-09 09:58:40.537, 530
5, 2008-08-10 22:47:15.403, 280

How do I'd to create a view to produce a weekly summary (mon - sun) like the following?

count(CustomerID), sum(Amount), week_start, week_end
7, 2990, 2008-08-04, 2008-08-10

Thanks

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 @TransactionsCash
SELECT 1, '2008-08-04 08:24:51.003', 240 UNION ALL
SELECT 1, '2008-08-05 12:25:04.007', 500 UNION ALL
SELECT 1, '2008-08-06 10:25:49.010', 130 UNION ALL
SELECT 2, '2008-08-07 14:02:18.497', 340 UNION ALL
SELECT 3, '2008-08-08 18:52:00.583', 970 UNION ALL
SELECT 4, '2008-08-09 09:58:40.537', 530 UNION ALL
SELECT 5, '2008-08-10 22:47:15.403', 280

SELECT 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]

Go to Top of Page

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
Go to Top of Page

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]

Go to Top of Page

pcock
Starting Member

12 Posts

Posted - 2008-08-12 : 18:46:48
Thank you!
Works like a charm =)
Go to Top of Page
   

- Advertisement -