USE OLAP
--Question 1
--In Question 1 of chapter 16 we build the following bar graph
SELECT c.country
       ,replicate(nchar(9608),round(sum(s.profit)/1000,0)) as [profit                                 ]
	   ,FORMAT(sum(s.profit),'C') as value
FROM countries as c JOIN sales_enriched as s ON c.city=s.retailer_city
GROUP BY c.country
ORDER BY profit desc
--We are going to Create a function using defaults to display this graph allowing users to filter
--by country, year and retailer_type to help you out let's do this step by step

--a. To see the data we are working with do a query on the underlying data for Austrlia 2016 Equipment
--Store Rental. You should get 14 rows. If not check the datatypes and expecially spaces.
SELECT *
FROM countries as c JOIN sales_enriched as s ON c.city=s.retailer_city
WHERE c.country = 'Australia'
and datepart(year,date) =2016
and trim(s.retailer_type) = 'Equipment Rental Store'

--b. Add the WHERE clause you used here to our bar chart query. Don't include the order by clause
--as we will add this in later.
SELECT c.country
       ,replicate(nchar(9608),round(sum(s.profit)/1000,0)) as [profit                                 ]
	   ,FORMAT(sum(s.profit),'C') as value
FROM countries as c JOIN sales_enriched as s ON c.city=s.retailer_city
WHERE c.country = 'Australia'
and datepart(year,date) =2016
and trim(s.retailer_type) = 'Equipment Rental Store'
GROUP BY c.country

--c. The scale is too small change it to 1 character for every 10
SELECT c.country
       ,replicate(nchar(9608),round(sum(s.profit)/10,0)) as [profit                                 ]
	   ,FORMAT(sum(s.profit),'C') as value
FROM countries as c JOIN sales_enriched as s ON c.city=s.retailer_city
WHERE c.country = 'Australia'
and datepart(year,date) =2016
and trim(s.retailer_type) = 'Equipment Rental Store'
GROUP BY c.country

--d. Create a function out of this query, which country, year and retailer_type. Make sure you replace 
--the hard-coded values in the where clause with the parameters. Becuase we are working with
--'%' we need to make sure all our parameters are characters (year should be four characters long) and
--we are using like instead of =.
go
CREATE FUNCTION filter_profit_by_country_year_and_retailer_type(
	@country char(120) = '%'
	,@year char(4) = '%'
	,@retailer_type char(120) = '%') returns table AS
RETURN (SELECT c.country
		   ,replicate(nchar(9608),round(sum(s.profit)/1000,0)) as [profit                                 ]
		   ,FORMAT(sum(s.profit),'C') as value
		FROM countries as c JOIN sales_enriched as s ON c.city=s.retailer_city
		WHERE trim(c.country) like trim(@country)
			and cast(datepart(year,date) as char(4)) like trim(@year)
			and trim(s.retailer_type) like trim(@retailer_type)
		GROUP BY c.country)
go
--e. Test each parameter seperately using Australia 2016 'Equipment Rental Store'. The do a final
--test with all three
SELECT * 
FROM filter_profit_by_country_year_and_retailer_type(
	'Australia'
	,default
	,default)
SELECT * 
FROM filter_profit_by_country_year_and_retailer_type(
	default
	,'2016'
	,default)
SELECT * 
FROM filter_profit_by_country_year_and_retailer_type(
	default
	,default
	,'Equipment Rental Store')
SELECT * 
FROM filter_profit_by_country_year_and_retailer_type(
	'Australia'
	,'2016'
	,'Equipment Rental Store')

--Question 2
--a. Nest the previous function inside a procedure to allow for named parameter passing
go
CREATE PROCEDURE call_filter_profit_by_country_year_and_retailer_type
	@country char(120) = '%'
	,@year char(4) = '%'
	,@retailer_type char(120) = '%'
AS
BEGIN
	SELECT * 
	FROM filter_profit_by_country_year_and_retailer_type(@country, @year, @retailer_type)
END
go
--b. Test it, as before, with each parameter and then all of them.
exec call_filter_profit_by_country_year_and_retailer_type @country = 'Australia'
exec call_filter_profit_by_country_year_and_retailer_type @year = '2016'
exec call_filter_profit_by_country_year_and_retailer_type @retailer_type = 'Equipment Rental Store'
exec call_filter_profit_by_country_year_and_retailer_type 
    @country = 'Australia'
	,@year = '2016'
	,@retailer_type = 'Equipment Rental Store'

--Question 3 (Challenge) 
--Using dynamic SQL, allow the user to specify (using a parameter) which column (country or value)
--to sort by. You will need to alter the filter_profit_by_country_year_and_retailer_type function to
--set the scale back to 1000. Test using 2016 results ordering by country, value and value desc. 
--Don't forget to test that it is generating the 
--correct SQL statement first before you try to execute it.
go
ALTER PROCEDURE call_filter_profit_by_country_year_and_retailer_type @country char(120) = '%'
	,@year char(4) =  '%'
	,@retailer_type char(120) =  '%'
	,@order char(10) = 'country'
AS
BEGIN
	declare @sql char(2000) = 
	concat('SELECT * FROM filter_profit_by_country_year_and_retailer_type('''
	,trim(@country),''',''',@year,''',''',trim(@retailer_type),''')'
	,char(13)
	,'ORDER BY ',@order)
	--print @sql
	exec (@sql)
END
go

exec call_filter_profit_by_country_year_and_retailer_type @year = '2016'
														  ,@order = 'country'
exec call_filter_profit_by_country_year_and_retailer_type @year = '2016'
														  ,@order = 'value'
exec call_filter_profit_by_country_year_and_retailer_type @year = '2016'
														  ,@order = 'value desc'