USE [HOBBY_SHOP]
GO

DROP TABLE IF EXISTS 
	[SALES].[TRANSACTIONS]
	GO

CREATE TABLE [SALES].[TRANSACTIONS](
	[TransId]			[varchar](64)	NOT NULL,
	[TransDate]			[date]			NOT NULL,
	[CustId]			[varchar](64)	NOT NULL,
	[CustName]			[varchar](64)	NOT NULL,
	[ProdID]			[varchar](64)	NOT NULL,
	[Quantity]			[int]			NOT NULL,
	[WholeSalePrice]	[money]			NOT NULL
) ON [PRIMARY]
GO

 INSERT INTO [SALES].[TRANSACTIONS] VALUES
	('T001','10-01-2018','C001','Acme Hobby Shop','P001',10,20.00),
	('T002','10-02-2018','C001','Acme Hobby Shop','P002',10,50.00),
	('T003','10-03-2018','C001','Acme Hobby Shop','P003',10,75.00),
	('T004','10-04-2018','C001','Acme Hobby Shop','P004',15,100.00),
	('T005','10-05-2018','C001','Acme Hobby Shop','P005',10,125.00),

	('T006','11-01-2018','C002','John''s Hobby Shop','P001',12,20.00),
	('T007','11-02-2018','C002','John''s Hobby Shop','P002',12,50.00),
	('T008','11-03-2018','C002','John''s Hobby Shop','P003',12,75.00),
	('T009','11-04-2018','C002','John''s Hobby Shop','P004',50,100.00),
	('T010','11-05-2018','C002','John''s Hobby Shop','P005',10,125.00),

	('T011','12-01-2018','C003','Central Hobby Shop','P006',8,20.00),
	('T012','12-10-2018','C003','Central Hobby Shop','P007',9,50.00),
	('T013','12-11-2018','C003','Central Hobby Shop','P008',5,75.00),
	('T014','12-21-2018','C003','Central Hobby Shop','P009',6,100.00),
	('T015','12-24-2018','C003','Central Hobby Shop','P010',10,75.00),

	('T016','09-01-2018','C004','Main Street Hobby Shop','P001',7,20.00),
	('T017','09-02-2018','C004','Main Street Hobby Shop','P001',8,20.00),
	('T018','09-07-2018','C004','Main Street Hobby Shop','P002',10,50.00),
	('T019','09-09-2018','C004','Main Street Hobby Shop','P003',10,75.00),
	('T020','09-09-2018','C004','Main Street Hobby Shop','P004',20,100.00),

	('T021','08-01-2018','C005','Euro Trains Hobby Shop','P007',10,50.00),
	('T022','08-01-2018','C005','Euro Trains Hobby Shop','P008',100,75.00),
	('T023','08-01-2018','C005','Euro Trains Hobby Shop','P010',50,75.00),
	('T024','08-01-2018','C005','Euro Trains Hobby Shop','P010',110,75.00),
	('T025','08-01-2018','C005','Euro Trains Hobby Shop','P010',110,75.00);
GO


CREATE TABLE [SALES].[INVOICE](
	[INV_NO] [varchar](32) NULL,
	[INVOICE_DATE] [date] NOT NULL,
	[CUST_ID] [varchar](64) NOT NULL,
	[PROD_ID] [varchar](64) NOT NULL,
	[QUANTITY] [smallint] NULL,
	[UNIT_PRICE] [decimal](10, 2) NULL,
	[TOTAL_PRICE] [money] NULL
) ON [HOBBY_SHOP_DATA_FG]
GO

TRUNCATE TABLE [SALES].[INVOICE]
GO

INSERT INTO [SALES].[INVOICE]
SELECT [TransId]
      ,[TransDate]
      ,[CustId]
      ,[ProdID]
      ,[Quantity]
      ,[WholeSalePrice]
	  ,[Quantity] * [WholeSalePrice]
  FROM [HOBBY_SHOP].[SALES].[TRANSACTIONS]
  GO



