-- Change the USE statment to your database
--
use tstudentNW;

drop table if exists NWCustomerOLAP;

SELECT identity(int, 1, 1) as RecID, Orders.CustomerID, Customers.CompanyName, Customers.Country, Customers.Region,
	Orders.EmployeeID, CONCAT(Employees.FirstName, ' ', Employees.LastName) as EmployeeName, 
	Orders.ShipVia as ShipperID, Shippers.CompanyName AS ShipperName,
	ROUND(SUM([Order Details].UnitPrice * [Order Details].Quantity),2) as OrderGross,
	ROUND(SUM([Order Details].UnitPrice * [Order Details].Quantity * [Order Details].Discount),2) as OrderDiscount,
	ROUND(SUM([Order Details].UnitPrice * [Order Details].Quantity * (1-[Order Details].Discount)),2) as OrderNet
 INTO NWCustomerOLAP
 FROM Customers INNER JOIN
	Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN
	[Order Details] ON Orders.OrderID = [Order Details].OrderID INNER JOIN
	Employees ON Orders.EmployeeID = Employees.EmployeeID INNER JOIN
	Shippers ON Orders.ShipVia = Shippers.ShipperID
GROUP BY Orders.OrderID, Orders.CustomerID, Customers.CompanyName, Customers.Country, Customers.Region,
	Orders.EmployeeID, CONCAT(Employees.FirstName, ' ', Employees.LastName),
	Orders.ShipVia, Shippers.CompanyName;

-- NOTE: This assumes the NWAnalysts group SQL Server has been added as a login for your database
--       which only needs to happen once and can be accomplished via the SQL
-- CREATE USER [VM-1\NWAnalysts] FOR LOGIN [VM-1\NWAnalysts]
--
grant select on NWCustomerOLAP to [vm-1\nwanalysts];