USE [Cube]
GO

DROP TABLE IF EXISTS DimMovie;
GO

CREATE TABLE [dbo].[DimMovie](
	[movieID] [nvarchar](10) NOT NULL,
	[primaryTitle] [nvarchar](255) NULL,
	[originalTitle] [nvarchar](255) NULL,
	[runtimeMinutes] [int] NULL,
	[runtimeRange] [nvarchar](15) NULL,
	[budget] [int] NULL,
	[budgetRange] [nvarchar](30) NULL,
	[primaryLanguage] [nvarchar](10) NULL
 CONSTRAINT [DimMoviePK] PRIMARY KEY CLUSTERED
(
	[movieID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO DimMovie
SELECT movieID, primaryTitle, originalTitle, runtimeMinutes,
CASE
	WHEN runtimeMinutes <= 60 THEN '0 - 60'
	WHEN runtimeMinutes > 60 AND runtimeMinutes <= 75 THEN '60 - 75'
	WHEN runtimeMinutes > 75 AND runtimeMinutes <= 90 THEN '75 - 90'
	WHEN runtimeMinutes > 90 AND runtimeMinutes <= 105  THEN '90 - 105'
	WHEN runtimeMinutes > 105 AND runtimeMinutes <= 120  THEN '105 - 120'
	WHEN runtimeMinutes > 120 THEN '> 120'
	ELSE 'UNKNOWN'
END as runtimeRange,
budget,
CASE
	WHEN budget <= 1000000 THEN '0 - 1 000 000'
	WHEN budget > 1000000 AND budget <= 5000000 THEN '1 000 000 - 5 000 000'
	WHEN budget > 5000000 AND budget <= 10000000 THEN '5 000 000 - 10 000 000'
	WHEN budget > 10000000 AND budget <= 25000000 THEN '10 000 000 - 25 000 000'
	WHEN budget > 25000000 AND budget <= 50000000  THEN '25 000 000 - 50 000 000'
	WHEN budget > 50000000 AND budget <= 100000000  THEN '50 000 000 - 100 000 000'
	WHEN budget > 100000000 THEN '> 100 000 000'
	ELSE 'UNKNOWN'
END as budgetRange,
ISNULL(primaryLanguage, 'UNKNOWN')
FROM IntermediateDB.dbo.Movie