200分求周汇总、月汇总、季度汇总的存储过程（还有100分在MS-SQL Server）

[cc99]

200分求周汇总、月汇总、季度汇总的存储过程

涉及到的表：

create table account --帐户信息
(
accid int identity(1,1) primary key,--帐户序号
accname varchar(20),--帐户名称
accpas varchar(20),--帐户密码
uid char(20), --用户id号
useon char(1) default '1',--帐号是否启用
)

create table userinfo
(
uid char(20), --用户id号
uname nvarchar(20),--真实姓名
upart nvarchar(50),--所在部门
)

--销售基础数据
create table sellbase
(
uid char(20), --用户id号
extent nvarchar(50),--所在区域
upart nvarchar(50),--所在科室
task varchar(20), --年度任务
)

--到帐金额
create table ucurrent
(
uid char(20), --用户id号
cmoney varchar(20),--到帐金额
cdate datetime,--到帐日期
)

现在需要根据以上这些表，返回到帐金额的
===用户名、所在部门、排名、累积到帐资金、本周到帐资金、本月到帐资金、本季度到帐资金 ===
构成的一张表。天哪，我看了头都大的，差了很多资料，我这里也没有什么人可以请教的。谢谢，兄弟们帮忙！

[paoluo]

Select
A.uname As 用户名,
A.upart As 所在部门,
SUM(IsNull(B.cmoney,0)) As 累积到帐资金,
SUM(Case DateDiff(wk,B.cdate,GetDate()) When 0 Then IsNull(B.cmoney,0) Else 0 End) As 本周到帐资金,
SUM(Case DateDiff(mm,B.cdate,GetDate()) When 0 Then IsNull(B.cmoney,0) Else 0 End) As 本月到帐资金,
SUM(Case DateDiff(qq,B.cdate,GetDate()) When 0 Then IsNull(B.cmoney,0) Else 0 End) As 本季度到帐资金
From userinfo A
Left Join ucurrent B
On A.uid=B.uid
Group By A.uname,A.upart
Order By A.uname,A.upart

[Eddie005]

select account.accname,userinfo.uname,userinfo.upart,
(select sum(cmoney) from ucurrent where ucurrent.uid=account.uid),
(select sum(cmoney) from ucurrent where ucurrent.uid=account.uid and cdate>=DateAdd(day,-7,getdate()),
(select sum(cmoney) from ucurrent where ucurrent.uid=account.uid and datepart(year,cdate)=DatePart(year,getdate() and datepart(month,cdate)=DatePart(month,getdate()) ),
(select sum(cmoney) from ucurrent where ucurrent.uid=account.uid and datepart(year,cdate)=DatePart(year,getdate()) ),
from account inner join userinfo on account.uid = userinfo.uid where account.useon = 1

[Eddie005]

o~ paoluo(一天到晚游泳的鱼) 的答案更好！

[cc99]

排名按照到帐金额的多少来排名

[paoluo]

借用臨時表吧。


Select
用户名,
所在部门,
排名=Identity(Int,1,1),
累积到帐资金,
本周到帐资金,
本月到帐资金,
本季度到帐资金
Into #T
From(
Select
A.uname As 用户名,
A.upart As 所在部门,
SUM(IsNull(B.cmoney,0)) As 累积到帐资金,
SUM(Case DateDiff(wk,B.cdate,GetDate()) When 0 Then IsNull(B.cmoney,0) Else 0 End) As 本周到帐资金,
SUM(Case DateDiff(mm,B.cdate,GetDate()) When 0 Then IsNull(B.cmoney,0) Else 0 End) As 本月到帐资金,
SUM(Case DateDiff(qq,B.cdate,GetDate()) When 0 Then IsNull(B.cmoney,0) Else 0 End) As 本季度到帐资金
From userinfo A
Left Join ucurrent B
On A.uid=B.uid
Group By A.uname,A.upart) C
Order By 累积到帐资金 Desc

Select * From #T

[Fooo]

#T

[A__]

#T