USE OLTP

SELECT *
FROM countries

go
CREATE VIEW countries_view as
SELECT country,city
FROM countries
go

SELECT *
FROM countries_view
SELECT Code, Product_line
       ,dbo.findstringto([Product_type/Product],'/') as product_type
	   ,dbo.findstringfrom([Product_type/Product],'/') as product
	   ,Product_cost
FROM products

SELECT Code, Product_line
       ,dbo.findstringto([Product_type/Product],'/') as product_type
	   ,dbo.proper_case(dbo.findstringfrom([Product_type/Product],'/')) as product
	   ,Product_cost
FROM products

go
CREATE VIEW product_view AS
SELECT Code, Product_line
       ,dbo.findstringto([Product_type/Product],'/') as product_type
	   ,dbo.proper_case(dbo.findstringfrom([Product_type/Product],'/')) as product
	   ,Product_cost
FROM products
go

SELECT *
FROM  product_view

--We can also apply the same strategy to sumarising our data
SELECT date,sale_price,quantity_sold
FROM sales

--this is transactional, but for data analysis purposes
--we are likely to be working summary data
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
GROUP BY datepart(year,date)
      ,datepart(month,date)
	  ,datepart(quarter,date)
	  ,datepart(day,date)
ORDER BY datepart(year,date)
      ,datepart(month,date)
	  ,datepart(quarter,date)
	  ,datepart(day,date)

go
CREATE VIEW sales_by_time AS
--Summarising the data
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_sales
FROM sales
GROUP BY datepart(year,date)
      ,datepart(month,date)
	  ,datepart(quarter,date)
	  ,datepart(day,date)
go

SELECT * 
FROM sales_by_time
ORDER BY year,month,quarter,day