DROP TABLE IF EXISTS CUSTOMER
GO

CREATE TABLE [SALES].[CUSTOMER](
	[CUST_ID] [varchar](6) NOT NULL,
	[CUST_NAME] [varchar](256) NOT NULL
) ON [PRIMARY]
GO

INSERT INTO [SALES].[CUSTOMER]
SELECT DISTINCT [CustId],[CustName]
FROM [SALES].[TRANSACTIONS]
ORDER BY [CustId]
GO

	

DROP TABLE IF EXISTS [SALES].[TRANSACTION_HISTORY]
GO


SELECT 
	'T' + C.CustId + P.[ProdID] + 
		+ CONVERT(VARCHAR,[CalendarYear])
		+ CONVERT(VARCHAR,[CalendarMonth])
		+ CONVERT(VARCHAR,[CalendarDay]) AS [TransId],
	CAL.CalendarDate AS TransDate,
	C.[CustId],
    C.[CustName],
	P.[ProdID],
    P.[ProdName],
    P.[RetailPrice],
    P.[WholeSalePrice]
INTO [SALES].[TRANSACTION_HISTORY]
FROM [SALES].[CUSTOMER] C
CROSS JOIN
	[SALES].[PRODUCT] P
CROSS JOIN[SALES].[CALENDAR] CAL
GO


SELECT
	[TransId], 
	[TransDate], 
	[CustId], 
	[CustName], 
	[ProdID], 
	[Quantity], 
	[WholeSalePrice]	
FROM [SALES].[TRANSACTIONS]
GO

/*********************************/
/* Let's create some more tables */
/*********************************/

CREATE TABLE [SALES].[EXPENSIVE_PRODUCTS]
(
ProdName VARCHAR(256) NOT NULL,
RetailPrice MONEY NOT NULL,
WholePrice MONEY NOT NULL
)
GO

/*************************************************/
/* retail price is 10% less than wholesale price */
/*************************************************/

TRUNCATE TABLE [SALES].[EXPENSIVE_PRODUCTS]
GO

INSERT INTO [SALES].[EXPENSIVE_PRODUCTS]
SELECT DISTINCT
	[ProdID],
	[WholeSalePrice] *.9 AS [RetailPrice],
	[WholeSalePrice]
FROM  [SALES].[TRANSACTIONS]
WHERE [WholeSalePrice]  > 50.00
GO

SELECT * FROM [SALES].[EXPENSIVE_PRODUCTS]
GO


CREATE TABLE [SALES].[PRODUCTS]
(
ProdName VARCHAR(256) NOT NULL,
RetailPrice MONEY NOT NULL,
WholePrice MONEY NOT NULL
)
GO

TRUNCATE TABLE [SALES].[PRODUCTS]
GO

INSERT INTO [SALES].[PRODUCTS]
SELECT DISTINCT [ProdID],[WholeSalePrice] *.9,[WholeSalePrice]
FROM  [SALES].[TRANSACTIONS]
GO

SELECT * FROM [SALES].[PRODUCTS]
GO

DROP TABLE [SALES].[PRODUCT]
GO

DROP TABLE 
	IF EXISTS [SALES].[PRODUCT]
	GO

SELECT DISTINCT
	[ProdID],
	CASE	
		WHEN [ProdId] = 'P001' THEN 'N Gauge Electric Locomotive'
		WHEN [ProdId] = 'P002' THEN 'Z Gauge Electric Locomotive'
		WHEN [ProdId] = 'P003' THEN 'HO Gauge Electric Locomotive'
		WHEN [ProdId] = 'P004' THEN 'N Gauge Steam Locomotive'
		WHEN [ProdId] = 'P005' THEN 'Z Gauge Steam Locomotive'
		WHEN [ProdId] = 'P006' THEN 'HO Gauge Steam Locomotive'
		WHEN [ProdId] = 'P007' THEN 'N Gauge Diesel Locomotive'
		WHEN [ProdId] = 'P008' THEN 'HO Gauge Diesel Locomotive'
		WHEN [ProdId] = 'P009' THEN 'Z Gauge Diesel Locomotive'
		WHEN [ProdId] = 'P010' THEN 'N Gauge Passanger Car'
	ELSE 'Unkown Product'	
	END AS ProdName,
    CONVERT(MONEY,[WholeSalePrice] * .75) AS [RetailPrice],
	[WholeSalePrice] -- 25% markup!
