/*********************/
/* CREATE A CALENDAR */
/*********************/

USE [ORGANIZATION]
GO

DROP TABLE IF EXISTS MASTER_DATA.CALENDAR
GO

CREATE TABLE MASTER_DATA.CALENDAR
(
CALENDAR_DATE		DATE NOT NULL,
CALENDAR_YEAR		SMALLINT NOT NULL,
CALENDAR_QUARTER	SMALLINT NOT NULL,
CALENDAR_MONTH		SMALLINT NOT NULL,
CALENDAR_WEEK	    SMALLINT NOT NULL
)
GO

DECLARE @StartDate DATE;
DECLARE @StopDate  DATE;

SET @StartDate = '1-1-2010';
SET @StopDate = '12-31-2020';

WHILE @StartDate <= @StopDate
BEGIN
	PRINT CONVERT(VARCHAR,@StartDate);

	INSERT INTO MASTER_DATA.CALENDAR
	VALUES(
		@StartDate,
		DATEPART(yy,@StartDate),
		DATEPART(qq,@StartDate),
		DATEPART(mm,@StartDate),
		DATEPART(ww,@StartDate)
		);

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

SELECT * FROM MASTER_DATA.CALENDAR
GO

USE [ORGANIZATION]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[SALES].[INVOICE]') AND type in (N'U'))
DROP TABLE [SALES].[INVOICE]
GO

CREATE TABLE [SALES].[INVOICE](
	[CUS_ID] [varchar](6) NOT NULL,
	[INV_ID] [varchar](32) NOT NULL,
	[PROD_ID] [varchar](7) NOT NULL,
	[CALENDAR_DATE] [date] NOT NULL,
	[INV_AMT] [decimal](10, 2) NULL
) ON [PRIMARY]
GO