'Go'附近的SQL语法不正确 [英] SQL- Incorrect Syntax near 'Go'
问题描述
大家好，
我正在尝试在Visual Studio 2012中执行以下SQL脚本：
--DROP DATABASE Sales_DW
GO
创建数据库Sales_DW
GO
使用Sales_DW
GO
- 在数据仓库中创建客户维度表，其中包含客户个人详细信息。
创建表DimCustomer
（
CustomerID int主键标识，
CustomerAltID varchar（10）not null，
CustomerName varchar（50 ），
性别varchar（20）
）
GO
- - 使用样本值填写Customer维度
插入DimCustomer（CustomerAltID，CustomerName，Gender）值
（'IMI-001'， '亨利福特'，'M'），
（'IMI-002'，'比尔盖茨'，'M'），
（'IMI-003'， 'Muskan Shaikh'，'F'），
（'IMI-004'，'Richard Thrubin'，'M'），
（'IMI-005'，'Emma Wattson'，'F'）;
GO
- 创建产品尺寸表的基本级别，不考虑任何类别或子类别。
创建表DimProduct
（
ProductKey int主键标识，
ProductAltKey varchar（10）not null，
ProductName varchar（100），
ProductActualCost money，
ProductSalesCost money
）
GO
- 使用样本值填充产品维度
插入DimProduct（ProductAltKey，ProductName，ProductActualCost，ProductSalesCost）值
（'ITM -001'，'小麦地板1公斤'，5.50,6.50），
（'ITM-002'，'米粒1公斤'，22.50,24），
（ 'ITM-003'，'SunFlower Oil 1 ltr'，42,43.5），
（'ITM-004'，'Nirma Soap'，18,20），
（'ITM-005'，'Arial洗衣粉1公斤'，135,139）;
GO
- 创建商店维度表，其中包含各个地方可用的详细相关商店。
创建表DimStores
（
StoreID int主键标识，
StoreAltID varchar（10）not null，
StoreName varchar（100），
StoreLocation varchar（100），
City varchar（100），
州varchar（100），
国家varchar（100）
）
GO
- 使用样本值填充Store Dimension
插入DimStores（StoreAltID，StoreName，StoreLocation，City，State，Country）值
（'LOC-A1'，'X-Mart'，'SP RingRoad'，'Ahmedabad'，'Guj'，'India'），
（'LOC-A2'，'X-Mart'，'Maninagar'，'Ahmedabad'，'Guj'，'印度'），
（'LOC-A3'，'X-Mart'，'Sivranjani'，'艾哈迈达巴德'，'古吉'，'印度'）;
GO
- 创建尺寸销售人员表，其中包含各个地方可用的详细相关商店。
创建表DimSalesPerson
（
SalesPersonID int主键标识，
SalesPersonAltID varchar（10）非空，
SalesPersonName varchar（100），
StoreID int，
City varchar（100），
State varchar（100），
国家varchar（100）
）
GO
- 填写维度销售人员带样本值
插入DimSalesPerson（SalesPersonAltID，SalesPersonName，StoreID，City，State，Country）值
（'SP-DMSPR1' ， '与Ashish'，1 '艾哈默德巴德'， 'GUJ'，印度'），
（'SP-DMSPR2'，'Ketan'，1，'Ahmedabad'，'Guj'，'India'），
（'SP- DMNGR1'，'Srinivas'，2，'Ahmedabad'，'Guj'，'India'），
（'SP-DMNGR2'，'Saad'，2，'Ahmedabad'，'Guj' ，'印度'），
（'SP-DMSVR1'，'Jasmin'，3，'Ahmedabad'，'Guj'，'India'），
（' SP-DMSVR2'，'雅各布'，3，'艾哈迈达巴德'，'古吉'，'印度'）;
GO
< br $>
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo]。[DimTime] （
[TimeKey] [int] NOT NULL，
[TimeAltKey] [int] NOT NULL，
[Time30] [varchar]（ 8）NOT NULL，
[Hour30] [tinyint] NOT NULL，
[MinuteNumber] [tinyint] NOT NULL，
[SecondNumber] [tinyint] NOT NULL，
[TimeInSecond] [int] NOT NULL，
[HourlyB ucket] varchar（15）not null，
[DayTimeBucketGroupKey] int not null，
[DayTimeBucket] varchar（100）not null
CONSTRAINT [PK_DimTime] PRIMARY KEY CLUSTERED
（
[TimeKey] ASC
）
WITH（PAD_INDEX = OFF， STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON）ON [PRIMARY]
）
ON [PRIMARY]
< br $>
GO
SET ANSI_PADDING OFF
GO
/ *****在Test_DW中创建存储过程并运行SP以使用值填充时间维度**** /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo]。[FillDimTime]
as
BEGIN
- 指定您需要填写时间维度的总小时数
DECLARE @Size INTEGER
--iF @ Size = 32那么这将填充val时间维度时间最长32:59小时
设置@尺寸= 23
DECLARE @hour INTEGER
DECLARE @minute INTEGER
DECLARE @second INTEGER
DECLARE @k INTEGER
DECLARE @TimeAltKey INTEGER
DECLARE @ TimeInSeconds INTEGER
DECLARE @ Time30 varchar（25）
DECLARE @ Hour30 varchar（4）
DECLARE @ Minute30 varchar（4）
DECLARE @ Second30 varchar（4）
DECLARE @HourBucket varchar（15）
DECLARE @HourBucketGroupKey int
DECLARE @DayTimeBucket varchar（100）
DECLARE @DayTimeBucketGroupKey int
SET @hour = 0
SET @minute = 0
SET @second = 0
SET @k = 0
SET @TimeAltKey = 0
WHILE（@ hour< = @Size）
BEGIN
if（@hour< 10）
开始
设置@ Hour30 ='0'+演员（@hour as varchar（10））
end
else
开始
设置@ Hour30 = @hour
结束
- 创建小时桶值
套装@ HourBucket = @ Hour30 +'：00'+' - '+ @ Hour30 +'：59'
WHILE （@minute< = 59）
BEGIN
WHILE（@second< = 59）
BEGIN
设置@TimeAltKey = @hour * 10000 + @分钟* 100 + @秒
设置@TimeInSeconds = @ hour * 3600 + @minute * 60 + @ second
如果@minute< 10
开始
set @ Minute30 ='0'+ cast（@minute as varchar（10））
end
else
begin
set @ Minute30 = @minute
结束
如果@second< 10
开始
set @ Second30 =' 0'+演员（@second as varchar（10））
结束
其他
开始
set @ Second30 = @secon d $ / $
结束
- Time30的Concatenate值
set @ Time30 = @ Hour30 +'：'+ @ Minute30 +'：'+ @ Second30
--DayTimeBucketGroupKey可以用于排序DayTime Bucket正确的顺序
SELECT @DayTimeBucketGroupKey =
CASE
WHEN（@TimeAltKey> = 00000 AND @TimeAltKey< = 25959）那么0
WHEN（@TimeAltKey> = 30000 AND @TimeAltKey< = 65959）然后1
WHEN（@TimeAltKey> = 70000 AND @TimeAltKey< = 85959）那么2
WHEN（@TimeAltKey> = 90000 AND @TimeAltKey < = 115959）那么3
WHEN（@TimeAltKey> = 120000 AND @TimeAltKey< = 135959）那么4
WHEN（@TimeAltKey> = 140000 AND @TimeAltKey< = 155959）然后5
WHEN（@TimeAltKey> = 50000 AND @TimeAltKey< = 175959）那么6
WHEN（@TimeAltKey> = 180000 AND @TimeAltKey< = 235959）那么7
WHEN（@TimeAltKey> = 240000）那么8
结束
--print @ DayTimeBucketGroupKey
- DayTimeBucket时间划分为特定时区因此数据可按行分组，以便按时间分析
SELECT @DayTimeBucket =
CASE
WHEN（@TimeAltKey> = 00000 AND @TimeAltKey< = 25959）那么'深夜（00:00 AM到02:59 AM）'
WHEN（@TimeAltKey> = 30000 AND @TimeAltKey< = 65959）然后'清晨（凌晨03:00至早上6:59）'
WHEN（@TimeAltKey> = 70000 AND @TimeAltKey< = 85959）然后是峰值（早上7点到早上8点59分）'
WHEN（@TimeAltKey> = 90000 AND @TimeAltKey< = 115959）那么'中午（上午9:00至上午11:59）'
WHEN（@TimeAltKey> = 120000 AND @TimeAltKey< = 135959）那么'午餐' （中午12:00到下午13:59）'
WHEN（@TimeAltKey> = 140000 AND @TimeAltKey< = 155959）那么'中午'（下午14:00到下午15:59） ）'
WHEN（@TimeAltKey> = 50000 AND @TimeAltKey< = 175959）那么'PM峰值（16:00 PM到17:59 PM）'
WHEN（@TimeAltKey> = 180000 AND @TimeAltKey< = 235959）那么'晚上（晚上18:00到23:59）'
WHEN（@TimeAltKey> = 240000）那么'前一天深夜（下午24:00至'+演员阵容（@Size as varchar（10））+ '00：00 PM）'
END
- print @ DayTimeBucket
插入DimTime（TimeKey，TimeAltKey，[Time30]，[Hour30]，[MinuteNumber]，[SecondNumber]，[TimeInSecond]，[HourlyBucket]，DayTimeBucketGroupKey ，DayTimeBucket）
VALUES（@ k，@ TimeAltKey，@ Time30，@ hour，@ minute，@ Second，@ TimeInSeconds，@ HourBucket，@ DayTimeBucketGroupKey，@ DayTimeBucket）
SET @second = @second + 1
SET @k = @k + 1
END
SET @minute = @minute + 1
SET @second = 0
END
SET @hour = @hour + 1
SET @minute = 0
END
END
GO
执行[FillDimTime]
GO
开始尝试
DROP TABLE [dbo]。[DimDate]
END TRY
BEGIN CATCH
/ *无动作* /
END CATCH
/ ** ************************************************** ****************************** /
CREATE TABLE [ dbo]。[DimDate]
（[DateKey] INT主键，
[日期] DATETIME，
[FullDateUK] CHAR（10） ， - dd-MM-yyyy格式的日期
[FullDateUSA] CHAR（10）， - MM-dd-yyyy格式的日期
[DayOfMonth] VARCHAR（ 2）， - 场将举行每月的数量
[DaySuffix] VARCHAR（4）， - 将后缀应用为第1，第2，第3等
[DayName] VARCHAR（9）， - 包含当天的名字，星期日，星期一
[DayOfWeekUSA] CHAR（1）， - 第一天星期日= 1和星期六= 7
[DayOfWeekUK] CHAR（1） ， - 第一天星期一= 1和星期日= 7
[DayOfWeekInMonth] VARCHAR（2）， - 第1个星期一或第2个星期一月份
[DayOfWeekInYear] VARCHAR（ 2），
[DayOfQuarter] VARCHAR（3），
[DayOfYear] VARCHAR（3），
[WeekOfMonth] VARCHAR（1） ， - 周的月数
[WeekOfQuarter] VARCHAR（2）， - 每季度的数字
[WeekOfYear] VARCHAR（2）， - 周数年度
[月] VARCHAR（2）， - 月份1到12的数量
[MonthName] VARCHAR（9）， - 1月，2月等
[MonthOfQuarter] VARCHAR（2）， - 月份数属于季度
[季度] CHAR（1），
[QuarterName] VARCHAR（9）， - 第一，第二..
[年] CHAR（4）， - 存储在行中的日期年值
[年名] CHAR（7）， - 2012年，2012年，2013年
[MonthYear] CHAR（10）， - 2013年2月，2013年2月
[MMYYYY] CHAR（6），
[FirstDayOfMonth] DATE ，
[LastDayOfMonth] DATE，
[FirstDayOfQuarter] DATE，
[LastDayOfQuarter] DATE，
[FirstDayOfYear ]日期，
[LastDayOfYear]日期，
[IsHolidayUSA] BIT， - 国旗1 =国定假日，0-无国定假日
[IsWeekday] BIT， - 0 =周末，1 =周日
[HolidayUSA] VARCHAR（50）， - 美国度假名称
[IsHolidayUK] BIT Null， - 国旗1 =国定假日，0-无国定假日
[HolidayUK] VARCHAR（50）Null - 英国假日名称
）
GO
/ ****************** ************************************************** ************************ /
- 在此指定开始日期和结束日期
--Value开始日期必须小于您的结束日期
DECLARE @StartDate DATETIME = '01 / 01/2013' - 日期范围的起始值
DECLARE @EndDate DATETIME = '01 / 01/2015' - 日期范围的结束价格
- 临时变量在每个处理过程中保持值年度日期
DECLARE
@DayOfWeekInMonth INT，
@DayOfWeekInYear INT，
@DayOfQuarter INT，
@WeekOfMonth INT，
@CurrentYear INT，
@CurrentMonth INT，
@CurrentQuarter INT
/ *表数据类型，用于存储月份和年份的星期几* /
DECLARE @DayOfWeek TABLE（DOW INT，MonthCount INT，QuarterCount INT，YearCount INT）
INSERT INTO @DayOfWeek VALUES（1,0,0,0）
INSERT INTO @DayOfWeek VALUES（2 ，0,0,0）
插入@DayOfWeek值（3,0,0,0）
插入@DayOfWeek值（4,0,0,0） ）
INSERT INTO @DayOfWeek VALUES（5,0,0,0）
INSERT INTO @DayOfWeek VALUES（6,0,0,0）
INSERT INTO @DayOfWeek VALUES（7,0,0,0）
- 提取并将当前日期的部分值分配给变量
DECLARE @CurrentDate AS DATETIME = @StartDate
SET @CurrentMonth = DATEPART（MM，@ CurrentDate）
SET @CurrentYear = DATEPART（YY， @CurrentDate）
SET @CurrentQuarter = DATEPART（QQ，@ CurrentDate）
/ *********** ************************************************** *********************************** /
- 仅在开始日期（当前日期）小于你上面指定的结束日期
WHILE @CurrentDate< @EndDate
BEGIN
/ *开始星期几逻辑* /
/ *如果月份更改则检查当前日期月份的变化
更改变量值* /
IF @CurrentMonth！= DATEPART（MM，@ CurrentDate）
BEGIN
更新@DayOfWeek
SET MonthCount = 0
SET @CurrentMonth = DATEPART（MM，@ CurrentDate ）
结束
/ *如果季度变化则检查当前日期的季度变化然后更改
可变值* /
如果@CurrentQuarter！= DATEPART（QQ，@ CurrentDate）
BEGIN
UPDATE @DayOfWeek
SET QuarterCount = 0
SET @CurrentQuarter = DATEPART（QQ，@ CurrentDate）
结束
/ *如果年份发生变化，请检查当前日期年份的变化然后更改
变量值* /
IF @CurrentYear！= DATEPART（YY，@ CurrentDate）
BEGIN
更新@DayOfWeek
SET YearCount = 0
SET @CurrentYear = DATEPART（YY，@ CurrentDate）
END
- - 从变量设置上面创建的表数据类型中的值
更新@DayOfWeek
SET
MonthCount = MonthCount + 1，
QuarterCount = QuarterCount + 1，
YearCount = YearCount + 1
WHERE DOW = DATEPART（DW，@ CurrentDate）
SELECT
@DayOfWeekInMonth = MonthCount，
@DayOfQuarter = QuarterCount，
@ DayOfWeekInYear = YearCount
FROM @DayOfWeek
WHERE DOW = DATEPART（DW，@ CurrentDate）
/ *结束日期逻辑* /
/ *使用值填充维度表* /
INSERT INTO [dbo]。[DimDate]
SELECT
CONVERT（char（8），@ CurrentDate，112）as DateKey，
@CurrentDate AS Date，
CONVERT（ char（10），@ CurrentDate，103）作为FullDateUK，
CONVERT（char（10），@ CurrentDate，101）作为FullDateUSA，
DATEPART（DD，@ CurrentDate ）AS DayOfMonth，
- 应用后缀值，如1st，2nd 3rd等..
CASE
WHAT DATEPART（DD，@ CurrentDate） IN（11,12,13）那么CAST（DATEPART（DD，@ CurrentDate）AS VARCHAR）+'th'
当正确（DATEPART（DD，@ CurrentDate），1）= 1那么CAST （DATEPART（DD，@ CurrentDate）AS VARCHAR）+'st'
当右（DATEPART（DD，@ CurrentDate），1）= 2那么CAST（DATEPART（DD，@ CurrentDate）AS VARCHAR ）+'nd'
当右（DATEPART（DD，@ CurrentDate），1）= 3那么CAST（DATEPART（DD，@ CurrentDate）AS VARCHAR）+'rd'
ELSE CAST（DATEPART（DD，@ CurrentDate）AS VARCHAR）+'th'
END作为DaySuffix，
DATENAME（ DW，@ CurrentDate）AS Day姓名，
DATEPART（DW，@ CurrentDate）作为DayOfWeekUSA，
- 按照每个美国检查星期几并按英国格式更改
CASE DATEPART（DW，@ CurrentDate）
当1那么7
当2那么1
当3那么2
当4那么3
当5那么4
当6那么5
当7那么6
结束
作为DayOfWeekUK，
@DayOfWeekInMonth作为DayOfWeekInMonth，
@DayOfWeekInYear作为DayOfWeekInYear，
@DayOfQuarter AS DayOfQuarter，
DATEPART（DY，@ CurrentDate）AS DayOfYear，
DATEPART（WW，@ CurrentDate）+ 1 - DATEPART（ WW，CONVERT（VARCHAR，DATEPART（MM，@ CurrentDate））+'/ 1 /'+ CONVERT（VARCHAR，DATEPART（YY，@ CurrentDate）））AS WeekOfMonth，
（DATEDIFF（DD， DATEADD（QQ，DATEDIFF（QQ，0，@ CurrentDate），0），@ CurrentDate）/ 7）+ 1 AS WeekOfQuarter，
DATEPART（WW，@ CurrentDate）AS WeekOfYear，
DATEPART（MM，@ CurrentDate）AS月，
DATENAME（MM，@ CurrentDate）AS MonthName，
CASE
WHAT DATEPART（MM，@ CurrentDate）IN（1,4,7,10）那么1
当DATEPART（MM，@ CurrentDate）IN（2,5,8,11）那么2
WHAT DATEPART（MM，@ CurrentDate）IN（3,6,9,12）那么3
END作为MonthOfQuarter，
DATEPART（QQ ，@ CurrentDate）AS Quarter，
CASE DATEPART（QQ，@ CurrentDate）
WHEN 1那么'First'
当2'那么'秒'
当3那么'第三'
当4那么'第四'
结束作为QuarterName，
DATEPART （年，@ CurrentDate）AS年，
'CY'+ CONVERT（VARCHAR，DATEPART（年份，@ CurrentDate））AS年名，
LEFT（DATENAME（MM， @CurrentDate），3）+' - '+ CONVERT（VARCHAR，DATEPART（YY，@ CurrentDate））AS MonthYear，
RIGHT（'0'+ CONVERT（VARCHAR，DATEPART（MM，@ CurrentDate） ）），2）+ CONVERT（VARCHA R，DATEPART（YY，@ CurrentDate））AS MMYYYY，
CONVERT（DATETIME，CONVERT（DATE，DATEADD（DD， - （DATEPART（DD，@ CurrentDate） - 1），@ CurrentDate）） ）AS FirstDayOfMonth，
CONVERT（DATETIME，CONVERT（日期，DATEADD（DD， - （DATEPART（DD，（DATEADD（MM，1，@ CurrentDate）））），DATEADD（MM，1， @CurrentDate））））AS LastDayOfMonth，
DATEADD（QQ，DATEDIFF（QQ，0，@ CurrentDate），0）AS FirstDayOfQuarter，
DATEADD（QQ，DATEDIFF（ QQ，-1，@ CurrentDate）， - 1）AS LastDayOfQuarter，
CONVERT（DATETIME，'01/01 /'+ CONVERT（VARCHAR，DATEPART（YY，@ CurrentDate）））AS FirstDayOfYear，
CONVERT（DATETIME，'12 / 31 /'+ CONVERT（VARCHAR，DATEPART（YY，@ CurrentDate）））AS LastDayOfYear，
NULL AS IsHolidayUSA，
CASE DATEPART（DW，@ CurrentDate）
当1那么0
当2那么1
当3那么1
当4那么1
当5那么1
当6那么1
当7那么0
结束为IsWeekday，
NULL作为HolidayUSA，Null，Null
SET @CurrentDate = DATEADD（DD，1，@ CurrentDate）
结束
/ * Add HOLIDAYS UK * /
- 4月18日星期五
更新[dbo]。[DimDate]
SET HolidayUK ='耶稣受难日'
WHERE [月] = 4和[DayOfMonth] = 18
- 复活节4月21日星期一
UPDATE [dbo]。[DimDate]
SET HolidayUK ='复活节星期一'
WHERE [月] = 4 AND [DayOfMonth] = 21
- 五月初银行假期5月5日
更新[dbo]。[DimDate]
SET HolidayUK ='5月初银行假日'
地点[月] = 5和[DayOfMonth] = 5
- 春季银行假期5月26日
更新[dbo]。[DimDate]
SET HolidayUK ='春天银行假日'
WHERE [月] = 5和[DayOfMonth] = 26
- 夏季银行假日8月25日
更新[dbo]。[DimDate]
SET HolidayUK ='夏季银行假期'
哪里[月] = 8和[DayOfMonth] = 25
- 节礼日12月26日
更新[dbo]。[DimDate]
SET HolidayUK ='节礼日'
WHERE [月] = 12和[DayOfMonth] = 26
- CHRISTMAS
UPDATE [dbo ]。[DimDate]
SET HolidayUK ='圣诞节'
WHERE [月] = 12和[DayOfMonth] = 25
- 元旦
UPDATE [dbo]。[DimDate]
SET HolidayUK ='新年''
WHERE [月] = 1 AND [DayOfMonth] = 1
UPDATE [dbo]。[DimDate]
SET IsHolidayUK =当HolidayUK为空时为0当HolidayUK不为空时那么1结束
/ *添加假日美国* /
/ *感恩节 - 11月的第四个星期四* /
UPDATE [dbo]。[DimDate]
SET HolidayUSA ='感恩节'
WHERE
[月] = 11
和[DayOfWeekUSA] = '星期四'
和DayOfWeekInMonth = 4
/ * CHRISTMAS * /
UPDATE [dbo]。[ DimDate]
SET HolidayUSA ='圣诞节'
WHERE [月] = 12和[DayOfMonth] = 25
/ * 7月4日* /
UPDATE [dbo]。[DimDate]
SET HolidayUSA ='独立日'
WHERE [月] = 7和[DayOfMonth] = 4
/ *元旦* /
更新[dbo]。[DimDate]
SET HolidayUSA ='新年''
WHERE [月] = 1和[DayOfMonth] = 1
/ *阵亡将士纪念日 - 5月上周一* /
更新[dbo]。[DimDate]
SET HolidayUSA ='阵亡将士纪念日'
来自[dbo]。[DimDate]
WHERE DateKey IN
（
SELECT
M AX（DateKey）
来自[dbo]。[DimDate]
WHERE
[MonthName] ='May'
AND [DayOfWeekUSA] ='星期一'
GROUP BY
[年]，
[月]
）
/ *劳动节 - 九月的第一个星期一* /
UPDATE [dbo]。[DimDate]
SET HolidayUSA ='劳动节'
来自[dbo]。[DimDate]
WHERE DateKey IN
（
SELECT
MIN（DateKey）
来自[dbo]。[DimDate]
WHERE
[MonthName] ='九月'
AND [DayOfWeekUSA] ='星期一'
GROUP BY
[年]，
[月]
）
/ *情人节* /
UPDATE [dbo]。 [DimDate]
SET HolidayUSA ='情人节'
WHERE
[月] = 2
和[DayOfMonth] = 14
/ *圣帕特里克节* /
UPDATE [dbo]。[DimDate]
SET HolidayUSA ='圣帕特里克节'
WHERE
[月] = 3
AND [DayOfMonth] = 17
/ * Martin Luthor King Day - 1983年1月的第三个星期一* /
UPDATE [dbo]。[DimDate]
SET HolidayUSA ='Martin Luthor King Jr Day'
WHERE
[月] = 1
和[DayOfWeekUSA] ='星期一'
和[年]> = 1983
和DayOfWeekInMonth = 3
/ *总统日 - 2月的第三个星期一* /
UPDATE [dbo]。[DimDate]
SET HolidayUSA ='总统'日'
WHERE
[月] = 2
和[DayOfWeekUSA] ='星期一'
和DayOfWeekInMonth = 3
/ *母亲节 - 五月的第二个星期日* /
UPDATE [dbo ] [DimDate]
SET HolidayUSA ='母亲节'
WHERE
[月] = 5
和[DayOfWeekUSA] ='星期天'
和DayOfWeekInMonth = 2
/ *父亲节 - 六月的第三个星期日* /
UPDATE [dbo]。[DimDate]
SET HolidayUSA ='父亲节'
WHERE
[月] = 6
和[DayOfWeekUSA] ='星期天'
和DayOfWeekInMonth = 3
/ *万圣节10/31 * /
更新[dbo]。[DimDate]
SET HolidayUSA ='万圣节'
WHERE
[月] = 10
和[DayOfMonth] = 31
/ *选举日 - 11月第一个星期一之后的第一个星期二* /
BEGIN
DECLARE @Holidays TABLE（ID INT IDENTITY（1,1）， DateID int，Week TINYINT，YEAR CHAR（4），DAY CHAR（2））
INSERT INTO @Holidays（DateID，[Year]，[Day]）
SELECT
DateKey，
[年]，
[DayOfMonth]
FROM [D b o]。[DimDate]
WHERE
[月] = 11
和[DayOfWeekUSA] ='星期一'
订购
年份，
DayOfMonth
DECLARE @CNTR INT，@ POS INT，@ STARTYEAR INT，@ ENDYEAR INT，@ MINDAY INT
SELECT
@CURRENTYEAR = MIN（[Year]）
，@ STARTYEAR = MIN（[年]）
，@ ENDYEAR = MAX（[年]）
来自@Holidays
WHILE @CURRENTYEAR< = @ENDYEAR
BEGIN
SELECT @CNTR = COUNT（[年]）
FROM @Holidays
WHERE [年] = @CURRENTYEAR
SET @POS = 1
WHILE @POS< = @CNTR
BEGIN
SELECT @MINDAY = MIN（DAY）
来自@Holidays
WHERE
[年] = @CURRENTYEAR
和[周] IS NULL
UPDATE @Holidays
SET [周] = @POS
WHERE
[年] = @CURRENTYEAR
和[日] = @MINDAY
SELECT @POS = @POS + 1
END
SELECT @CURRENTYEAR = @CURRENTYEAR + 1
END
UPDATE [dbo]。[DimDate]
SET HolidayUSA ='选举日'
来自[dbo] ]。[DimDate] DT
加入@Holidays HL ON（HL.DateID + 1）= DT.DateKey
WHERE
[周] = 1
结束
UPDATE [dbo]。[DimDate]
SET IsHolidayUSA =假日美国的情况NULL那么0当HolidayUSA不为空时那么1结束
/ ********************** ************************************************** ************************************************** *********************************/
- 从DimDate中选择*
- 日期维度中的脚本2会计日历设置
/ ** ************************************************** ************************************************** ******************* /
SELECT * FROM [dbo]。[DimDate]
/ *将财政日期列添加到DimDate * /
ALTER TABLE [dbo]。[DimDate] ADD
[FiscalDayOfYear] VARCHAR（3），
[FiscalWeekOfYear] VARCHAR（3），
[FiscalMonth] VARCHAR（2），
[FiscalQuarter] CHAR（1），
[FiscalQuarterName] VARCHAR（9），
[FiscalYear] CHAR（4），
[FiscalYearName] CHAR（7），
[FiscalMonthYear ] CHAR（10），
[FiscalMMYYYY] CHAR（6），
[FiscalFirstDayOfMonth] DATE，
[FiscalLastDayOfMonth] DATE，
[FiscalFirstDayOfQuarter] DATE，
[FiscalLastDayOfQuarter] DATE，
[FiscalFirstDayOfYear] DATE，
[FiscalLastDayOfYear] DATE < br $>
GO
/ ***************** ***** ************************************************** ************************************************** *****************************
需要填充以下部分来定义财务日历
******************************************* ************************************************** ************************************************** ******** /
DECLARE
@dtFiscalYearStart SMALLDATETIME ='1995年1月1日'，
@FiscalYear INT = 1995，
@LastYear INT = 2025，
@FirstLeapYearInPeriod INT = 1996
/ *********************************************** ************************************************** ************************************************** **** /
DECLARE
@iTemp INT，
@LeapWeek INT，
@CurrentDate DATETIME，
@FiscalDayOfYear INT，
@FiscalWeekOfYear INT，
@FiscalMonth INT，
$ b$b \t@FiscalQuarter INT,
\t@FiscalQuarterName VARCHAR(10),
\t@FiscalYearName VARCHAR(7),
\t@LeapYear INT,
\t@FiscalFirstDayOfYear DATE,
\t@FiscalFirstDayOfQuarter DATE,
\t@FiscalFirstDayOfMonth DATE,
\t@FiscalLastDayOfYear DATE,
\t@FiscalLastDayOfQuarter DATE,
\t@FiscalLastDayOfMonth DATE
/*Holds the years that have 455 in last quarter*/
DECLARE @LeapTable TABLE (leapyear INT)
/*TABLE to contain the fiscal year calendar*/
DECLARE @tb TABLE(
\tPeriodDate DATETIME,
\t[FiscalDayOfYear] VARCHAR(3),
\t[FiscalWeekOfYear] VARCHAR(3),
\t[FiscalMonth] VARCHAR(2),
\t[FiscalQuarter] VARCHAR(1),
\t[FiscalQuarterName] VARCHAR(9),
\t[FiscalYear] VARCHAR(4),
\t[FiscalYearName] VARCHAR(7),
\t[FiscalMonthYear] VARCHAR(10),
\t[FiscalMMYYYY] VARCHAR( 6),
\t[FiscalFirstDayOfMonth] DATE,
\t[FiscalLastDayOfMonth] DATE,
\t[FiscalFirstDayOfQuarter] DATE,
\t[FiscalLastDayOfQuarter] DATE,
\t[FiscalFirstDayOfYear] DATE,
\t[FiscalLastDayOfYear] DATE)
/*Populate the table with all leap years*/
SET @LeapYear = @FirstLeapYearInPeriod
WHILE (@LeapYear < @LastYear)
\tBEGIN
\t\tINSERT INTO @leapTable VALUES (@LeapYear)
\t\tSET @LeapYear = @LeapYear + 5
\tEND
/*Initiate parameters before loop*/
SET @CurrentDate = @dtFiscalYearStart
SET @FiscalDayOfYear = 1
SET @FiscalWeekOfYear = 1
SET @FiscalMonth = 1
SET @FiscalQuarter = 1
SET @FiscalWeekOfYear = 1
IF (EXISTS (SELECT * FROM @LeapTable WHERE @FiscalYear = leapyear))
\tBEGIN
\t\tSET @LeapWeek = 1
\tEND
\tELSE
\tBEGIN
\t\tSET @LeapWeek = 0
\tEND
/*******************************************************************************************************************************************************/
/* Loop on days in interval*/
WHILE (DATEPART(yy,@CurrentDate) <= @LastYear)
BEGIN
\t
/*S ET fiscal Month*/
\tSELECT @FiscalMonth = CASE
\t\t/*Use this section for a 4-5-4 calendar. Every leap year the result will be a 4-5-5*/
\t\tWHEN @FiscalWeekOfYear BETWEEN 1 AND 4 THEN 1 /*4 weeks*/
\t\tWHEN @FiscalWeekOfYear BETWEEN 5 AND 9 THEN 2 /*5 weeks*/
\t\tWHEN @FiscalWeekOfYear BETWEEN 10 AND 13 THEN 3 /*4 weeks*/
\t\tWHEN @FiscalWeekOfYear BETWEEN 14 AND 17 THEN 4 /*4 weeks*/
\t\tWHEN @FiscalWeekOfYear BETWEEN 18 AND 22 THEN 5 /*5 weeks*/
\t\tWHEN @FiscalWeekOfYear BETWEEN 23 AND 26 THEN 6 /*4 weeks*/
\t\tWHEN @FiscalWeekOfYear BETWEEN 27 AND 30 THEN 7 /*4 weeks*/
\t\tWHEN @FiscalWeekOfYear BETWEEN 31 AND 35 THEN 8 /*5 weeks*/
\t\tWHEN @FiscalWeekOfYear BETWEEN 36 AND 39 THEN 9 /*4 weeks*/
\t\tWHEN @FiscalWeekOfYear BETWEEN 40 AND 43 THEN 10 /*4 weeks*/
\t\tWHEN @FiscalWeekOfYear BETWEEN 44 AND (48+@LeapWeek) THEN 11 /*5 weeks*/
\t\tWHEN @FiscalWeekOfYear BETWEEN (49+@LeapWeek) AND (52+@LeapWeek) THEN 12 /*4 weeks (5 weeks on leap year)*/
\t\t
$ b$b \t\t/*Use this section for a 4-4-5 calendar. Every leap year the result will be a 4-5-5*/
\t\t/*
\t\tWHEN @FiscalWeekOfYear BETWEEN 1 AND 4 THEN 1 /*4 weeks*/
\t\tWHEN @FiscalWeekOfYear BETWEEN 5 AND 8 THEN 2 /*4 weeks*/
\t\tWHEN @FiscalWeekOfYear BETWEEN 9 AND 13 THEN 3 /*5 weeks*/
\t\tWHEN @FiscalWeekOfYear BETWEEN 14 AND 17 THEN 4 /*4 weeks*/
\t\tWHEN @FiscalWeekOfYear BETWEEN 18 AND 21 THEN 5 /*4 weeks*/
\t\tWHEN @FiscalWeekOfYear BETWEEN 22 AND 26 THEN 6 /*5 weeks*/
\t\tWHEN @FiscalWeekOfYear BETWEEN 27 AND 30 THEN 7 /*4 weeks*/
\t\tWHEN @FiscalWeekOfYear BETWEEN 31 AND 34 THEN 8 /*4 weeks*/
\t\tWHEN @FiscalWeekOfYear BETWEEN 35 AND 39 THEN 9 /*5 weeks*/
\t\tWHEN @FiscalWeekOfYear BETWEEN 40 AND 43 THEN 10 /*4 weeks*/
\t\tWHEN @FiscalWeekOfYear BETWEEN 44 AND (47+@leapWeek) THEN 11 /*4 weeks (5 weeks on leap year)*/
\t\tWHEN @FiscalWeekOfYear BETWEEN (48+@leapWeek) AND (52+@leapWeek) THEN 12 /*5 weeks*/
\t\t*/
\tEND
\t/*SET Fiscal Quarter*/
\tSELECT @FiscalQuarter = CASE
\t\tWHEN @FiscalMonth BETWEEN 1 AND 3 THEN 1
\t\tWHEN @FiscalMonth BETWEEN 4 AND 6 THEN 2
\t\tWHEN @FiscalMonth BETWEEN 7 AND 9 THEN 3
\t\tWHEN @FiscalMonth BETWEEN 10 AND 12 THEN 4
\tEND
\t
\tSELECT @FiscalQuarterName = CASE
\t\tWHEN @FiscalMonth BETWEEN 1 AND 3 THEN ’First’
\t\tWHEN @FiscalMonth BETWEEN 4 AND 6 THEN ’Second’
\t\tWHEN @FiscalMonth BETWEEN 7 AND 9 THEN ’Third’
\t\tWHEN @FiscalMonth BETWEEN 10 AND 12 THEN ’Fourth’
\tEND
\t
\t/*Set Fiscal Year Name*/
\tSELECT @FiscalYearName = ’FY ’ + CONVERT(VARCHAR, @FiscalYear)
\tINSERT INTO @tb (PeriodDate, FiscalDayOfYear, FiscalWeekOfYear, fiscalMonth, FiscalQuarter, FiscalQuarterName, FiscalYear, FiscalYearName) VALUES
\t(@Cu rrentDate, @FiscalDayOfYear, @FiscalWeekOfYear, @FiscalMonth, @FiscalQuarter, @FiscalQuarterName, @FiscalYear, @FiscalYearName)
\t/*SET next day*/
\tSET @CurrentDate = DATEADD(dd, 1, @CurrentDate)
\tSET @FiscalDayOfYear = @FiscalDayOfYear + 1
\tSET @FiscalWeekOfYear = ((@FiscalDayOfYear-1) / 7) + 1
\tIF (@FiscalWeekOfYear > (52+@LeapWeek))
\tBEGIN
\t\t/*Reset a new year*/
\t\tSET @FiscalDayOfYear = 1
\t\tSET @FiscalWeekOfYear = 1
\t\tSET @FiscalYear = @FiscalYear + 1
\t\tIF ( EXISTS (SELECT * FROM @leapTable WHERE @FiscalYear = leapyear))
\t\tBEGIN
\t\t\tSET @LeapWeek = 1
\t\tEND
\t\tELSE
\t\tBEGIN
\t\t\tSET @LeapWeek = 0
\t\tEND
\tEND
END
/*******************************************************************************************************************************************************/
/*Set first and last days of the fiscal months*/
UPDATE @tb
SET
\tFiscalFirstDayOfMonth = minmax.StartDate,
\tFiscalLastDayOfMonth = minmax.EndDate
FROM
@tb t,
\t(
\tSELECT FiscalMonth, FiscalQuarter, FiscalYear, MIN(PeriodDate) AS StartDate, MAX(PeriodDate ) AS EndDate
\tFROM @tb
\tGROUP BY FiscalMonth, FiscalQuarter, FiscalYear
\t) minmax
WHERE
\tt.FiscalMonth = minmax.FiscalMonth AND
\tt.FiscalQuarter = minmax.FiscalQuarter AND
\tt.FiscalYear = minmax.FiscalYear
/*Set first and last days of the fiscal quarters*/
UPDATE @tb
SET
\tFiscalFirstDayOfQuarter = minmax.StartDate,
\tFiscalLastDayOfQuarter = minmax.EndDate
FROM
@tb t,
\t(
\tSELECT FiscalQuarter, FiscalYear, min(PeriodDate) as StartDate, max(PeriodDate) as EndDate
\tFROM @tb
\tGROUP BY FiscalQuarter, FiscalYear
\t) minmax
WHERE
\tt.FiscalQuarter = minmax.FiscalQuarter AND
\tt.FiscalYear = minmax.FiscalYear
/*Set first and last days of the fiscal years*/
UPDATE @tb
SET
\tFiscalFirstDayOfYea r = minmax.StartDate,
\tFiscalLastDayOfYear = minmax.EndDate
FROM
@tb t,
\t(
\tSELECT FiscalYear, min(PeriodDate) as StartDate, max(PeriodDate) as EndDate
\tFROM @tb
\tGROUP BY FiscalYear
\t) minmax
WHERE
\tt.FiscalYear = minmax.FiscalYear
/*Set FiscalYearMonth*/
UPDATE @tb
SET
\tFiscalMonthYear =
\t\tCASE FiscalMonth
\t\tWHEN 1 THEN ’Jan’
\t\tWHEN 2 THEN ’Feb’
\t\tWHEN 3 THEN ’Mar’
\t\tWHEN 4 THEN ’Apr’
\t\tWHEN 5 THEN ’May’
\t\tWHEN 6 THEN ’Jun’
\t\tWHEN 7 THEN ’Jul’
\t\tWHEN 8 THEN ’Aug’
\t\tWHEN 9 THEN ’Sep’
\t\tWHEN 10 THEN ’Oct’
\t\tWHEN 11 THEN ’Nov’
\t\tWHEN 12 THEN ’Dec’
\t\tEND + ’-’ + CONVERT(VARCHAR, FiscalYear)
/*Set F iscalMMYYYY*/
UPDATE @tb
SET
\tFiscalMMYYYY = RIGHT(’0’ + CONVERT(VARCHAR, FiscalMonth),2) + CONVERT(VARCHAR, FiscalYear)
/*******************************************************************************************************************************************************/
UPDATE [dbo].[DimDate]
\tSET
\tFiscalDayOfYear = a.FiscalDayOfYear
\t, FiscalWeekOfYear = a.FiscalWeekOfYear
\t, FiscalMonth = a.FiscalMonth
\t, FiscalQuarter = a.FiscalQuarter
\t, FiscalQuarterName = a.FiscalQuarterName
\t, FiscalYear = a.FiscalYear
\t, FiscalYearName = a.FiscalYearName
\t, FiscalMonthYear = a.FiscalMonthYear
\t, FiscalMMYYYY = a.FiscalMMYYYY
\t, FiscalFirstDayOfMonth = a.FiscalFirstDayOfMonth
\t, FiscalLastDayOfMonth = a.FiscalLastDayOfMonth
\t, FiscalFirstDayOfQuarter = a.FiscalFirstDayO fQuarter
\t, FiscalLastDayOfQuarter = a.FiscalLastDayOfQuarter
\t, FiscalFirstDayOfYear = a.FiscalFirstDayOfYear
\t, FiscalLastDayOfYear = a.FiscalLastDayOfYear
FROM @tb a
\tINNER JOIN [dbo].[DimDate] b ON a.PeriodDate = b.[Date]
/*******************************************************************************************************************************************************/
Create Table FactProductSales
(
TransactionId bigint primary key identity,
SalesInvoiceNumber int not null,
SalesDateKey int,
SalesTimeKey int,
SalesTimeAltKey int,
StoreID int not null,
CustomerID int not null,
ProductID int not null,
SalesPersonID int not null,
Quantity float,
SalesTotalCost money,
ProductActualCost money,
Deviation floa t
)
GO
--Add Relation between Fact table and dimension tables.
-- Add relation between fact table foreign keys to Primary keys of Dimensions
AlTER TABLE FactProductSales ADD CONSTRAINT FK_StoreID FOREIGN KEY (StoreID)REFERENCES DimStores(StoreID);
AlTER TABLE FactProductSales ADD CONSTRAINT FK_CustomerID FOREIGN KEY (CustomerID)REFERENCES Dimcustomer(CustomerID);
AlTER TABLE FactProductSales ADD CONSTRAINT FK_ProductKey FOREIGN KEY (ProductID)REFERENCES Dimproduct(ProductKey);
AlTER TABLE FactProductSales ADD CONSTRAINT FK_SalesPersonID FOREIGN KEY (SalesPersonID)REFERENCES Dimsalesperson(SalesPersonID);
GO
AlTER TABLE FactProductSales ADD CONSTRAINT FK_SalesDateKey FOREIGN KEY (SalesDateKey)REFERENCES DimDate(DateKey);
GO
AlTER TABLE FactProductSales ADD CONSTRAINT FK_SalesTimeKey FOREIGN KEY (SalesTimeKey)REFERENCES DimTIME(TimeKey);
$b $b GO
--Populate your Fact table with historical transaction values of sales for previous day, with proper values of dimension key values.
Insert into FactProductSales(SalesInvoiceNumber,SalesDateKey,SalesTimeKey,SalesTimeAltKey,StoreID,CustomerID,ProductID ,SalesPersonID,Quantity,ProductActualCost,SalesTotalCost,Deviation)values
--1-jan-2013
--SalesInvoiceNumber,SalesDateKey,SalesTimeKey,SalesTimeAltKey,StoreID,CustomerID,ProductID ,SalesPersonID,Quantity,ProductActualCost,SalesTotalCost,Deviation)
(1,20130101,44347,121907,1,1,1,1,2,11,13,2),
(1,20130101,44347,121907,1,1,2,1,1,22.50,24,1.5),
(1,20130101,44347,121907,1,1,3,1,1,42,43.5,1.5),
(2,20130101,44519,122159,1,2,3,1,1,42,43.5,1.5),
(2,20130101,44519,122159,1,2,4,1,3,54,60,6),
(3,20130101,52415,143335,1,3,2,2,2,11,13,2),
(3,20130101,52415,143335,1,3,3,2,1,42,43.5, 1.5),
(3,20130101,52415,143335,1,3,4,2,3,54,60,6),
(3,20130101,52415,143335,1,3,5,2,1,135,139,4),
--2-jan-2013
--SalesInvoiceNumber,SalesDateKey,SalesTimeKey,SalesTimeAltKey,StoreID,CustomerID,ProductID ,SalesPersonID,Quantity,ProductActualCost,SalesTotalCost,Deviation)
(4,20130102,44347,121907,1,1,1,1,2,11,13,2),
(4,20130102,44347,121907,1,1,2,1,1,22.50,24,1.5),
(5,20130102,44519,122159,1,2,3,1,1,42,43.5,1.5),
(5,20130102,44519,122159,1,2,4,1,3,54,60,6),
(6,20130102,52415,143335,1,3,2,2,2,11,13,2),
(6,20130102,52415,143335,1,3,5,2,1,135,139,4),
(7,20130102,44347,121907,2,1,4,3,3,54,60,6),
(7,20130102,44347,121907,2,1,5,3,1,135,139,4),
--3-jan-2013
--SalesInvoiceNumber,SalesDateKey,SalesTimeKey,SalesTimeAltKey,StoreID,CustomerID,ProductID ,SalesPersonID,Quantity,ProductActualCost,SalesTo talCost,Deviation)
(8,20130103,59326,162846,1,1,3,1,2,84,87,3),
(8,20130103,59326,162846,1,1,4,1,3,54,60,3),
(9,20130103,59349,162909,1,2,1,1,1,5.5,6.5,1),
(9,20130103,59349,162909,1,2,2,1,1,22.50,24,1.5),
(10,20130103,67390,184310,1,3,1,2,2,11,13,2),
(10,20130103,67390,184310,1,3,4,2,3,54,60,6),
(11,20130103,74877,204757,2,1,2,3,1,5.5,6.5,1),
(11,20130103,74877,204757,2,1,3,3,1,42,43.5,1.5)
GO
I keep getting the error:
\"Error Message: Incorrect syntax near ’GO’.
Database Sales_DW doesn’t exists.Make sure that the name is entered correctly.\"
Please help.
Hello everyone,
I'm trying to execute the following SQL script in Visual Studio 2012:
--DROP DATABASE Sales_DW
GO
Create database Sales_DW
GO
Use Sales_DW
GO
--Create Customer dimension table in Data Warehouse which will hold customer personal details.
Create table DimCustomer
(
CustomerID int primary key identity,
CustomerAltID varchar(10) not null,
CustomerName varchar(50),
Gender varchar(20)
)
GO
--Fill the Customer dimension with sample Values
Insert into DimCustomer(CustomerAltID,CustomerName,Gender)values
('IMI-001','Henry Ford','M'),
('IMI-002','Bill Gates','M'),
('IMI-003','Muskan Shaikh','F'),
('IMI-004','Richard Thrubin','M'),
('IMI-005','Emma Wattson','F');
GO
--Create basic level of Product Dimension table without considering any Category or Subcategory.
Create table DimProduct
(
ProductKey int primary key identity,
ProductAltKey varchar(10)not null,
ProductName varchar(100),
ProductActualCost money,
ProductSalesCost money
)
GO
--Fill the Product dimension with sample Values
Insert into DimProduct(ProductAltKey,ProductName, ProductActualCost, ProductSalesCost)values
('ITM-001','Wheat Floor 1kg',5.50,6.50),
('ITM-002','Rice Grains 1kg',22.50,24),
('ITM-003','SunFlower Oil 1 ltr',42,43.5),
('ITM-004','Nirma Soap',18,20),
('ITM-005','Arial Washing Powder 1kg',135,139);
GO
--Create Store Dimension table which will hold details related stores available across various place.
Create table DimStores
(
StoreID int primary key identity,
StoreAltID varchar(10)not null,
StoreName varchar(100),
StoreLocation varchar(100),
City varchar(100),
State varchar(100),
Country varchar(100)
)
GO
--Fill the Store Dimension with sample Values
Insert into DimStores(StoreAltID,StoreName,StoreLocation,City,State,Country )values
('LOC-A1','X-Mart','S.P. RingRoad','Ahmedabad','Guj','India'),
('LOC-A2','X-Mart','Maninagar','Ahmedabad','Guj','India'),
('LOC-A3','X-Mart','Sivranjani','Ahmedabad','Guj','India');
GO
--Create Dimension Sales Person table which will hold details related stores available across various place.
Create table DimSalesPerson
(
SalesPersonID int primary key identity,
SalesPersonAltID varchar(10)not null,
SalesPersonName varchar(100),
StoreID int,
City varchar(100),
State varchar(100),
Country varchar(100)
)
GO
--Fill the Dimension Sales Person with sample Values
Insert into DimSalesPerson(SalesPersonAltID,SalesPersonName,StoreID,City,State,Country )values
('SP-DMSPR1','Ashish',1,'Ahmedabad','Guj','India'),
('SP-DMSPR2','Ketan',1,'Ahmedabad','Guj','India'),
('SP-DMNGR1','Srinivas',2,'Ahmedabad','Guj','India'),
('SP-DMNGR2','Saad',2,'Ahmedabad','Guj','India'),
('SP-DMSVR1','Jasmin',3,'Ahmedabad','Guj','India'),
('SP-DMSVR2','Jacob',3,'Ahmedabad','Guj','India');
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DimTime](
[TimeKey] [int] NOT NULL,
[TimeAltKey] [int] NOT NULL,
[Time30] [varchar](8) NOT NULL,
[Hour30] [tinyint] NOT NULL,
[MinuteNumber] [tinyint] NOT NULL,
[SecondNumber] [tinyint] NOT NULL,
[TimeInSecond] [int] NOT NULL,
[HourlyBucket] varchar(15)not null,
[DayTimeBucketGroupKey] int not null,
[DayTimeBucket] varchar(100) not null
CONSTRAINT [PK_DimTime] PRIMARY KEY CLUSTERED
(
[TimeKey] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/***** Create Stored procedure In Test_DW and Run SP To Fill Time Dimension with Values****/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[FillDimTime]
as
BEGIN
--Specify Total Number of Hours You need to fill in Time Dimension
DECLARE @Size INTEGER
--iF @Size=32 THEN This will Fill values Upto 32:59 hr in Time Dimension
Set @Size=23
DECLARE @hour INTEGER
DECLARE @minute INTEGER
DECLARE @second INTEGER
DECLARE @k INTEGER
DECLARE @TimeAltKey INTEGER
DECLARE @TimeInSeconds INTEGER
DECLARE @Time30 varchar(25)
DECLARE @Hour30 varchar(4)
DECLARE @Minute30 varchar(4)
DECLARE @Second30 varchar(4)
DECLARE @HourBucket varchar(15)
DECLARE @HourBucketGroupKey int
DECLARE @DayTimeBucket varchar(100)
DECLARE @DayTimeBucketGroupKey int
SET @hour = 0
SET @minute = 0
SET @second = 0
SET @k = 0
SET @TimeAltKey = 0
WHILE(@hour<= @Size )
BEGIN
if (@hour <10 )
begin
set @Hour30 = '0' + cast( @hour as varchar(10))
end
else
begin
set @Hour30 = @hour
end
--Create Hour Bucket Value
set @HourBucket= @Hour30+':00' +'-' +@Hour30+':59'
WHILE(@minute <= 59)
BEGIN
WHILE(@second <= 59)
BEGIN
set @TimeAltKey = @hour *10000 +@minute*100 +@second
set @TimeInSeconds =@hour * 3600 + @minute *60 +@second
If @minute <10
begin
set @Minute30 = '0' + cast ( @minute as varchar(10) )
end
else
begin
set @Minute30 = @minute
end
if @second <10
begin
set @Second30 = '0' + cast ( @second as varchar(10) )
end
else
begin
set @Second30 = @second
end
--Concatenate values for Time30
set @Time30 = @Hour30 +':'+@Minute30 +':'+@Second30
--DayTimeBucketGroupKey can be used in Sorting of DayTime Bucket In proper Order
SELECT @DayTimeBucketGroupKey =
CASE
WHEN (@TimeAltKey >= 00000 AND @TimeAltKey <= 25959) THEN 0
WHEN (@TimeAltKey >= 30000 AND @TimeAltKey <= 65959) THEN 1
WHEN (@TimeAltKey >= 70000 AND @TimeAltKey <= 85959) THEN 2
WHEN (@TimeAltKey >= 90000 AND @TimeAltKey <= 115959) THEN 3
WHEN (@TimeAltKey >= 120000 AND @TimeAltKey <= 135959)THEN 4
WHEN (@TimeAltKey >= 140000 AND @TimeAltKey <= 155959)THEN 5
WHEN (@TimeAltKey >= 50000 AND @TimeAltKey <= 175959) THEN 6
WHEN (@TimeAltKey >= 180000 AND @TimeAltKey <= 235959)THEN 7
WHEN (@TimeAltKey >= 240000) THEN 8
END
--print @DayTimeBucketGroupKey
-- DayTimeBucket Time Divided in Spcific Time Zone So Data can Be Grouped as per Bucket for Analyzing as per time of day
SELECT @DayTimeBucket =
CASE
WHEN (@TimeAltKey >= 00000 AND @TimeAltKey <= 25959) THEN 'Late Night (00:00 AM To 02:59 AM)'
WHEN (@TimeAltKey >= 30000 AND @TimeAltKey <= 65959) THEN 'Early Morning(03:00 AM To 6:59 AM)'
WHEN (@TimeAltKey >= 70000 AND @TimeAltKey <= 85959) THEN 'AM Peak (7:00 AM To 8:59 AM)'
WHEN (@TimeAltKey >= 90000 AND @TimeAltKey <= 115959) THEN 'Mid Morning (9:00 AM To 11:59 AM)'
WHEN (@TimeAltKey >= 120000 AND @TimeAltKey <= 135959) THEN 'Lunch (12:00 PM To 13:59 PM)'
WHEN (@TimeAltKey >= 140000 AND @TimeAltKey <= 155959)THEN 'Mid Afternoon (14:00 PM To 15:59 PM)'
WHEN (@TimeAltKey >= 50000 AND @TimeAltKey <= 175959)THEN 'PM Peak (16:00 PM To 17:59 PM)'
WHEN (@TimeAltKey >= 180000 AND @TimeAltKey <= 235959)THEN 'Evening (18:00 PM To 23:59 PM)'
WHEN (@TimeAltKey >= 240000) THEN 'Previous Day Late Night (24:00 PM to '+cast( @Size as varchar(10)) +':00 PM )'
END
-- print @DayTimeBucket
INSERT into DimTime (TimeKey,TimeAltKey,[Time30] ,[Hour30] ,[MinuteNumber],[SecondNumber],[TimeInSecond],[HourlyBucket],DayTimeBucketGroupKey,DayTimeBucket)
VALUES (@k,@TimeAltKey ,@Time30 ,@hour ,@minute,@Second , @TimeInSeconds,@HourBucket,@DayTimeBucketGroupKey,@DayTimeBucket )
SET @second = @second + 1
SET @k = @k + 1
END
SET @minute = @minute + 1
SET @second = 0
END
SET @hour = @hour + 1
SET @minute =0
END
END
GO
Exec [FillDimTime]
GO
BEGIN TRY
DROP TABLE [dbo].[DimDate]
END TRY
BEGIN CATCH
/*No Action*/
END CATCH
/**********************************************************************************/
CREATE TABLE [dbo].[DimDate]
( [DateKey] INT primary key,
[Date] DATETIME,
[FullDateUK] CHAR(10), -- Date in dd-MM-yyyy format
[FullDateUSA] CHAR(10),-- Date in MM-dd-yyyy format
[DayOfMonth] VARCHAR(2), -- Field will hold day number of Month
[DaySuffix] VARCHAR(4), -- Apply suffix as 1st, 2nd ,3rd etc
[DayName] VARCHAR(9), -- Contains name of the day, Sunday, Monday
[DayOfWeekUSA] CHAR(1),-- First Day Sunday=1 and Saturday=7
[DayOfWeekUK] CHAR(1),-- First Day Monday=1 and Sunday=7
[DayOfWeekInMonth] VARCHAR(2), --1st Monday or 2nd Monday in Month
[DayOfWeekInYear] VARCHAR(2),
[DayOfQuarter] VARCHAR(3),
[DayOfYear] VARCHAR(3),
[WeekOfMonth] VARCHAR(1),-- Week Number of Month
[WeekOfQuarter] VARCHAR(2), --Week Number of the Quarter
[WeekOfYear] VARCHAR(2),--Week Number of the Year
[Month] VARCHAR(2), --Number of the Month 1 to 12
[MonthName] VARCHAR(9),--January, February etc
[MonthOfQuarter] VARCHAR(2),-- Month Number belongs to Quarter
[Quarter] CHAR(1),
[QuarterName] VARCHAR(9),--First,Second..
[Year] CHAR(4),-- Year value of Date stored in Row
[YearName] CHAR(7), --CY 2012,CY 2013
[MonthYear] CHAR(10), --Jan-2013,Feb-2013
[MMYYYY] CHAR(6),
[FirstDayOfMonth] DATE,
[LastDayOfMonth] DATE,
[FirstDayOfQuarter] DATE,
[LastDayOfQuarter] DATE,
[FirstDayOfYear] DATE,
[LastDayOfYear] DATE,
[IsHolidayUSA] BIT,-- Flag 1=National Holiday, 0-No National Holiday
[IsWeekday] BIT,-- 0=Week End ,1=Week Day
[HolidayUSA] VARCHAR(50),--Name of Holiday in US
[IsHolidayUK] BIT Null, -- Flag 1=National Holiday, 0-No National Holiday
[HolidayUK] VARCHAR(50) Null --Name of Holiday in UK
)
GO
/********************************************************************************************/
--Specify Start Date and End date here
--Value of Start Date Must be Less than Your End Date
DECLARE @StartDate DATETIME = '01/01/2013' --Starting value of Date Range
DECLARE @EndDate DATETIME = '01/01/2015' --End Value of Date Range
--Temporary Variables To Hold the Values During Processing of Each Date of Year
DECLARE
@DayOfWeekInMonth INT,
@DayOfWeekInYear INT,
@DayOfQuarter INT,
@WeekOfMonth INT,
@CurrentYear INT,
@CurrentMonth INT,
@CurrentQuarter INT
/*Table Data type to store the day of week count for the month and year*/
DECLARE @DayOfWeek TABLE (DOW INT, MonthCount INT, QuarterCount INT, YearCount INT)
INSERT INTO @DayOfWeek VALUES (1, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (2, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (3, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (4, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (5, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (6, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (7, 0, 0, 0)
--Extract and assign part of Values from Current Date to Variable
DECLARE @CurrentDate AS DATETIME = @StartDate
SET @CurrentMonth = DATEPART(MM, @CurrentDate)
SET @CurrentYear = DATEPART(YY, @CurrentDate)
SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)
/********************************************************************************************/
--Proceed only if Start Date(Current date ) is less than End date you specified above
WHILE @CurrentDate < @EndDate
BEGIN
/*Begin day of week logic*/
/*Check for Change in Month of the Current date if Month changed then
Change variable value*/
IF @CurrentMonth != DATEPART(MM, @CurrentDate)
BEGIN
UPDATE @DayOfWeek
SET MonthCount = 0
SET @CurrentMonth = DATEPART(MM, @CurrentDate)
END
/* Check for Change in Quarter of the Current date if Quarter changed then change
Variable value*/
IF @CurrentQuarter != DATEPART(QQ, @CurrentDate)
BEGIN
UPDATE @DayOfWeek
SET QuarterCount = 0
SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)
END
/* Check for Change in Year of the Current date if Year changed then change
Variable value*/
IF @CurrentYear != DATEPART(YY, @CurrentDate)
BEGIN
UPDATE @DayOfWeek
SET YearCount = 0
SET @CurrentYear = DATEPART(YY, @CurrentDate)
END
-- Set values in table data type created above from variables
UPDATE @DayOfWeek
SET
MonthCount = MonthCount + 1,
QuarterCount = QuarterCount + 1,
YearCount = YearCount + 1
WHERE DOW = DATEPART(DW, @CurrentDate)
SELECT
@DayOfWeekInMonth = MonthCount,
@DayOfQuarter = QuarterCount,
@DayOfWeekInYear = YearCount
FROM @DayOfWeek
WHERE DOW = DATEPART(DW, @CurrentDate)
/*End day of week logic*/
/* Populate Your Dimension Table with values*/
INSERT INTO [dbo].[DimDate]
SELECT
CONVERT (char(8),@CurrentDate,112) as DateKey,
@CurrentDate AS Date,
CONVERT (char(10),@CurrentDate,103) as FullDateUK,
CONVERT (char(10),@CurrentDate,101) as FullDateUSA,
DATEPART(DD, @CurrentDate) AS DayOfMonth,
--Apply Suffix values like 1st, 2nd 3rd etc..
CASE
WHEN DATEPART(DD,@CurrentDate) IN (11,12,13) THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'th'
WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 1 THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'st'
WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 2 THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'nd'
WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 3 THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'rd'
ELSE CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'th'
END AS DaySuffix,
DATENAME(DW, @CurrentDate) AS DayName,
DATEPART(DW, @CurrentDate) AS DayOfWeekUSA,
-- check for day of week as Per US and change it as per UK format
CASE DATEPART(DW, @CurrentDate)
WHEN 1 THEN 7
WHEN 2 THEN 1
WHEN 3 THEN 2
WHEN 4 THEN 3
WHEN 5 THEN 4
WHEN 6 THEN 5
WHEN 7 THEN 6
END
AS DayOfWeekUK,
@DayOfWeekInMonth AS DayOfWeekInMonth,
@DayOfWeekInYear AS DayOfWeekInYear,
@DayOfQuarter AS DayOfQuarter,
DATEPART(DY, @CurrentDate) AS DayOfYear,
DATEPART(WW, @CurrentDate) + 1 - DATEPART(WW, CONVERT(VARCHAR, DATEPART(MM, @CurrentDate)) + '/1/' + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate))) AS WeekOfMonth,
(DATEDIFF(DD, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0), @CurrentDate) / 7) + 1 AS WeekOfQuarter,
DATEPART(WW, @CurrentDate) AS WeekOfYear,
DATEPART(MM, @CurrentDate) AS Month,
DATENAME(MM, @CurrentDate) AS MonthName,
CASE
WHEN DATEPART(MM, @CurrentDate) IN (1, 4, 7, 10) THEN 1
WHEN DATEPART(MM, @CurrentDate) IN (2, 5, 8, 11) THEN 2
WHEN DATEPART(MM, @CurrentDate) IN (3, 6, 9, 12) THEN 3
END AS MonthOfQuarter,
DATEPART(QQ, @CurrentDate) AS Quarter,
CASE DATEPART(QQ, @CurrentDate)
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
WHEN 4 THEN 'Fourth'
END AS QuarterName,
DATEPART(YEAR, @CurrentDate) AS Year,
'CY ' + CONVERT(VARCHAR, DATEPART(YEAR, @CurrentDate)) AS YearName,
LEFT(DATENAME(MM, @CurrentDate), 3) + '-' + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate)) AS MonthYear,
RIGHT('0' + CONVERT(VARCHAR, DATEPART(MM, @CurrentDate)),2) + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate)) AS MMYYYY,
CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, @CurrentDate) - 1), @CurrentDate))) AS FirstDayOfMonth,
CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, (DATEADD(MM, 1, @CurrentDate)))), DATEADD(MM, 1, @CurrentDate)))) AS LastDayOfMonth,
DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0) AS FirstDayOfQuarter,
DATEADD(QQ, DATEDIFF(QQ, -1, @CurrentDate), -1) AS LastDayOfQuarter,
CONVERT(DATETIME, '01/01/' + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate))) AS FirstDayOfYear,
CONVERT(DATETIME, '12/31/' + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate))) AS LastDayOfYear,
NULL AS IsHolidayUSA,
CASE DATEPART(DW, @CurrentDate)
WHEN 1 THEN 0
WHEN 2 THEN 1
WHEN 3 THEN 1
WHEN 4 THEN 1
WHEN 5 THEN 1
WHEN 6 THEN 1
WHEN 7 THEN 0
END AS IsWeekday,
NULL AS HolidayUSA, Null, Null
SET @CurrentDate = DATEADD(DD, 1, @CurrentDate)
END
/*Add HOLIDAYS UK*/
-- Good Friday April 18
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Good Friday'
WHERE [Month] = 4 AND [DayOfMonth] = 18
-- Easter Monday April 21
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Easter Monday'
WHERE [Month] = 4 AND [DayOfMonth] = 21
-- Early May Bank Holiday May 5
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Early May Bank Holiday'
WHERE [Month] = 5 AND [DayOfMonth] = 5
-- Spring Bank Holiday May 26
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Spring Bank Holiday'
WHERE [Month] = 5 AND [DayOfMonth] = 26
-- Summer Bank Holiday August 25
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Summer Bank Holiday'
WHERE [Month] = 8 AND [DayOfMonth] = 25
-- Boxing Day December 26
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Boxing Day'
WHERE [Month] = 12 AND [DayOfMonth] = 26
--CHRISTMAS
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Christmas Day'
WHERE [Month] = 12 AND [DayOfMonth] = 25
--New Years Day
UPDATE [dbo].[DimDate]
SET HolidayUK = 'New Year''s Day'
WHERE [Month] = 1 AND [DayOfMonth] = 1
UPDATE [dbo].[DimDate]
SET IsHolidayUK = CASE WHEN HolidayUK IS NULL THEN 0 WHEN HolidayUK IS NOT NULL THEN 1 END
/*Add HOLIDAYS USA*/
/*THANKSGIVING - Fourth THURSDAY in November*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Thanksgiving Day'
WHERE
[Month] = 11
AND [DayOfWeekUSA] = 'Thursday'
AND DayOfWeekInMonth = 4
/*CHRISTMAS*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Christmas Day'
WHERE [Month] = 12 AND [DayOfMonth] = 25
/*4th of July*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Independance Day'
WHERE [Month] = 7 AND [DayOfMonth] = 4
/*New Years Day*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'New Year''s Day'
WHERE [Month] = 1 AND [DayOfMonth] = 1
/*Memorial Day - Last Monday in May*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Memorial Day'
FROM [dbo].[DimDate]
WHERE DateKey IN
(
SELECT
MAX(DateKey)
FROM [dbo].[DimDate]
WHERE
[MonthName] = 'May'
AND [DayOfWeekUSA] = 'Monday'
GROUP BY
[Year],
[Month]
)
/*Labor Day - First Monday in September*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Labor Day'
FROM [dbo].[DimDate]
WHERE DateKey IN
(
SELECT
MIN(DateKey)
FROM [dbo].[DimDate]
WHERE
[MonthName] = 'September'
AND [DayOfWeekUSA] = 'Monday'
GROUP BY
[Year],
[Month]
)
/*Valentine's Day*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Valentine''s Day'
WHERE
[Month] = 2
AND [DayOfMonth] = 14
/*Saint Patrick's Day*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Saint Patrick''s Day'
WHERE
[Month] = 3
AND [DayOfMonth] = 17
/*Martin Luthor King Day - Third Monday in January starting in 1983*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Martin Luthor King Jr Day'
WHERE
[Month] = 1
AND [DayOfWeekUSA] = 'Monday'
AND [Year] >= 1983
AND DayOfWeekInMonth = 3
/*President's Day - Third Monday in February*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'President''s Day'
WHERE
[Month] = 2
AND [DayOfWeekUSA] = 'Monday'
AND DayOfWeekInMonth = 3
/*Mother's Day - Second Sunday of May*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Mother''s Day'
WHERE
[Month] = 5
AND [DayOfWeekUSA] = 'Sunday'
AND DayOfWeekInMonth = 2
/*Father's Day - Third Sunday of June*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Father''s Day'
WHERE
[Month] = 6
AND [DayOfWeekUSA] = 'Sunday'
AND DayOfWeekInMonth = 3
/*Halloween 10/31*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Halloween'
WHERE
[Month] = 10
AND [DayOfMonth] = 31
/*Election Day - The first Tuesday after the first Monday in November*/
BEGIN
DECLARE @Holidays TABLE (ID INT IDENTITY(1,1), DateID int, Week TINYINT, YEAR CHAR(4), DAY CHAR(2))
INSERT INTO @Holidays(DateID, [Year],[Day])
SELECT
DateKey,
[Year],
[DayOfMonth]
FROM [dbo].[DimDate]
WHERE
[Month] = 11
AND [DayOfWeekUSA] = 'Monday'
ORDER BY
YEAR,
DayOfMonth
DECLARE @CNTR INT, @POS INT, @STARTYEAR INT, @ENDYEAR INT, @MINDAY INT
SELECT
@CURRENTYEAR = MIN([Year])
, @STARTYEAR = MIN([Year])
, @ENDYEAR = MAX([Year])
FROM @Holidays
WHILE @CURRENTYEAR <= @ENDYEAR
BEGIN
SELECT @CNTR = COUNT([Year])
FROM @Holidays
WHERE [Year] = @CURRENTYEAR
SET @POS = 1
WHILE @POS <= @CNTR
BEGIN
SELECT @MINDAY = MIN(DAY)
FROM @Holidays
WHERE
[Year] = @CURRENTYEAR
AND [Week] IS NULL
UPDATE @Holidays
SET [Week] = @POS
WHERE
[Year] = @CURRENTYEAR
AND [Day] = @MINDAY
SELECT @POS = @POS + 1
END
SELECT @CURRENTYEAR = @CURRENTYEAR + 1
END
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Election Day'
FROM [dbo].[DimDate] DT
JOIN @Holidays HL ON (HL.DateID + 1) = DT.DateKey
WHERE
[Week] = 1
END
UPDATE [dbo].[DimDate]
SET IsHolidayUSA = CASE WHEN HolidayUSA IS NULL THEN 0 WHEN HolidayUSA IS NOT NULL THEN 1 END
/*******************************************************************************************************************************************************/
--select * from DimDate
--Script 2 fiscal calendar setting in Date dimension
/*******************************************************************************************************************************************************/
SELECT * FROM [dbo].[DimDate]
/*Add Fiscal date columns to DimDate*/
ALTER TABLE [dbo].[DimDate] ADD
[FiscalDayOfYear] VARCHAR(3),
[FiscalWeekOfYear] VARCHAR(3),
[FiscalMonth] VARCHAR(2),
[FiscalQuarter] CHAR(1),
[FiscalQuarterName] VARCHAR(9),
[FiscalYear] CHAR(4),
[FiscalYearName] CHAR(7),
[FiscalMonthYear] CHAR(10),
[FiscalMMYYYY] CHAR(6),
[FiscalFirstDayOfMonth] DATE,
[FiscalLastDayOfMonth] DATE,
[FiscalFirstDayOfQuarter] DATE,
[FiscalLastDayOfQuarter] DATE,
[FiscalFirstDayOfYear] DATE,
[FiscalLastDayOfYear] DATE
GO
/*******************************************************************************************************************************************************
The following section needs to be populated for defining the fiscal calendar
*******************************************************************************************************************************************************/
DECLARE
@dtFiscalYearStart SMALLDATETIME = 'January 01, 1995',
@FiscalYear INT = 1995,
@LastYear INT = 2025,
@FirstLeapYearInPeriod INT = 1996
/*******************************************************************************************************************************************************/
DECLARE
@iTemp INT,
@LeapWeek INT,
@CurrentDate DATETIME,
@FiscalDayOfYear INT,
@FiscalWeekOfYear INT,
@FiscalMonth INT,
@FiscalQuarter INT,
@FiscalQuarterName VARCHAR(10),
@FiscalYearName VARCHAR(7),
@LeapYear INT,
@FiscalFirstDayOfYear DATE,
@FiscalFirstDayOfQuarter DATE,
@FiscalFirstDayOfMonth DATE,
@FiscalLastDayOfYear DATE,
@FiscalLastDayOfQuarter DATE,
@FiscalLastDayOfMonth DATE
/*Holds the years that have 455 in last quarter*/
DECLARE @LeapTable TABLE (leapyear INT)
/*TABLE to contain the fiscal year calendar*/
DECLARE @tb TABLE(
PeriodDate DATETIME,
[FiscalDayOfYear] VARCHAR(3),
[FiscalWeekOfYear] VARCHAR(3),
[FiscalMonth] VARCHAR(2),
[FiscalQuarter] VARCHAR(1),
[FiscalQuarterName] VARCHAR(9),
[FiscalYear] VARCHAR(4),
[FiscalYearName] VARCHAR(7),
[FiscalMonthYear] VARCHAR(10),
[FiscalMMYYYY] VARCHAR(6),
[FiscalFirstDayOfMonth] DATE,
[FiscalLastDayOfMonth] DATE,
[FiscalFirstDayOfQuarter] DATE,
[FiscalLastDayOfQuarter] DATE,
[FiscalFirstDayOfYear] DATE,
[FiscalLastDayOfYear] DATE)
/*Populate the table with all leap years*/
SET @LeapYear = @FirstLeapYearInPeriod
WHILE (@LeapYear < @LastYear)
BEGIN
INSERT INTO @leapTable VALUES (@LeapYear)
SET @LeapYear = @LeapYear + 5
END
/*Initiate parameters before loop*/
SET @CurrentDate = @dtFiscalYearStart
SET @FiscalDayOfYear = 1
SET @FiscalWeekOfYear = 1
SET @FiscalMonth = 1
SET @FiscalQuarter = 1
SET @FiscalWeekOfYear = 1
IF (EXISTS (SELECT * FROM @LeapTable WHERE @FiscalYear = leapyear))
BEGIN
SET @LeapWeek = 1
END
ELSE
BEGIN
SET @LeapWeek = 0
END
/*******************************************************************************************************************************************************/
/* Loop on days in interval*/
WHILE (DATEPART(yy,@CurrentDate) <= @LastYear)
BEGIN
/*SET fiscal Month*/
SELECT @FiscalMonth = CASE
/*Use this section for a 4-5-4 calendar. Every leap year the result will be a 4-5-5*/
WHEN @FiscalWeekOfYear BETWEEN 1 AND 4 THEN 1 /*4 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 5 AND 9 THEN 2 /*5 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 10 AND 13 THEN 3 /*4 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 14 AND 17 THEN 4 /*4 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 18 AND 22 THEN 5 /*5 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 23 AND 26 THEN 6 /*4 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 27 AND 30 THEN 7 /*4 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 31 AND 35 THEN 8 /*5 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 36 AND 39 THEN 9 /*4 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 40 AND 43 THEN 10 /*4 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 44 AND (48+@LeapWeek) THEN 11 /*5 weeks*/
WHEN @FiscalWeekOfYear BETWEEN (49+@LeapWeek) AND (52+@LeapWeek) THEN 12 /*4 weeks (5 weeks on leap year)*/
/*Use this section for a 4-4-5 calendar. Every leap year the result will be a 4-5-5*/
/*
WHEN @FiscalWeekOfYear BETWEEN 1 AND 4 THEN 1 /*4 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 5 AND 8 THEN 2 /*4 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 9 AND 13 THEN 3 /*5 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 14 AND 17 THEN 4 /*4 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 18 AND 21 THEN 5 /*4 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 22 AND 26 THEN 6 /*5 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 27 AND 30 THEN 7 /*4 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 31 AND 34 THEN 8 /*4 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 35 AND 39 THEN 9 /*5 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 40 AND 43 THEN 10 /*4 weeks*/
WHEN @FiscalWeekOfYear BETWEEN 44 AND (47+@leapWeek) THEN 11 /*4 weeks (5 weeks on leap year)*/
WHEN @FiscalWeekOfYear BETWEEN (48+@leapWeek) AND (52+@leapWeek) THEN 12 /*5 weeks*/
*/
END
/*SET Fiscal Quarter*/
SELECT @FiscalQuarter = CASE
WHEN @FiscalMonth BETWEEN 1 AND 3 THEN 1
WHEN @FiscalMonth BETWEEN 4 AND 6 THEN 2
WHEN @FiscalMonth BETWEEN 7 AND 9 THEN 3
WHEN @FiscalMonth BETWEEN 10 AND 12 THEN 4
END
SELECT @FiscalQuarterName = CASE
WHEN @FiscalMonth BETWEEN 1 AND 3 THEN 'First'
WHEN @FiscalMonth BETWEEN 4 AND 6 THEN 'Second'
WHEN @FiscalMonth BETWEEN 7 AND 9 THEN 'Third'
WHEN @FiscalMonth BETWEEN 10 AND 12 THEN 'Fourth'
END
/*Set Fiscal Year Name*/
SELECT @FiscalYearName = 'FY ' + CONVERT(VARCHAR, @FiscalYear)
INSERT INTO @tb (PeriodDate, FiscalDayOfYear, FiscalWeekOfYear, fiscalMonth, FiscalQuarter, FiscalQuarterName, FiscalYear, FiscalYearName) VALUES
(@CurrentDate, @FiscalDayOfYear, @FiscalWeekOfYear, @FiscalMonth, @FiscalQuarter, @FiscalQuarterName, @FiscalYear, @FiscalYearName)
/*SET next day*/
SET @CurrentDate = DATEADD(dd, 1, @CurrentDate)
SET @FiscalDayOfYear = @FiscalDayOfYear + 1
SET @FiscalWeekOfYear = ((@FiscalDayOfYear-1) / 7) + 1
IF (@FiscalWeekOfYear > (52+@LeapWeek))
BEGIN
/*Reset a new year*/
SET @FiscalDayOfYear = 1
SET @FiscalWeekOfYear = 1
SET @FiscalYear = @FiscalYear + 1
IF ( EXISTS (SELECT * FROM @leapTable WHERE @FiscalYear = leapyear))
BEGIN
SET @LeapWeek = 1
END
ELSE
BEGIN
SET @LeapWeek = 0
END
END
END
/*******************************************************************************************************************************************************/
/*Set first and last days of the fiscal months*/
UPDATE @tb
SET
FiscalFirstDayOfMonth = minmax.StartDate,
FiscalLastDayOfMonth = minmax.EndDate
FROM
@tb t,
(
SELECT FiscalMonth, FiscalQuarter, FiscalYear, MIN(PeriodDate) AS StartDate, MAX(PeriodDate) AS EndDate
FROM @tb
GROUP BY FiscalMonth, FiscalQuarter, FiscalYear
) minmax
WHERE
t.FiscalMonth = minmax.FiscalMonth AND
t.FiscalQuarter = minmax.FiscalQuarter AND
t.FiscalYear = minmax.FiscalYear
/*Set first and last days of the fiscal quarters*/
UPDATE @tb
SET
FiscalFirstDayOfQuarter = minmax.StartDate,
FiscalLastDayOfQuarter = minmax.EndDate
FROM
@tb t,
(
SELECT FiscalQuarter, FiscalYear, min(PeriodDate) as StartDate, max(PeriodDate) as EndDate
FROM @tb
GROUP BY FiscalQuarter, FiscalYear
) minmax
WHERE
t.FiscalQuarter = minmax.FiscalQuarter AND
t.FiscalYear = minmax.FiscalYear
/*Set first and last days of the fiscal years*/
UPDATE @tb
SET
FiscalFirstDayOfYear = minmax.StartDate,
FiscalLastDayOfYear = minmax.EndDate
FROM
@tb t,
(
SELECT FiscalYear, min(PeriodDate) as StartDate, max(PeriodDate) as EndDate
FROM @tb
GROUP BY FiscalYear
) minmax
WHERE
t.FiscalYear = minmax.FiscalYear
/*Set FiscalYearMonth*/
UPDATE @tb
SET
FiscalMonthYear =
CASE FiscalMonth
WHEN 1 THEN 'Jan'
WHEN 2 THEN 'Feb'
WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr'
WHEN 5 THEN 'May'
WHEN 6 THEN 'Jun'
WHEN 7 THEN 'Jul'
WHEN 8 THEN 'Aug'
WHEN 9 THEN 'Sep'
WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov'
WHEN 12 THEN 'Dec'
END + '-' + CONVERT(VARCHAR, FiscalYear)
/*Set FiscalMMYYYY*/
UPDATE @tb
SET
FiscalMMYYYY = RIGHT('0' + CONVERT(VARCHAR, FiscalMonth),2) + CONVERT(VARCHAR, FiscalYear)
/*******************************************************************************************************************************************************/
UPDATE [dbo].[DimDate]
SET
FiscalDayOfYear = a.FiscalDayOfYear
, FiscalWeekOfYear = a.FiscalWeekOfYear
, FiscalMonth = a.FiscalMonth
, FiscalQuarter = a.FiscalQuarter
, FiscalQuarterName = a.FiscalQuarterName
, FiscalYear = a.FiscalYear
, FiscalYearName = a.FiscalYearName
, FiscalMonthYear = a.FiscalMonthYear
, FiscalMMYYYY = a.FiscalMMYYYY
, FiscalFirstDayOfMonth = a.FiscalFirstDayOfMonth
, FiscalLastDayOfMonth = a.FiscalLastDayOfMonth
, FiscalFirstDayOfQuarter = a.FiscalFirstDayOfQuarter
, FiscalLastDayOfQuarter = a.FiscalLastDayOfQuarter
, FiscalFirstDayOfYear = a.FiscalFirstDayOfYear
, FiscalLastDayOfYear = a.FiscalLastDayOfYear
FROM @tb a
INNER JOIN [dbo].[DimDate] b ON a.PeriodDate = b.[Date]
/*******************************************************************************************************************************************************/
Create Table FactProductSales
(
TransactionId bigint primary key identity,
SalesInvoiceNumber int not null,
SalesDateKey int,
SalesTimeKey int,
SalesTimeAltKey int,
StoreID int not null,
CustomerID int not null,
ProductID int not null,
SalesPersonID int not null,
Quantity float,
SalesTotalCost money,
ProductActualCost money,
Deviation float
)
GO
--Add Relation between Fact table and dimension tables.
-- Add relation between fact table foreign keys to Primary keys of Dimensions
AlTER TABLE FactProductSales ADD CONSTRAINT FK_StoreID FOREIGN KEY (StoreID)REFERENCES DimStores(StoreID);
AlTER TABLE FactProductSales ADD CONSTRAINT FK_CustomerID FOREIGN KEY (CustomerID)REFERENCES Dimcustomer(CustomerID);
AlTER TABLE FactProductSales ADD CONSTRAINT FK_ProductKey FOREIGN KEY (ProductID)REFERENCES Dimproduct(ProductKey);
AlTER TABLE FactProductSales ADD CONSTRAINT FK_SalesPersonID FOREIGN KEY (SalesPersonID)REFERENCES Dimsalesperson(SalesPersonID);
GO
AlTER TABLE FactProductSales ADD CONSTRAINT FK_SalesDateKey FOREIGN KEY (SalesDateKey)REFERENCES DimDate(DateKey);
GO
AlTER TABLE FactProductSales ADD CONSTRAINT FK_SalesTimeKey FOREIGN KEY (SalesTimeKey)REFERENCES DimTIME(TimeKey);
GO
--Populate your Fact table with historical transaction values of sales for previous day, with proper values of dimension key values.
Insert into FactProductSales(SalesInvoiceNumber,SalesDateKey,SalesTimeKey,SalesTimeAltKey,StoreID,CustomerID,ProductID ,SalesPersonID,Quantity,ProductActualCost,SalesTotalCost,Deviation)values
--1-jan-2013
--SalesInvoiceNumber,SalesDateKey,SalesTimeKey,SalesTimeAltKey,StoreID,CustomerID,ProductID ,SalesPersonID,Quantity,ProductActualCost,SalesTotalCost,Deviation)
(1,20130101,44347,121907,1,1,1,1,2,11,13,2),
(1,20130101,44347,121907,1,1,2,1,1,22.50,24,1.5),
(1,20130101,44347,121907,1,1,3,1,1,42,43.5,1.5),
(2,20130101,44519,122159,1,2,3,1,1,42,43.5,1.5),
(2,20130101,44519,122159,1,2,4,1,3,54,60,6),
(3,20130101,52415,143335,1,3,2,2,2,11,13,2),
(3,20130101,52415,143335,1,3,3,2,1,42,43.5,1.5),
(3,20130101,52415,143335,1,3,4,2,3,54,60,6),
(3,20130101,52415,143335,1,3,5,2,1,135,139,4),
--2-jan-2013
--SalesInvoiceNumber,SalesDateKey,SalesTimeKey,SalesTimeAltKey,StoreID,CustomerID,ProductID ,SalesPersonID,Quantity,ProductActualCost,SalesTotalCost,Deviation)
(4,20130102,44347,121907,1,1,1,1,2,11,13,2),
(4,20130102,44347,121907,1,1,2,1,1,22.50,24,1.5),
(5,20130102,44519,122159,1,2,3,1,1,42,43.5,1.5),
(5,20130102,44519,122159,1,2,4,1,3,54,60,6),
(6,20130102,52415,143335,1,3,2,2,2,11,13,2),
(6,20130102,52415,143335,1,3,5,2,1,135,139,4),
(7,20130102,44347,121907,2,1,4,3,3,54,60,6),
(7,20130102,44347,121907,2,1,5,3,1,135,139,4),
--3-jan-2013
--SalesInvoiceNumber,SalesDateKey,SalesTimeKey,SalesTimeAltKey,StoreID,CustomerID,ProductID ,SalesPersonID,Quantity,ProductActualCost,SalesTotalCost,Deviation)
(8,20130103,59326,162846,1,1,3,1,2,84,87,3),
(8,20130103,59326,162846,1,1,4,1,3,54,60,3),
(9,20130103,59349,162909,1,2,1,1,1,5.5,6.5,1),
(9,20130103,59349,162909,1,2,2,1,1,22.50,24,1.5),
(10,20130103,67390,184310,1,3,1,2,2,11,13,2),
(10,20130103,67390,184310,1,3,4,2,3,54,60,6),
(11,20130103,74877,204757,2,1,2,3,1,5.5,6.5,1),
(11,20130103,74877,204757,2,1,3,3,1,42,43.5,1.5)
GO
I keep getting the error:
"Error Message: Incorrect syntax near 'GO'.
Database Sales_DW doesn't exists.Make sure that the name is entered correctly."
Please help.
推荐答案
Right after the 16th \"GO\", you have incorrect commenting out syntax. You are using \"/*\" instead of \"--\". You are doing that several times throughout the code. Check all instances and correct them.
The second error couldn’t be clearer. That tables does not exist or you spelled it wrong, plain and simple.
Right after the 16th "GO", you have incorrect commenting out syntax. You are using "/*" instead of "--". You are doing that several times throughout the code. Check all instances and correct them.
The second error couldn't be clearer. That tables does not exist or you spelled it wrong, plain and simple.
这篇关于'Go'附近的SQL语法不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