INTO [INVENTORY].[PRODUCT]
FROM [SALES].[TRANSACTIONS]
GO


CREATE TABLE [INVENTORY].[PRODUCT_TYPE](
	[PROD_TYPE] [varchar](4) NOT NULL,
	[PROD_TYPE_NAME] [varchar](28) NOT NULL,
	) ON [HOBBY_SHOP_DATA_FG]
GO

INSERT INTO [INVENTORY].[PRODUCT_TYPE]
SELECT DISTINCT 
	'PT00',
	CASE
		WHEN PROD_NAME LIKE '%Electric Locomotive%' THEN 'Electric Locomotive'
		WHEN PROD_NAME LIKE '%Steam Locomotive%' THEN 'Steam Locomotive'
		WHEN PROD_NAME LIKE '%Diesel Locomotive%' THEN 'Diesel Locomotive'
		WHEN PROD_NAME LIKE '%Passanger%' THEN 'Passenger Car'
		ELSE 'TBD'
	END AS PROD_TYPE_NAME
FROM [INVENTORY].[PRODUCT]
GO

/*
PROD_TYPE	PROD_TYPE_NAME
PT01	Diesel Locomotive
PT02	Electric Locomotive
PT03	Passenger Car
PT04	Steam Locomotive
*/

UPDATE [INVENTORY].[PRODUCT]
SET [PROD_TYPE] = 
	CASE
		WHEN PROD_NAME LIKE '%Electric Locomotive%' THEN 'PT02'
		WHEN PROD_NAME LIKE '%Steam Locomotive%' THEN 'PT04'
		WHEN PROD_NAME LIKE '%Diesel Locomotive%' THEN 'PT01'
		WHEN PROD_NAME LIKE '%Passanger%' THEN 'PT03'
		ELSE 'TBD'
	END 
GO

CREATE TABLE [SALES].[CALENDAR] (
	CalendarDate	DATE,
	CalendarYear	SMALLINT,
	CalendarMonth	SMALLINT,
	CalendarDay		SMALLINT
	)
GO

/************************************************************************/
/* Let's create a batch to load the calendar with 9 years worth of data */
/************************************************************************/

DECLARE @CalendarDate DATE;

DECLARE @StartDate    DATE;
DECLARE @EndDate      DATE;

DECLARE @Year        SMALLINT;
DECLARE @Month       SMALLINT;
DECLARE @Day         SMALLINT;

SET @StartDate  = '01-01-2010';
SET @EndDate = '12-31-2019';

SET @CalendarDate = @StartDate;

TRUNCATE TABLE [SALES].[CALENDAR]; -- use carefully

WHILE (@CalendarDate <=  @EndDate)
	BEGIN
	PRINT '================================';
	PRINT 'Today: ' + CONVERT(VARCHAR,@CalendarDate);

	SET @Year = DATEPART(yy,@CalendarDate);
	SET @Month = DATEPART(mm,@CalendarDate);
	SET @Day = DATEPART(dd,@CalendarDate);

	PRINT 'Year: ' + CONVERT(VARCHAR,@Year);
	PRINT 'Month: ' + CONVERT(VARCHAR,@Month);
	PRINT 'Day: ' + CONVERT(VARCHAR,@day);

	PRINT '================================';
	PRINT ''; -- print a blank line

	INSERT INTO [SALES].[CALENDAR] VALUES(@CalendarDate,@Year,@Month,@Day);

	SET @CalendarDate = DATEADD(dd,1,@CalendarDate);
	END
GO