-- John Aromando -- SQL Variables & User Functions --

use ogcPubLev3

--create variables to assist querying a table.
--run variables simultaneously with the query.
declare @Dsct1 as float = 0.05 --test with alt var of .1
declare @Dsct2 as float = 0.10 --test with alt var of .2
declare @Dsct3 as float = 0.15 --test with alt var of .3

select 
	Custname, Bktitle, Qty, slprice, 
	Total=qty*slprice, Discount=
		CASE 
			 WHEN qty < 200 THEN @Dsct1
			 WHEN qty < 400 THEN @Dsct2
			 ELSE  @Dsct3
		END,
	DiscountedPrice=(qty * slprice)*
		CASE 
			 WHEN qty < 200 THEN 1-@Dsct1
			 WHEN qty < 400 THEN 1-@Dsct2
			 ELSE  1-@Dsct3
		END
from
	Customers as c, Sales as s, Titles as t
where
	c.custnum=s.custnum and
	s.partnum=t.partnum
order by custname

--clean up formatting of previous query.
declare @Dsct1 as float = .05 --test with alt var of .1
declare @Dsct2 as float = .1 --test with alt var of .2
declare @Dsct3 as float = .15 --test with alt var of .3

select 
	Custname as Customer, Bktitle as Book, Qty, 
	'$ '+str(round(slprice,2),5,2) as SRP, 
	'$ '+str(round(qty*slprice,2),5,2) as Total,
		CASE 
			 WHEN qty < 200 THEN str(round(@Dsct1,3)*100,3,0)+' %'
			 WHEN qty < 400 THEN str(round(@Dsct2,3)*100,3,0)+' %'
			 ELSE  str(round(@Dsct3,3)*100,3,0)+' %'
		END as Discount,
		CASE 
			 WHEN qty < 200 THEN '$ '+str(round((qty*slprice)*(1-@Dsct1),2),5,2)
			 WHEN qty < 400 THEN '$ '+str(round((qty*slprice)*(1-@Dsct2),2),5,2)
			 ELSE  '$ '+str(round((qty*slprice)*(1-@Dsct3),2),5,2)
		END as DiscountPrice
from
	Customers as c, Sales as s, Titles as t
where
	c.custnum=s.custnum and
	s.partnum=t.partnum
order by custname

--Views: create the view first and then apply the variables to it
Create View AllTablesBase
as
SELECT 
	c.*, 
	s.ordnum, sldate, qty, 
	t.*,
	r.lname, fname, commrate,
	qty*slprice as OrderTotal 
FROM Customers as c 
		JOIN Sales as s ON s.custnum = c.custnum 
		JOIN Titles as t ON t.partnum = s.partnum 
		JOIN Slspers as r ON r.repid = c.repid

--test view		
select * from alltablesbase	

--run the variables on the view
declare @Dsct1 as float = 0.05 --test with alt var of .1
declare @Dsct2 as float = 0.10 --test with alt var of .2
declare @Dsct3 as float = 0.15 --test with alt var of .3

select 
	Custname, Bktitle, Qty, slprice, 
	OrderTotal, Discount=
		CASE 
			 WHEN qty < 200 THEN @Dsct1
			 WHEN qty < 400 THEN @Dsct2
			 ELSE  @Dsct3
		END,
	DiscountedTotal= OrderTotal*
		CASE 
			 WHEN qty < 200 THEN 1-@Dsct1
			 WHEN qty < 400 THEN 1-@Dsct2
			 ELSE  1-@Dsct3
		END
from alltablesbase
order by custname

/*You are not able to declare variables in the creation process
of making views but you could create a custom function which 
acts like a view.  Declare variables in the function and set 
values while executing, similar to setting a parameter value.*/
CREATE function dbo.GetDsct
	(@Dsct1 as float, @Dsct2 as float, @Dsct3 as float) 
RETURNS TABLE 
RETURN 
select 
	Custname as Customer, Bktitle as Book, Qty, 
	slprice as SRP, 
	qty*slprice as Total,
		CASE 
			 WHEN qty < 200 THEN @Dsct1
			 WHEN qty < 400 THEN @Dsct2
			 ELSE  @Dsct3
		END as Discount,
		CASE 
			 WHEN qty < 200 THEN (qty*slprice)*(1-@Dsct1)
			 WHEN qty < 400 THEN (qty*slprice)*(1-@Dsct2)
			 ELSE  (qty*slprice)*(1-@Dsct3)
		END as DiscountPrice
from
	Customers as c, Sales as s, Titles as t
where
	c.custnum=s.custnum and
	s.partnum=t.partnum
--  order by qty -- Similar to views, sorts do not work here.

--execute the function and set the variables
SELECT * FROM dbo.GetDsct(.1,.2,.3)
order by qty

--verify structure
sp_help getdsct

--delete function
drop function getdsct

--cleaned up version using conversion functions
CREATE function dbo.GetDsct
	(@Dsct1 as float, @Dsct2 as float, @Dsct3 as float) 
RETURNS TABLE 
RETURN 
select 
	Custname as Customer, Bktitle as Book, Qty, 
	'$ '+str(round(slprice,2),5,2) as SRP, 
	'$ '+str(round(qty*slprice,2),5,2) as Total,
		CASE 
			 WHEN qty < 200 THEN str(round(@Dsct1,3)*100,3,0)+' %'
			 WHEN qty < 400 THEN str(round(@Dsct2,3)*100,3,0)+' %'
			 ELSE  str(round(@Dsct3,3)*100,3,0)+' %'
		END as Discount,
		CASE 
			 WHEN qty < 200 THEN '$ '+str(round((qty*slprice)*(1-@Dsct1),2),5,2)
			 WHEN qty < 400 THEN '$ '+str(round((qty*slprice)*(1-@Dsct2),2),5,2)
			 ELSE  '$ '+str(round((qty*slprice)*(1-@Dsct3),2),5,2)
		END as DiscountPrice
from
	Customers as c, Sales as s, Titles as t
where
	c.custnum=s.custnum and
	s.partnum=t.partnum