---------------------业务员业绩报表----------------------
create proc p4
as
--该存储过程求哪一个营业员签订的订单最多以及与此业务员签订订单的所有客户资料
begin
declare @eid int
declare @re table(eid int,count1 int)
insert @re select employeeid,count(*) from orders group by employeeid
--将各个业务员签订的订单数插入@re
set @eid=(select eid from @re where count1=(select max(count1) from @re ))
--将最多的业务员编号输入@eid
print convert(varchar,@eid) + '号业务员签订的订单最多'
--输出此业务员的编号
select * from employees where employeeid=@eid
--显示此业务员的详细资料
select * from orders as a right join customers as b on = and =@eid
--显示与此业务员签订订单的客户资料
end
--drop proc p4
exec p4
---------------------------------------------------我就是无情冷酷的分隔线-----------------------------------------------------------------
create proc p5
--各业务员业绩周报
as
begin
select employeeid as 业务员号,count(*) as 订单数,sum(freight) as 签单金额 from orders
where ((orderdate<=getdate()) and (orderdate>dateadd(day,-7,getdate()))) group by employeeid order by employeeid
end
--由于northwind中的签单记录都在98年以前，所以执行此存储过程的结果必为空集，请插入记录，或将系统时间改为96年-98年