----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create proc p4
as
--This stored procedure requires which salesperson has the most orders and all customer information for the order signed with this salesperson
begin
declare @eid int
declare @re table(eid int,count1 int)
insert @re select employeeid,count(*) from orders group by employeeid
--Insert the order count signed by each salesperson into @re
set @eid=(select eid from @re where count1=(select max(count1) from @re ))
--Enter the most salesperson number to @eid
print convert(varchar,@eid) + 'The salesperson signs the most orders'
--Output the number of this salesperson
select * from employees where employeeid=@eid
--Show detailed information of this salesperson
select * from orders as a right join customers as b on = and =@eid
--Show the customer information that signed an order with this salesperson
end
--drop proc p4
exec p4
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create proc p5
-- Weekly performance report of each salesperson
as
begin
select employeeid as salesperson number,count(*) as order number,sum(freight) as order amount from orders
where ((orderdate<=getdate()) and (orderdate>dateadd(day,-7,getdate()))) group by employeeid order by employeeid
end
--Since the signing records in northwind are all 98 years ago, the result of executing this stored procedure must be an empty set. Please insert the record, or change the system time to 96-98 years.
create proc p4
as
--This stored procedure requires which salesperson has the most orders and all customer information for the order signed with this salesperson
begin
declare @eid int
declare @re table(eid int,count1 int)
insert @re select employeeid,count(*) from orders group by employeeid
--Insert the order count signed by each salesperson into @re
set @eid=(select eid from @re where count1=(select max(count1) from @re ))
--Enter the most salesperson number to @eid
print convert(varchar,@eid) + 'The salesperson signs the most orders'
--Output the number of this salesperson
select * from employees where employeeid=@eid
--Show detailed information of this salesperson
select * from orders as a right join customers as b on = and =@eid
--Show the customer information that signed an order with this salesperson
end
--drop proc p4
exec p4
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create proc p5
-- Weekly performance report of each salesperson
as
begin
select employeeid as salesperson number,count(*) as order number,sum(freight) as order amount from orders
where ((orderdate<=getdate()) and (orderdate>dateadd(day,-7,getdate()))) group by employeeid order by employeeid
end
--Since the signing records in northwind are all 98 years ago, the result of executing this stored procedure must be an empty set. Please insert the record, or change the system time to 96-98 years.