USE OLTP
--Consider the query from the last chapter
SELECT * 
FROM sales_by_time

--Views can be queried just like tables
SELECT * 
FROM sales_by_time
WHERE year = 2016
ORDER BY month,quarter,day

--Table valued functions
go
CREATE FUNCTION sales_by_time_for_product(@product_code varchar(5)) returns TABLE 
    return (select datepart(year,date) as year,datepart(month,date) as month,datepart(quarter,date) as quarter
				   ,datepart(day,date) as day,sum(sale_price*quantity_sold) as total_sale
			from sales
			where product_code like concat(@product_code,'%') --allow for partical match
			group by datepart(year,date),datepart(month,date),datepart(quarter,date),datepart(day,date))
go

--What are our breakdown of sales for 'F20' for 2016?
SELECT *
FROM sales_by_time_for_product('F20')
WHERE year = 2016
ORDER BY month,quarter,day

--partial matching
SELECT *
FROM sales_by_time_for_product('F')
WHERE year = 2016
ORDER BY month,quarter,day

--blank product
SELECT *
FROM sales_by_time_for_product('')
WHERE year = 2016
ORDER BY month,quarter,day