求一个Procedure

Question

Table: STU
ID  StuID  StatusID    EFF_Date      Cost
1    1      100         01-01-2008    10
1    2      100         01-01-2008    20
1    3      100         02-01-2008    30
1    4      101         02-01-2008    40
1    5      102         03-01-2008    50
StatusID=100代表新学生
要求写一个Store procedure输入EFF_Date,要完成如下逻辑:
对于新学生，计算EFF_Date那一天的数量和COST，再计算EFF_Date之前(包括)所有学生的数量和COST如下例子:如EFF_Date输入01-01-2008
ID   新学生总数    新学生COST  学生总数   总COST
1    2            30        2         30如EFF_Date输入01-02-2008
ID   新学生总数    新学生COST  学生总数   总COST
1    0            0         2         30
如EFF_Date输入02-01-2008
ID   新学生总数    新学生COST  学生总数   总COST
1    1            30        4         100EFF_Date输入02-02-2008
ID   新学生总数    新学生COST  学生总数   总COST
1    0            0         4         100如EFF_Date输入03-01-2008
ID   新学生总数    新学生COST  学生总数   总COST
1    0            0         5         150EFF_Date输入03-02-2008
ID   新学生总数    新学生COST  学生总数   总COST
1    0            0         5         150

Answer 1

你举的例子是不是有点错?如EFF_Date输入02-01-2008 
ID  新学生总数    新学生COST  学生总数  总COST 
1    1            30        4        100 ---- 明明02-01-2008有两位同学呀? 怎么在你例子中只有一位了? 还是跟StatusID也有关??

Answer 2

create proc wsp
@date datetime
as
select id,
新学生总数=(select count(1) from STU where eff_date=@date),
新学生COST=(select isnull(sum(Cost),0) from STU where eff_date=@date),
学生总数=(select count(1) from STU where eff_date<=@date),
总COST=(select sum(Cost) from STU where eff_date<=@date)
from STU a group by id

Answer 3

create  proc p_selectstuinfo
@EFF_Date datetime
as
declare @id int,@a int,@b int,@c int,@d int
set @id=1
select @a=count(*),@b=sum(COST)
from stu as a where statusid=100 and EFF_Date=@EFF_Date group by id
select @c=count(*) from stu where EFF_Date<=@EFF_Date
select @d=sum(cost) from stu where EFF_Date<=@EFF_Date
select @id as id,isnull(@a,0) as 新学生总数,isnull(@b,0) as 新学生COST,isnull(@c,0) as 学生总数,isnull(@d,0) as 总COSTp_selectstuinfo '01-01-2008'
p_selectstuinfo '01-02-2008'
p_selectstuinfo '02-01-2008'
p_selectstuinfo '02-02-2008'
p_selectstuinfo '03-01-2008'
p_selectstuinfo '03-02-2008'

Answer 4

create table student(id int,stuid int,statusid int,eff_date datetime,cost int)
insert into student select 1,1,100,'01-01-2008',10
insert into student select 1,2,100,'01-01-2008',20
insert into student select 1,3,100,'02-01-2008',30
insert into student select 1,4,101,'02-01-2008',40
insert into student select 1,5,102,'03-01-2008',50
create proc proc_student
@dt datetime
as
begin
select id,
sum(case when statusid=100 then 1 else 0 end) as '新学生总数',
sum(case when statusid=100 then cost else 0 end) as '新学生COST',
count(1) as 学生总数,
sum(cost) as 总COST
from student
where datediff(dd,eff_date,@dt)>=0
group by id
endexec proc_student '2008-02-01'id 新学生总数 新学生COST 学生总数 总COST
1 3 60 4 100

Answer 5

结果:
p_selectstuinfo '01-01-2008'
/*
1 2 30 2 30
*/
p_selectstuinfo '01-02-2008'
/*
1 0 0 2 30
*/
p_selectstuinfo '02-01-2008'
/*
1 1 30 4 100
*/
p_selectstuinfo '02-02-2008'
/*
1 0 0 4 100
*/
p_selectstuinfo '03-01-2008'
/*
1 0 0 5 150
*/
p_selectstuinfo '03-02-2008'
/*
1 0 0 5 150
*/
没搞懂前面那个id是什么意思。

Answer 6

--忘记新学生这个条件了。改下。。
create  table STU(ID int,StuID int,StatusID int,EFF_Date datetime,Cost int)
insert into STU
select 1,1,100,'01-01-2008',10 union all
select 1,2,100,'01-01-2008',20 union all
select 1,3,100,'02-01-2008',30 union all
select 1,4,101,'02-01-2008',40 union all
select 1,5,102,'03-01-2008',50
gocreate proc wsp
@date datetime
as
select id,
新学生总数=(select count(1) from STU where eff_date=@date and statusid=100),
新学生COST=(select isnull(sum(Cost),0) from STU where eff_date=@date and statusid=100),
学生总数=(select count(1) from STU where eff_date<=@date),
总COST=(select sum(Cost) from STU where eff_date<=@date)
from STU a group by id
go--测试：
exec wsp '01-02-2008'
go

Answer 7

create table student(id int,stuid int,statusid int,eff_date datetime,cost int)
insert into student select 1,1,100,'01-01-2008',10
insert into student select 1,2,100,'01-01-2008',20
insert into student select 1,3,100,'02-01-2008',30
insert into student select 1,4,101,'02-01-2008',40
insert into student select 1,5,102,'03-01-2008',50
alter proc proc_student
@dt datetime
as
begin
select id,
sum(case when statusid=100 and datediff(dd,eff_date,@dt)=0 then 1 else 0 end) as '新学生总数',
sum(case when statusid=100 and datediff(dd,eff_date,@dt)=0 then cost else 0 end) as '新学生COST',
count(1) as 学生总数,
sum(cost) as 总COST
from student
where datediff(dd,eff_date,@dt)>=0
group by id
endexec proc_student '2008-02-02'id 新学生总数 新学生COST 学生总数 总COST
1 0 0 4 100这个靠谱了..

Answer 8

exec proc_student '01-01-2008'
exec proc_student '01-02-2008'
exec proc_student '02-01-2008'
exec proc_student '02-02-2008'
exec proc_student '03-01-2008'
exec proc_student '03-02-2008'id 新学生总数 新学生COST 学生总数 总COST
1 2 30 2 30id 新学生总数 新学生COST 学生总数 总COST
1 0 0 2 30id 新学生总数 新学生COST 学生总数 总COST
1 1 30 4 100id 新学生总数 新学生COST 学生总数 总COST
1 0 0 4 100id 新学生总数 新学生COST 学生总数 总COST
1 0 0 5 150id 新学生总数 新学生COST 学生总数 总COST
1 0 0 5 150