parameters_in_procedure-join&correlated sub-query content, Sql Server

parameters_in_procedure-join&correlated sub-query content

-- ENJOY codding
-- by alex_ogrinja

create table orders
(ord_no int
, purch_amt float
, ord_date date
, customer_id int
, salesman_id int);
go
insert into orders
values
(70001,      150.5  ,     '2012-10-05',  3005      ,   5002),
(70009 ,      270.65     , '2012-09-10',  3001    ,     5005),
(70002  ,     65.26     ,  '2012-10-05' , 3002   ,      5001),
(70004   ,    110.5    ,   '2012-08-17'  ,3009  ,       5003),
(70007   ,    948.5   ,    '2012-09-10',  3005 ,        5002),
(70005    ,   2400.6 ,     '2012-07-27',  3007     ,    5001),
(70008     ,  5760       , '2012-09-10' , 3002      ,   5001),
(70010,       1983.43   ,  '2012-10-10',  3004    ,     5006),
(70003  ,     2480.4   ,   '2012-10-10' , 3009   ,      5003),
(70012   ,    250.45  ,    '2012-06-27' , 3008  ,       5002),
(70011    ,   75.29  ,     '2012-08-17' , 3003 ,        5007),
(70013     ,  3045.6,      '2012-04-25'  ,3002,         5001);
go
create table salesman 
(salesman_id int
, name varchar(100)
, city varchar(50)
, commission float);
go
insert into salesman 
values
(5001 ,        'James Hoog',  'New York' ,   0.15),
(5002  ,       'Nail Knite',  'Paris'    ,   0.13),
(5005   ,      'Pit Alex'  ,  'London'  ,    0.11),
(5006    ,     'Mc Lyon'   ,  'Paris'   ,    0.14),
(5003     ,    'Ogrinja Alexandru',  'Bucuresti' ,   0.12),
(5007      ,   'Paul Adam' ,  'Rome'    ,    0.13);
go
create procedure salesman_report
(@nr_customers int
, @nr_orders int
, @sum float
, @avg float
, @amount_min float
, @amount_max float
, @order_date_min int
, @order_date_max int)
as
begin
declare @amount_null float
set @amount_null= (select max(purch_amt) from orders)
declare @order_date_null_min int
set @order_date_null_min= (select min(month(ord_date)) from orders)
declare @order_date_null_max int
set @order_date_null_max= (select max(month(ord_date)) from orders)
end
begin
select s.salesman_id, s.name salesman_name, o.customer_id, o.ord_no order_id, o.purch_amt amount, o.ord_date
from salesman s
join orders o
on s.salesman_id = o.salesman_id
where isnull
(@nr_customers,0) <=
(select count(distinct customer_id) from orders
where salesman_id = s.salesman_id)
and isnull
(@nr_orders,0) <=
(select count(distinct ord_no) from orders
where ord_no = o.ord_no)
and isnull
(@sum,0) <= 
(select sum(purch_amt) from orders
where customer_id = o.customer_id)
and isnull
(@avg,0) <= 
(select avg(purch_amt) from orders
where customer_id = o.customer_id)
and isnull 
(@amount_min,0) <= all
(select purch_amt from orders
where customer_id = o.customer_id)
and isnull
(@amount_max,@amount_null) >= all
(select purch_amt from orders
where customer_id = o.customer_id)
and
month(o.ord_date) between isnull (@order_date_min,@order_date_null_min) and isnull(@order_date_max,@order_date_null_max)
order by s.salesman_id, o.customer_id, o.ord_no, o.purch_amt, o.ord_date;
end
begin
print ''
print 'The list of salesmen whose meet the following requests:'
print ''
if @nr_customers is not null and @nr_customers=1 print space(5)+'- have in portfolio at least '+ cast(@nr_customers as varchar(20)) +' customer'
if @nr_customers is not null and @nr_customers>1 print space(5)+'- have in portfolio at least '+ cast(@nr_customers as varchar(20)) +' customers'
if @nr_orders is not null and @nr_orders=1 print space(5)+'- each of their customers have at least '+ cast(@nr_orders as varchar(20)) +' order'
if @nr_orders is not null and @nr_orders>1 print space(5)+'- each of their customers have at least '+ cast(@nr_orders as varchar(20)) +' orders'
if @sum is not null print space(5)+'- the orders sum of each customer is at least ' + cast(@sum as varchar(20))
if @avg is not null print space(5)+'- the average of each customer to be at least ' + cast(@avg as varchar(20))
if 
@amount_min is not null 
or
@amount_max is not null
print space(5)+'- the orders amount of each customer to be higher and equal then ' + cast(@amount_min as varchar(20)) + ' and lower and equal then ' + cast(@amount_max as varchar(20))
if 
@order_date_min is not null 
or
@order_date_max is not null
print space(5)+'- the requested period is between month ' + cast(@order_date_min as varchar(20))  + ' and month ' + cast(@order_date_max as varchar(20))
end
go

/*
Legend:
The list of salesmen whose meet the following requests:
 
- have in portfolio at least [@nr_customers] customer(s)
- each of their customers have at least [@nr_orders] order(s),
- the orders sum of each customer is at least [@sum],
- the average of each customer to be at least [@avg],
- the orders amount of each customer to be higher and equal then [@amount_min] and lower and equal then [@amount_max],
- the requested period is between month [@order_date_min] and month [@order_date_max].

! NULL value doesn't activate the request.
*/

exec salesman_report
@nr_customers = null
, @nr_orders = null
, @sum = null
, @avg = null
, @amount_min = null
, @amount_max = null
, @order_date_min = null
, @order_date_max = null

-- ENJOY codding
-- by alex_ogrinja


run \| edit \| history \| help	0