n********6
发帖数: 1511 | 1 Source table:
transactionid, product, date
1, apple, 1/1/2011
2, apple, 1/12/2011
3, orange, 2/5/2011
...
100, apple, 11/1/2011
...
Objective report:
product, Count_Transaction_lastweek, Count_All
apple, 1, 4
orange, ...
谢谢。
*我先去自己想想看。 | a9
发帖数: 21638 | 2 group by 一下不就出来了吗？
【在 n********6 的大作中提到】
: Source table:
: transactionid, product, date
: 1, apple, 1/1/2011
: 2, apple, 1/12/2011
: 3, orange, 2/5/2011
: ...
: 100, apple, 11/1/2011
: ...
: Objective report:
: product, Count_Transaction_lastweek, Count_All
| n********6
发帖数: 1511 | 3 不知到如何一下group by同时得出count_lastweek & count_all
【在 a9 的大作中提到】
: group by 一下不就出来了吗？
| a9
发帖数: 21638 | 4 两次，left join
【在 n********6 的大作中提到】
: 不知到如何一下group by同时得出count_lastweek & count_all
| i*****w
发帖数: 75 | 5 Try this:
--Last Week means SUNDAY - SATURDAY
declare @tbl table (name varchar(30), dt datetime )
insert into @tbl values('Apple', '2011/11/1')
insert into @tbl values('Apple', '2011/11/2')
insert into @tbl values('Apple', '2011/11/4')
insert into @tbl values('Orange', '2011/11/10')
insert into @tbl values('Orange', '2011/11/5')
insert into @tbl values('Apple', '2011/11/6')
insert into @tbl values('Banana', '2011/11/10')
SELECT NAME, COUNT(*) as Total_Count, SUM(case when dt between DATEADD(dd, -
(datepart(dw, GetDate())+6), GetDate()) and DATEADD(dd, -datepart(dw,
GetDate()), GetDate()) then 1 else 0 end) as LastWeek_Count
FROM @tbl GROUP BY NAME
【在 n********6 的大作中提到】
: Source table:
: transactionid, product, date
: 1, apple, 1/1/2011
: 2, apple, 1/12/2011
: 3, orange, 2/5/2011
: ...
: 100, apple, 11/1/2011
: ...
: Objective report:
: product, Count_Transaction_lastweek, Count_All
| n********6
发帖数: 1511 | 6 Thank you very much.
To get more complicated:
What if to get the weekly summary in a time serial?
e.g.
product, week1, week2, ..., weekX, Total
先做 product, week1, ..., weekX（recursive? dynamic? pivot? else?)， 再加上left join得到Total?
【在 a9 的大作中提到】
: 两次，left join
| g***l
发帖数: 18555 | 7 当然要GROUP两次了，但注意要ALL FIRST，THEN UPDATE LAST WEEK |
|
