· 5 years ago · Feb 26, 2020, 04:28 AM
1--DROP DATABASE Sales_DW
2GO
3Create database Sales_DW
4Go
5
6Use Sales_DW
7Go
8
9--Create Customer dimension table in Data Warehouse which will hold customer personal details.
10
11Create table DimCustomer
12(
13CustomerID int primary key identity,
14CustomerAltID varchar(10) not null,
15CustomerName varchar(50),
16Gender varchar(20)
17)
18go
19
20--Fill the Customer dimension with sample Values
21
22Insert into DimCustomer(CustomerAltID,CustomerName,Gender)values
23('IMI-001','Henry Ford','M'),
24('IMI-002','Bill Gates','M'),
25('IMI-003','Muskan Shaikh','F'),
26('IMI-004','Richard Thrubin','M'),
27('IMI-005','Emma Wattson','F');
28Go
29
30--Create basic level of Product Dimension table without considering any Category or Subcategory.
31
32Create table DimProduct
33(
34ProductKey int primary key identity,
35ProductAltKey varchar(10)not null,
36ProductName varchar(100),
37ProductActualCost money,
38ProductSalesCost money
39
40)
41Go
42
43--Fill the Product dimension with sample Values
44
45Insert into DimProduct(ProductAltKey,ProductName, ProductActualCost, ProductSalesCost)values
46('ITM-001','Wheat Floor 1kg',5.50,6.50),
47('ITM-002','Rice Grains 1kg',22.50,24),
48('ITM-003','SunFlower Oil 1 ltr',42,43.5),
49('ITM-004','Nirma Soap',18,20),
50('ITM-005','Arial Washing Powder 1kg',135,139);
51GO
52--Create Store Dimension table which will hold details related stores available across various place.
53Create table DimStores
54(
55StoreID int primary key identity,
56StoreAltID varchar(10)not null,
57StoreName varchar(100),
58StoreLocation varchar(100),
59City varchar(100),
60State varchar(100),
61Country varchar(100)
62)
63Go
64--Fill the Store Dimension with sample Values
65Insert into DimStores(StoreAltID,StoreName,StoreLocation,City,State,Country )values
66('LOC-A1','X-Mart','S.P. RingRoad','Ahmedabad','Guj','India'),
67('LOC-A2','X-Mart','Maninagar','Ahmedabad','Guj','India'),
68('LOC-A3','X-Mart','Sivranjani','Ahmedabad','Guj','India');
69Go
70
71--Create Dimension Sales Person table which will hold details related stores available across various place.
72
73Create table DimSalesPerson
74(
75SalesPersonID int primary key identity,
76SalesPersonAltID varchar(10)not null,
77SalesPersonName varchar(100),
78StoreID int,
79City varchar(100),
80State varchar(100),
81Country varchar(100)
82)
83Go
84
85--Fill the Dimension Sales Person with sample Values
86
87Insert into DimSalesPerson(SalesPersonAltID,SalesPersonName,StoreID,City,State,Country )values
88('SP-DMSPR1','Ashish',1,'Ahmedabad','Guj','India'),
89('SP-DMSPR2','Ketan',1,'Ahmedabad','Guj','India'),
90('SP-DMNGR1','Srinivas',2,'Ahmedabad','Guj','India'),
91('SP-DMNGR2','Saad',2,'Ahmedabad','Guj','India'),
92('SP-DMSVR1','Jasmin',3,'Ahmedabad','Guj','India'),
93('SP-DMSVR2','Jacob',3,'Ahmedabad','Guj','India');
94Go
95
96
97
98
99SET ANSI_NULLS ON
100GO
101SET QUOTED_IDENTIFIER ON
102GO
103SET ANSI_PADDING ON
104GO
105
106CREATE TABLE [dbo].[DimTime](
107 [TimeKey] [int] NOT NULL,
108 [TimeAltKey] [int] NOT NULL,
109 [Time30] [varchar](8) NOT NULL,
110 [Hour30] [tinyint] NOT NULL,
111 [MinuteNumber] [tinyint] NOT NULL,
112 [SecondNumber] [tinyint] NOT NULL,
113 [TimeInSecond] [int] NOT NULL,
114 [HourlyBucket] varchar(15)not null,
115 [DayTimeBucketGroupKey] int not null,
116 [DayTimeBucket] varchar(100) not null
117 CONSTRAINT [PK_DimTime] PRIMARY KEY CLUSTERED
118 (
119 [TimeKey] ASC
120 )
121 WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
122 )
123 ON [PRIMARY]
124
125 GO
126
127 SET ANSI_PADDING OFF
128 GO
129
130
131/***** Create Stored procedure In Test_DW and Run SP To Fill Time Dimension with Values****/
132
133SET ANSI_NULLS ON
134GO
135SET QUOTED_IDENTIFIER ON
136GO
137CREATE PROCEDURE [dbo].[FillDimTime]
138as
139BEGIN
140
141--Specify Total Number of Hours You need to fill in Time Dimension
142DECLARE @Size INTEGER
143--iF @Size=32 THEN This will Fill values Upto 32:59 hr in Time Dimension
144Set @Size=23
145
146DECLARE @hour INTEGER
147DECLARE @minute INTEGER
148DECLARE @second INTEGER
149DECLARE @k INTEGER
150DECLARE @TimeAltKey INTEGER
151DECLARE @TimeInSeconds INTEGER
152DECLARE @Time30 varchar(25)
153DECLARE @Hour30 varchar(4)
154DECLARE @Minute30 varchar(4)
155DECLARE @Second30 varchar(4)
156DECLARE @HourBucket varchar(15)
157DECLARE @HourBucketGroupKey int
158DECLARE @DayTimeBucket varchar(100)
159DECLARE @DayTimeBucketGroupKey int
160
161SET @hour = 0
162SET @minute = 0
163SET @second = 0
164SET @k = 0
165SET @TimeAltKey = 0
166
167WHILE(@hour<= @Size )
168BEGIN
169
170 if (@hour <10 )
171 begin
172 set @Hour30 = '0' + cast( @hour as varchar(10))
173 end
174 else
175 begin
176 set @Hour30 = @hour
177 end
178 --Create Hour Bucket Value
179 set @HourBucket= @Hour30+':00' +'-' +@Hour30+':59'
180
181
182 WHILE(@minute <= 59)
183 BEGIN
184 WHILE(@second <= 59)
185 BEGIN
186
187 set @TimeAltKey = @hour *10000 +@minute*100 +@second
188 set @TimeInSeconds =@hour * 3600 + @minute *60 +@second
189
190 If @minute <10
191 begin
192 set @Minute30 = '0' + cast ( @minute as varchar(10) )
193 end
194 else
195 begin
196 set @Minute30 = @minute
197 end
198
199 if @second <10
200 begin
201 set @Second30 = '0' + cast ( @second as varchar(10) )
202 end
203 else
204 begin
205 set @Second30 = @second
206 end
207 --Concatenate values for Time30
208 set @Time30 = @Hour30 +':'+@Minute30 +':'+@Second30
209
210 --DayTimeBucketGroupKey can be used in Sorting of DayTime Bucket In proper Order
211 SELECT @DayTimeBucketGroupKey =
212 CASE
213 WHEN (@TimeAltKey >= 00000 AND @TimeAltKey <= 25959) THEN 0
214 WHEN (@TimeAltKey >= 30000 AND @TimeAltKey <= 65959) THEN 1
215 WHEN (@TimeAltKey >= 70000 AND @TimeAltKey <= 85959) THEN 2
216 WHEN (@TimeAltKey >= 90000 AND @TimeAltKey <= 115959) THEN 3
217 WHEN (@TimeAltKey >= 120000 AND @TimeAltKey <= 135959)THEN 4
218 WHEN (@TimeAltKey >= 140000 AND @TimeAltKey <= 155959)THEN 5
219 WHEN (@TimeAltKey >= 50000 AND @TimeAltKey <= 175959) THEN 6
220 WHEN (@TimeAltKey >= 180000 AND @TimeAltKey <= 235959)THEN 7
221 WHEN (@TimeAltKey >= 240000) THEN 8
222 END
223 --print @DayTimeBucketGroupKey
224 -- DayTimeBucket Time Divided in Spcific Time Zone So Data can Be Grouped as per Bucket for Analyzing as per time of day
225 SELECT @DayTimeBucket =
226 CASE
227 WHEN (@TimeAltKey >= 00000 AND @TimeAltKey <= 25959) THEN 'Late Night (00:00 AM To 02:59 AM)'
228 WHEN (@TimeAltKey >= 30000 AND @TimeAltKey <= 65959) THEN 'Early Morning(03:00 AM To 6:59 AM)'
229 WHEN (@TimeAltKey >= 70000 AND @TimeAltKey <= 85959) THEN 'AM Peak (7:00 AM To 8:59 AM)'
230 WHEN (@TimeAltKey >= 90000 AND @TimeAltKey <= 115959) THEN 'Mid Morning (9:00 AM To 11:59 AM)'
231 WHEN (@TimeAltKey >= 120000 AND @TimeAltKey <= 135959) THEN 'Lunch (12:00 PM To 13:59 PM)'
232 WHEN (@TimeAltKey >= 140000 AND @TimeAltKey <= 155959)THEN 'Mid Afternoon (14:00 PM To 15:59 PM)'
233 WHEN (@TimeAltKey >= 50000 AND @TimeAltKey <= 175959)THEN 'PM Peak (16:00 PM To 17:59 PM)'
234 WHEN (@TimeAltKey >= 180000 AND @TimeAltKey <= 235959)THEN 'Evening (18:00 PM To 23:59 PM)'
235 WHEN (@TimeAltKey >= 240000) THEN 'Previous Day Late Night (24:00 PM to '+cast( @Size as varchar(10)) +':00 PM )'
236 END
237 -- print @DayTimeBucket
238
239 INSERT into DimTime (TimeKey,TimeAltKey,[Time30] ,[Hour30] ,[MinuteNumber],[SecondNumber],[TimeInSecond],[HourlyBucket],DayTimeBucketGroupKey,DayTimeBucket)
240 VALUES (@k,@TimeAltKey ,@Time30 ,@hour ,@minute,@Second , @TimeInSeconds,@HourBucket,@DayTimeBucketGroupKey,@DayTimeBucket )
241
242 SET @second = @second + 1
243 SET @k = @k + 1
244 END
245 SET @minute = @minute + 1
246 SET @second = 0
247 END
248
249 SET @hour = @hour + 1
250 SET @minute =0
251 END
252
253END
254
255Go
256
257Exec [FillDimTime]
258go
259
260BEGIN TRY
261 DROP TABLE [dbo].[DimDate]
262END TRY
263
264BEGIN CATCH
265 /*No Action*/
266END CATCH
267
268/**********************************************************************************/
269
270CREATE TABLE [dbo].[DimDate]
271 ( [DateKey] INT primary key,
272 [Date] DATETIME,
273 [FullDateUK] CHAR(10), -- Date in dd-MM-yyyy format
274 [FullDateUSA] CHAR(10),-- Date in MM-dd-yyyy format
275 [DayOfMonth] VARCHAR(2), -- Field will hold day number of Month
276 [DaySuffix] VARCHAR(4), -- Apply suffix as 1st, 2nd ,3rd etc
277 [DayName] VARCHAR(9), -- Contains name of the day, Sunday, Monday
278 [DayOfWeekUSA] CHAR(1),-- First Day Sunday=1 and Saturday=7
279 [DayOfWeekUK] CHAR(1),-- First Day Monday=1 and Sunday=7
280 [DayOfWeekInMonth] VARCHAR(2), --1st Monday or 2nd Monday in Month
281 [DayOfWeekInYear] VARCHAR(2),
282 [DayOfQuarter] VARCHAR(3),
283 [DayOfYear] VARCHAR(3),
284 [WeekOfMonth] VARCHAR(1),-- Week Number of Month
285 [WeekOfQuarter] VARCHAR(2), --Week Number of the Quarter
286 [WeekOfYear] VARCHAR(2),--Week Number of the Year
287 [Month] VARCHAR(2), --Number of the Month 1 to 12
288 [MonthName] VARCHAR(9),--January, February etc
289 [MonthOfQuarter] VARCHAR(2),-- Month Number belongs to Quarter
290 [Quarter] CHAR(1),
291 [QuarterName] VARCHAR(9),--First,Second..
292 [Year] CHAR(4),-- Year value of Date stored in Row
293 [YearName] CHAR(7), --CY 2012,CY 2013
294 [MonthYear] CHAR(10), --Jan-2013,Feb-2013
295 [MMYYYY] CHAR(6),
296 [FirstDayOfMonth] DATE,
297 [LastDayOfMonth] DATE,
298 [FirstDayOfQuarter] DATE,
299 [LastDayOfQuarter] DATE,
300 [FirstDayOfYear] DATE,
301 [LastDayOfYear] DATE,
302 [IsHolidayUSA] BIT,-- Flag 1=National Holiday, 0-No National Holiday
303 [IsWeekday] BIT,-- 0=Week End ,1=Week Day
304 [HolidayUSA] VARCHAR(50),--Name of Holiday in US
305 [IsHolidayUK] BIT Null, -- Flag 1=National Holiday, 0-No National Holiday
306 [HolidayUK] VARCHAR(50) Null --Name of Holiday in UK
307 )
308GO
309
310
311/********************************************************************************************/
312--Specify Start Date and End date here
313--Value of Start Date Must be Less than Your End Date
314
315DECLARE @StartDate DATETIME = '01/01/2013' --Starting value of Date Range
316DECLARE @EndDate DATETIME = '01/01/2015' --End Value of Date Range
317
318--Temporary Variables To Hold the Values During Processing of Each Date of Year
319DECLARE
320 @DayOfWeekInMonth INT,
321 @DayOfWeekInYear INT,
322 @DayOfQuarter INT,
323 @WeekOfMonth INT,
324 @CurrentYear INT,
325 @CurrentMonth INT,
326 @CurrentQuarter INT
327
328/*Table Data type to store the day of week count for the month and year*/
329DECLARE @DayOfWeek TABLE (DOW INT, MonthCount INT, QuarterCount INT, YearCount INT)
330
331INSERT INTO @DayOfWeek VALUES (1, 0, 0, 0)
332INSERT INTO @DayOfWeek VALUES (2, 0, 0, 0)
333INSERT INTO @DayOfWeek VALUES (3, 0, 0, 0)
334INSERT INTO @DayOfWeek VALUES (4, 0, 0, 0)
335INSERT INTO @DayOfWeek VALUES (5, 0, 0, 0)
336INSERT INTO @DayOfWeek VALUES (6, 0, 0, 0)
337INSERT INTO @DayOfWeek VALUES (7, 0, 0, 0)
338
339--Extract and assign part of Values from Current Date to Variable
340
341DECLARE @CurrentDate AS DATETIME = @StartDate
342SET @CurrentMonth = DATEPART(MM, @CurrentDate)
343SET @CurrentYear = DATEPART(YY, @CurrentDate)
344SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)
345
346/********************************************************************************************/
347--Proceed only if Start Date(Current date ) is less than End date you specified above
348
349WHILE @CurrentDate < @EndDate
350BEGIN
351
352/*Begin day of week logic*/
353
354 /*Check for Change in Month of the Current date if Month changed then
355 Change variable value*/
356 IF @CurrentMonth != DATEPART(MM, @CurrentDate)
357 BEGIN
358 UPDATE @DayOfWeek
359 SET MonthCount = 0
360 SET @CurrentMonth = DATEPART(MM, @CurrentDate)
361 END
362
363 /* Check for Change in Quarter of the Current date if Quarter changed then change
364 Variable value*/
365
366 IF @CurrentQuarter != DATEPART(QQ, @CurrentDate)
367 BEGIN
368 UPDATE @DayOfWeek
369 SET QuarterCount = 0
370 SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)
371 END
372
373 /* Check for Change in Year of the Current date if Year changed then change
374 Variable value*/
375
376
377 IF @CurrentYear != DATEPART(YY, @CurrentDate)
378 BEGIN
379 UPDATE @DayOfWeek
380 SET YearCount = 0
381 SET @CurrentYear = DATEPART(YY, @CurrentDate)
382 END
383
384 -- Set values in table data type created above from variables
385
386 UPDATE @DayOfWeek
387 SET
388 MonthCount = MonthCount + 1,
389 QuarterCount = QuarterCount + 1,
390 YearCount = YearCount + 1
391 WHERE DOW = DATEPART(DW, @CurrentDate)
392
393 SELECT
394 @DayOfWeekInMonth = MonthCount,
395 @DayOfQuarter = QuarterCount,
396 @DayOfWeekInYear = YearCount
397 FROM @DayOfWeek
398 WHERE DOW = DATEPART(DW, @CurrentDate)
399
400/*End day of week logic*/
401
402
403/* Populate Your Dimension Table with values*/
404
405 INSERT INTO [dbo].[DimDate]
406 SELECT
407
408 CONVERT (char(8),@CurrentDate,112) as DateKey,
409 @CurrentDate AS Date,
410 CONVERT (char(10),@CurrentDate,103) as FullDateUK,
411 CONVERT (char(10),@CurrentDate,101) as FullDateUSA,
412 DATEPART(DD, @CurrentDate) AS DayOfMonth,
413 --Apply Suffix values like 1st, 2nd 3rd etc..
414 CASE
415 WHEN DATEPART(DD,@CurrentDate) IN (11,12,13) THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'th'
416 WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 1 THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'st'
417 WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 2 THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'nd'
418 WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 3 THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'rd'
419 ELSE CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'th'
420 END AS DaySuffix,
421
422 DATENAME(DW, @CurrentDate) AS DayName,
423 DATEPART(DW, @CurrentDate) AS DayOfWeekUSA,
424 -- check for day of week as Per US and change it as per UK format
425 CASE DATEPART(DW, @CurrentDate)
426 WHEN 1 THEN 7
427 WHEN 2 THEN 1
428 WHEN 3 THEN 2
429 WHEN 4 THEN 3
430 WHEN 5 THEN 4
431 WHEN 6 THEN 5
432 WHEN 7 THEN 6
433 END
434 AS DayOfWeekUK,
435
436 @DayOfWeekInMonth AS DayOfWeekInMonth,
437 @DayOfWeekInYear AS DayOfWeekInYear,
438 @DayOfQuarter AS DayOfQuarter,
439 DATEPART(DY, @CurrentDate) AS DayOfYear,
440 DATEPART(WW, @CurrentDate) + 1 - DATEPART(WW, CONVERT(VARCHAR, DATEPART(MM, @CurrentDate)) + '/1/' + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate))) AS WeekOfMonth,
441 (DATEDIFF(DD, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0), @CurrentDate) / 7) + 1 AS WeekOfQuarter,
442 DATEPART(WW, @CurrentDate) AS WeekOfYear,
443 DATEPART(MM, @CurrentDate) AS Month,
444 DATENAME(MM, @CurrentDate) AS MonthName,
445 CASE
446 WHEN DATEPART(MM, @CurrentDate) IN (1, 4, 7, 10) THEN 1
447 WHEN DATEPART(MM, @CurrentDate) IN (2, 5, 8, 11) THEN 2
448 WHEN DATEPART(MM, @CurrentDate) IN (3, 6, 9, 12) THEN 3
449 END AS MonthOfQuarter,
450 DATEPART(QQ, @CurrentDate) AS Quarter,
451 CASE DATEPART(QQ, @CurrentDate)
452 WHEN 1 THEN 'First'
453 WHEN 2 THEN 'Second'
454 WHEN 3 THEN 'Third'
455 WHEN 4 THEN 'Fourth'
456 END AS QuarterName,
457 DATEPART(YEAR, @CurrentDate) AS Year,
458 'CY ' + CONVERT(VARCHAR, DATEPART(YEAR, @CurrentDate)) AS YearName,
459 LEFT(DATENAME(MM, @CurrentDate), 3) + '-' + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate)) AS MonthYear,
460 RIGHT('0' + CONVERT(VARCHAR, DATEPART(MM, @CurrentDate)),2) + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate)) AS MMYYYY,
461 CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, @CurrentDate) - 1), @CurrentDate))) AS FirstDayOfMonth,
462 CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, (DATEADD(MM, 1, @CurrentDate)))), DATEADD(MM, 1, @CurrentDate)))) AS LastDayOfMonth,
463 DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0) AS FirstDayOfQuarter,
464 DATEADD(QQ, DATEDIFF(QQ, -1, @CurrentDate), -1) AS LastDayOfQuarter,
465 CONVERT(DATETIME, '01/01/' + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate))) AS FirstDayOfYear,
466 CONVERT(DATETIME, '12/31/' + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate))) AS LastDayOfYear,
467 NULL AS IsHolidayUSA,
468 CASE DATEPART(DW, @CurrentDate)
469 WHEN 1 THEN 0
470 WHEN 2 THEN 1
471 WHEN 3 THEN 1
472 WHEN 4 THEN 1
473 WHEN 5 THEN 1
474 WHEN 6 THEN 1
475 WHEN 7 THEN 0
476 END AS IsWeekday,
477 NULL AS HolidayUSA, Null, Null
478
479 SET @CurrentDate = DATEADD(DD, 1, @CurrentDate)
480END
481
482
483
484
485
486
487/*Add HOLIDAYS UK*/
488
489-- Good Friday April 18
490 UPDATE [dbo].[DimDate]
491 SET HolidayUK = 'Good Friday'
492 WHERE [Month] = 4 AND [DayOfMonth] = 18
493-- Easter Monday April 21
494 UPDATE [dbo].[DimDate]
495 SET HolidayUK = 'Easter Monday'
496 WHERE [Month] = 4 AND [DayOfMonth] = 21
497-- Early May Bank Holiday May 5
498 UPDATE [dbo].[DimDate]
499 SET HolidayUK = 'Early May Bank Holiday'
500 WHERE [Month] = 5 AND [DayOfMonth] = 5
501-- Spring Bank Holiday May 26
502 UPDATE [dbo].[DimDate]
503 SET HolidayUK = 'Spring Bank Holiday'
504 WHERE [Month] = 5 AND [DayOfMonth] = 26
505-- Summer Bank Holiday August 25
506 UPDATE [dbo].[DimDate]
507 SET HolidayUK = 'Summer Bank Holiday'
508 WHERE [Month] = 8 AND [DayOfMonth] = 25
509-- Boxing Day December 26
510 UPDATE [dbo].[DimDate]
511 SET HolidayUK = 'Boxing Day'
512 WHERE [Month] = 12 AND [DayOfMonth] = 26
513--CHRISTMAS
514 UPDATE [dbo].[DimDate]
515 SET HolidayUK = 'Christmas Day'
516 WHERE [Month] = 12 AND [DayOfMonth] = 25
517--New Years Day
518 UPDATE [dbo].[DimDate]
519 SET HolidayUK = 'New Year''s Day'
520 WHERE [Month] = 1 AND [DayOfMonth] = 1
521
522 UPDATE [dbo].[DimDate]
523 SET IsHolidayUK = CASE WHEN HolidayUK IS NULL THEN 0 WHEN HolidayUK IS NOT NULL THEN 1 END
524
525
526 /*Add HOLIDAYS USA*/
527 /*THANKSGIVING - Fourth THURSDAY in November*/
528 UPDATE [dbo].[DimDate]
529 SET HolidayUSA = 'Thanksgiving Day'
530 WHERE
531 [Month] = 11
532 AND [DayOfWeekUSA] = 'Thursday'
533 AND DayOfWeekInMonth = 4
534
535 /*CHRISTMAS*/
536 UPDATE [dbo].[DimDate]
537 SET HolidayUSA = 'Christmas Day'
538
539 WHERE [Month] = 12 AND [DayOfMonth] = 25
540
541 /*4th of July*/
542 UPDATE [dbo].[DimDate]
543 SET HolidayUSA = 'Independance Day'
544 WHERE [Month] = 7 AND [DayOfMonth] = 4
545
546 /*New Years Day*/
547 UPDATE [dbo].[DimDate]
548 SET HolidayUSA = 'New Year''s Day'
549 WHERE [Month] = 1 AND [DayOfMonth] = 1
550
551 /*Memorial Day - Last Monday in May*/
552 UPDATE [dbo].[DimDate]
553 SET HolidayUSA = 'Memorial Day'
554 FROM [dbo].[DimDate]
555 WHERE DateKey IN
556 (
557 SELECT
558 MAX(DateKey)
559 FROM [dbo].[DimDate]
560 WHERE
561 [MonthName] = 'May'
562 AND [DayOfWeekUSA] = 'Monday'
563 GROUP BY
564 [Year],
565 [Month]
566 )
567
568 /*Labor Day - First Monday in September*/
569 UPDATE [dbo].[DimDate]
570 SET HolidayUSA = 'Labor Day'
571 FROM [dbo].[DimDate]
572 WHERE DateKey IN
573 (
574 SELECT
575 MIN(DateKey)
576 FROM [dbo].[DimDate]
577 WHERE
578 [MonthName] = 'September'
579 AND [DayOfWeekUSA] = 'Monday'
580 GROUP BY
581 [Year],
582 [Month]
583 )
584
585 /*Valentine's Day*/
586 UPDATE [dbo].[DimDate]
587 SET HolidayUSA = 'Valentine''s Day'
588 WHERE
589 [Month] = 2
590 AND [DayOfMonth] = 14
591
592 /*Saint Patrick's Day*/
593 UPDATE [dbo].[DimDate]
594 SET HolidayUSA = 'Saint Patrick''s Day'
595 WHERE
596 [Month] = 3
597 AND [DayOfMonth] = 17
598
599 /*Martin Luthor King Day - Third Monday in January starting in 1983*/
600 UPDATE [dbo].[DimDate]
601 SET HolidayUSA = 'Martin Luthor King Jr Day'
602 WHERE
603 [Month] = 1
604 AND [DayOfWeekUSA] = 'Monday'
605 AND [Year] >= 1983
606 AND DayOfWeekInMonth = 3
607
608 /*President's Day - Third Monday in February*/
609 UPDATE [dbo].[DimDate]
610 SET HolidayUSA = 'President''s Day'
611 WHERE
612 [Month] = 2
613 AND [DayOfWeekUSA] = 'Monday'
614 AND DayOfWeekInMonth = 3
615
616 /*Mother's Day - Second Sunday of May*/
617 UPDATE [dbo].[DimDate]
618 SET HolidayUSA = 'Mother''s Day'
619 WHERE
620 [Month] = 5
621 AND [DayOfWeekUSA] = 'Sunday'
622 AND DayOfWeekInMonth = 2
623
624 /*Father's Day - Third Sunday of June*/
625 UPDATE [dbo].[DimDate]
626 SET HolidayUSA = 'Father''s Day'
627 WHERE
628 [Month] = 6
629 AND [DayOfWeekUSA] = 'Sunday'
630 AND DayOfWeekInMonth = 3
631
632 /*Halloween 10/31*/
633 UPDATE [dbo].[DimDate]
634 SET HolidayUSA = 'Halloween'
635 WHERE
636 [Month] = 10
637 AND [DayOfMonth] = 31
638
639 /*Election Day - The first Tuesday after the first Monday in November*/
640 BEGIN
641 DECLARE @Holidays TABLE (ID INT IDENTITY(1,1), DateID int, Week TINYINT, YEAR CHAR(4), DAY CHAR(2))
642
643 INSERT INTO @Holidays(DateID, [Year],[Day])
644 SELECT
645 DateKey,
646 [Year],
647 [DayOfMonth]
648 FROM [dbo].[DimDate]
649 WHERE
650 [Month] = 11
651 AND [DayOfWeekUSA] = 'Monday'
652 ORDER BY
653 YEAR,
654 DayOfMonth
655
656 DECLARE @CNTR INT, @POS INT, @STARTYEAR INT, @ENDYEAR INT, @MINDAY INT
657
658 SELECT
659 @CURRENTYEAR = MIN([Year])
660 , @STARTYEAR = MIN([Year])
661 , @ENDYEAR = MAX([Year])
662 FROM @Holidays
663
664 WHILE @CURRENTYEAR <= @ENDYEAR
665 BEGIN
666 SELECT @CNTR = COUNT([Year])
667 FROM @Holidays
668 WHERE [Year] = @CURRENTYEAR
669
670 SET @POS = 1
671
672 WHILE @POS <= @CNTR
673 BEGIN
674 SELECT @MINDAY = MIN(DAY)
675 FROM @Holidays
676 WHERE
677 [Year] = @CURRENTYEAR
678 AND [Week] IS NULL
679
680 UPDATE @Holidays
681 SET [Week] = @POS
682 WHERE
683 [Year] = @CURRENTYEAR
684 AND [Day] = @MINDAY
685
686 SELECT @POS = @POS + 1
687 END
688
689 SELECT @CURRENTYEAR = @CURRENTYEAR + 1
690 END
691
692 UPDATE [dbo].[DimDate]
693 SET HolidayUSA = 'Election Day'
694 FROM [dbo].[DimDate] DT
695 JOIN @Holidays HL ON (HL.DateID + 1) = DT.DateKey
696 WHERE
697 [Week] = 1
698 END
699
700 UPDATE [dbo].[DimDate]
701 SET IsHolidayUSA = CASE WHEN HolidayUSA IS NULL THEN 0 WHEN HolidayUSA IS NOT NULL THEN 1 END
702
703/*******************************************************************************************************************************************************/
704
705
706--select * from DimDate
707
708
709--Script 2 fiscal calendar setting in Date dimension
710/*******************************************************************************************************************************************************/
711
712SELECT * FROM [dbo].[DimDate]
713
714
715/*Add Fiscal date columns to DimDate*/
716ALTER TABLE [dbo].[DimDate] ADD
717 [FiscalDayOfYear] VARCHAR(3),
718 [FiscalWeekOfYear] VARCHAR(3),
719 [FiscalMonth] VARCHAR(2),
720 [FiscalQuarter] CHAR(1),
721 [FiscalQuarterName] VARCHAR(9),
722 [FiscalYear] CHAR(4),
723 [FiscalYearName] CHAR(7),
724 [FiscalMonthYear] CHAR(10),
725 [FiscalMMYYYY] CHAR(6),
726 [FiscalFirstDayOfMonth] DATE,
727 [FiscalLastDayOfMonth] DATE,
728 [FiscalFirstDayOfQuarter] DATE,
729 [FiscalLastDayOfQuarter] DATE,
730 [FiscalFirstDayOfYear] DATE,
731 [FiscalLastDayOfYear] DATE
732
733 GO
734
735/*******************************************************************************************************************************************************
736The following section needs to be populated for defining the fiscal calendar
737*******************************************************************************************************************************************************/
738
739DECLARE
740 @dtFiscalYearStart SMALLDATETIME = 'January 01, 1995',
741 @FiscalYear INT = 1995,
742 @LastYear INT = 2025,
743 @FirstLeapYearInPeriod INT = 1996
744
745/*******************************************************************************************************************************************************/
746
747DECLARE
748 @iTemp INT,
749 @LeapWeek INT,
750 @CurrentDate DATETIME,
751 @FiscalDayOfYear INT,
752 @FiscalWeekOfYear INT,
753 @FiscalMonth INT,
754 @FiscalQuarter INT,
755 @FiscalQuarterName VARCHAR(10),
756 @FiscalYearName VARCHAR(7),
757 @LeapYear INT,
758 @FiscalFirstDayOfYear DATE,
759 @FiscalFirstDayOfQuarter DATE,
760 @FiscalFirstDayOfMonth DATE,
761 @FiscalLastDayOfYear DATE,
762 @FiscalLastDayOfQuarter DATE,
763 @FiscalLastDayOfMonth DATE
764
765/*Holds the years that have 455 in last quarter*/
766DECLARE @LeapTable TABLE (leapyear INT)
767
768/*TABLE to contain the fiscal year calendar*/
769DECLARE @tb TABLE(
770 PeriodDate DATETIME,
771 [FiscalDayOfYear] VARCHAR(3),
772 [FiscalWeekOfYear] VARCHAR(3),
773 [FiscalMonth] VARCHAR(2),
774 [FiscalQuarter] VARCHAR(1),
775 [FiscalQuarterName] VARCHAR(9),
776 [FiscalYear] VARCHAR(4),
777 [FiscalYearName] VARCHAR(7),
778 [FiscalMonthYear] VARCHAR(10),
779 [FiscalMMYYYY] VARCHAR(6),
780 [FiscalFirstDayOfMonth] DATE,
781 [FiscalLastDayOfMonth] DATE,
782 [FiscalFirstDayOfQuarter] DATE,
783 [FiscalLastDayOfQuarter] DATE,
784 [FiscalFirstDayOfYear] DATE,
785 [FiscalLastDayOfYear] DATE)
786
787/*Populate the table with all leap years*/
788SET @LeapYear = @FirstLeapYearInPeriod
789WHILE (@LeapYear < @LastYear)
790 BEGIN
791 INSERT INTO @leapTable VALUES (@LeapYear)
792 SET @LeapYear = @LeapYear + 5
793 END
794
795/*Initiate parameters before loop*/
796SET @CurrentDate = @dtFiscalYearStart
797SET @FiscalDayOfYear = 1
798SET @FiscalWeekOfYear = 1
799SET @FiscalMonth = 1
800SET @FiscalQuarter = 1
801SET @FiscalWeekOfYear = 1
802
803IF (EXISTS (SELECT * FROM @LeapTable WHERE @FiscalYear = leapyear))
804 BEGIN
805 SET @LeapWeek = 1
806 END
807 ELSE
808 BEGIN
809 SET @LeapWeek = 0
810 END
811
812/*******************************************************************************************************************************************************/
813
814/* Loop on days in interval*/
815WHILE (DATEPART(yy,@CurrentDate) <= @LastYear)
816BEGIN
817
818/*SET fiscal Month*/
819 SELECT @FiscalMonth = CASE
820 /*Use this section for a 4-5-4 calendar. Every leap year the result will be a 4-5-5*/
821 WHEN @FiscalWeekOfYear BETWEEN 1 AND 4 THEN 1 /*4 weeks*/
822 WHEN @FiscalWeekOfYear BETWEEN 5 AND 9 THEN 2 /*5 weeks*/
823 WHEN @FiscalWeekOfYear BETWEEN 10 AND 13 THEN 3 /*4 weeks*/
824 WHEN @FiscalWeekOfYear BETWEEN 14 AND 17 THEN 4 /*4 weeks*/
825 WHEN @FiscalWeekOfYear BETWEEN 18 AND 22 THEN 5 /*5 weeks*/
826 WHEN @FiscalWeekOfYear BETWEEN 23 AND 26 THEN 6 /*4 weeks*/
827 WHEN @FiscalWeekOfYear BETWEEN 27 AND 30 THEN 7 /*4 weeks*/
828 WHEN @FiscalWeekOfYear BETWEEN 31 AND 35 THEN 8 /*5 weeks*/
829 WHEN @FiscalWeekOfYear BETWEEN 36 AND 39 THEN 9 /*4 weeks*/
830 WHEN @FiscalWeekOfYear BETWEEN 40 AND 43 THEN 10 /*4 weeks*/
831 WHEN @FiscalWeekOfYear BETWEEN 44 AND (48+@LeapWeek) THEN 11 /*5 weeks*/
832 WHEN @FiscalWeekOfYear BETWEEN (49+@LeapWeek) AND (52+@LeapWeek) THEN 12 /*4 weeks (5 weeks on leap year)*/
833
834 /*Use this section for a 4-4-5 calendar. Every leap year the result will be a 4-5-5*/
835 /*
836 WHEN @FiscalWeekOfYear BETWEEN 1 AND 4 THEN 1 /*4 weeks*/
837 WHEN @FiscalWeekOfYear BETWEEN 5 AND 8 THEN 2 /*4 weeks*/
838 WHEN @FiscalWeekOfYear BETWEEN 9 AND 13 THEN 3 /*5 weeks*/
839 WHEN @FiscalWeekOfYear BETWEEN 14 AND 17 THEN 4 /*4 weeks*/
840 WHEN @FiscalWeekOfYear BETWEEN 18 AND 21 THEN 5 /*4 weeks*/
841 WHEN @FiscalWeekOfYear BETWEEN 22 AND 26 THEN 6 /*5 weeks*/
842 WHEN @FiscalWeekOfYear BETWEEN 27 AND 30 THEN 7 /*4 weeks*/
843 WHEN @FiscalWeekOfYear BETWEEN 31 AND 34 THEN 8 /*4 weeks*/
844 WHEN @FiscalWeekOfYear BETWEEN 35 AND 39 THEN 9 /*5 weeks*/
845 WHEN @FiscalWeekOfYear BETWEEN 40 AND 43 THEN 10 /*4 weeks*/
846 WHEN @FiscalWeekOfYear BETWEEN 44 AND (47+@leapWeek) THEN 11 /*4 weeks (5 weeks on leap year)*/
847 WHEN @FiscalWeekOfYear BETWEEN (48+@leapWeek) AND (52+@leapWeek) THEN 12 /*5 weeks*/
848 */
849 END
850
851 /*SET Fiscal Quarter*/
852 SELECT @FiscalQuarter = CASE
853 WHEN @FiscalMonth BETWEEN 1 AND 3 THEN 1
854 WHEN @FiscalMonth BETWEEN 4 AND 6 THEN 2
855 WHEN @FiscalMonth BETWEEN 7 AND 9 THEN 3
856 WHEN @FiscalMonth BETWEEN 10 AND 12 THEN 4
857 END
858
859 SELECT @FiscalQuarterName = CASE
860 WHEN @FiscalMonth BETWEEN 1 AND 3 THEN 'First'
861 WHEN @FiscalMonth BETWEEN 4 AND 6 THEN 'Second'
862 WHEN @FiscalMonth BETWEEN 7 AND 9 THEN 'Third'
863 WHEN @FiscalMonth BETWEEN 10 AND 12 THEN 'Fourth'
864 END
865
866 /*Set Fiscal Year Name*/
867 SELECT @FiscalYearName = 'FY ' + CONVERT(VARCHAR, @FiscalYear)
868
869 INSERT INTO @tb (PeriodDate, FiscalDayOfYear, FiscalWeekOfYear, fiscalMonth, FiscalQuarter, FiscalQuarterName, FiscalYear, FiscalYearName) VALUES
870 (@CurrentDate, @FiscalDayOfYear, @FiscalWeekOfYear, @FiscalMonth, @FiscalQuarter, @FiscalQuarterName, @FiscalYear, @FiscalYearName)
871
872 /*SET next day*/
873 SET @CurrentDate = DATEADD(dd, 1, @CurrentDate)
874 SET @FiscalDayOfYear = @FiscalDayOfYear + 1
875 SET @FiscalWeekOfYear = ((@FiscalDayOfYear-1) / 7) + 1
876
877
878 IF (@FiscalWeekOfYear > (52+@LeapWeek))
879 BEGIN
880 /*Reset a new year*/
881 SET @FiscalDayOfYear = 1
882 SET @FiscalWeekOfYear = 1
883 SET @FiscalYear = @FiscalYear + 1
884 IF ( EXISTS (SELECT * FROM @leapTable WHERE @FiscalYear = leapyear))
885 BEGIN
886 SET @LeapWeek = 1
887 END
888 ELSE
889 BEGIN
890 SET @LeapWeek = 0
891 END
892 END
893END
894
895/*******************************************************************************************************************************************************/
896
897/*Set first and last days of the fiscal months*/
898UPDATE @tb
899SET
900 FiscalFirstDayOfMonth = minmax.StartDate,
901 FiscalLastDayOfMonth = minmax.EndDate
902FROM
903@tb t,
904 (
905 SELECT FiscalMonth, FiscalQuarter, FiscalYear, MIN(PeriodDate) AS StartDate, MAX(PeriodDate) AS EndDate
906 FROM @tb
907 GROUP BY FiscalMonth, FiscalQuarter, FiscalYear
908 ) minmax
909WHERE
910 t.FiscalMonth = minmax.FiscalMonth AND
911 t.FiscalQuarter = minmax.FiscalQuarter AND
912 t.FiscalYear = minmax.FiscalYear
913
914/*Set first and last days of the fiscal quarters*/
915UPDATE @tb
916SET
917 FiscalFirstDayOfQuarter = minmax.StartDate,
918 FiscalLastDayOfQuarter = minmax.EndDate
919FROM
920@tb t,
921 (
922 SELECT FiscalQuarter, FiscalYear, min(PeriodDate) as StartDate, max(PeriodDate) as EndDate
923 FROM @tb
924 GROUP BY FiscalQuarter, FiscalYear
925 ) minmax
926WHERE
927 t.FiscalQuarter = minmax.FiscalQuarter AND
928 t.FiscalYear = minmax.FiscalYear
929
930/*Set first and last days of the fiscal years*/
931UPDATE @tb
932SET
933 FiscalFirstDayOfYear = minmax.StartDate,
934 FiscalLastDayOfYear = minmax.EndDate
935FROM
936@tb t,
937 (
938 SELECT FiscalYear, min(PeriodDate) as StartDate, max(PeriodDate) as EndDate
939 FROM @tb
940 GROUP BY FiscalYear
941 ) minmax
942WHERE
943 t.FiscalYear = minmax.FiscalYear
944
945/*Set FiscalYearMonth*/
946UPDATE @tb
947SET
948 FiscalMonthYear =
949 CASE FiscalMonth
950 WHEN 1 THEN 'Jan'
951 WHEN 2 THEN 'Feb'
952 WHEN 3 THEN 'Mar'
953 WHEN 4 THEN 'Apr'
954 WHEN 5 THEN 'May'
955 WHEN 6 THEN 'Jun'
956 WHEN 7 THEN 'Jul'
957 WHEN 8 THEN 'Aug'
958 WHEN 9 THEN 'Sep'
959 WHEN 10 THEN 'Oct'
960 WHEN 11 THEN 'Nov'
961 WHEN 12 THEN 'Dec'
962 END + '-' + CONVERT(VARCHAR, FiscalYear)
963
964/*Set FiscalMMYYYY*/
965UPDATE @tb
966SET
967 FiscalMMYYYY = RIGHT('0' + CONVERT(VARCHAR, FiscalMonth),2) + CONVERT(VARCHAR, FiscalYear)
968
969/*******************************************************************************************************************************************************/
970
971UPDATE [dbo].[DimDate]
972 SET
973 FiscalDayOfYear = a.FiscalDayOfYear
974 , FiscalWeekOfYear = a.FiscalWeekOfYear
975 , FiscalMonth = a.FiscalMonth
976 , FiscalQuarter = a.FiscalQuarter
977 , FiscalQuarterName = a.FiscalQuarterName
978 , FiscalYear = a.FiscalYear
979 , FiscalYearName = a.FiscalYearName
980 , FiscalMonthYear = a.FiscalMonthYear
981 , FiscalMMYYYY = a.FiscalMMYYYY
982 , FiscalFirstDayOfMonth = a.FiscalFirstDayOfMonth
983 , FiscalLastDayOfMonth = a.FiscalLastDayOfMonth
984 , FiscalFirstDayOfQuarter = a.FiscalFirstDayOfQuarter
985 , FiscalLastDayOfQuarter = a.FiscalLastDayOfQuarter
986 , FiscalFirstDayOfYear = a.FiscalFirstDayOfYear
987 , FiscalLastDayOfYear = a.FiscalLastDayOfYear
988FROM @tb a
989 INNER JOIN [dbo].[DimDate] b ON a.PeriodDate = b.[Date]
990
991/*******************************************************************************************************************************************************/
992
993
994
995Create Table FactProductSales
996(
997TransactionId bigint primary key identity,
998SalesInvoiceNumber int not null,
999SalesDateKey int,
1000SalesTimeKey int,
1001SalesTimeAltKey int,
1002StoreID int not null,
1003CustomerID int not null,
1004ProductID int not null,
1005SalesPersonID int not null,
1006Quantity float,
1007SalesTotalCost money,
1008ProductActualCost money,
1009Deviation float
1010)
1011Go
1012--Add Relation between Fact table and dimension tables.
1013
1014-- Add relation between fact table foreign keys to Primary keys of Dimensions
1015AlTER TABLE FactProductSales ADD CONSTRAINT FK_StoreID FOREIGN KEY (StoreID)REFERENCES DimStores(StoreID);
1016AlTER TABLE FactProductSales ADD CONSTRAINT FK_CustomerID FOREIGN KEY (CustomerID)REFERENCES Dimcustomer(CustomerID);
1017AlTER TABLE FactProductSales ADD CONSTRAINT FK_ProductKey FOREIGN KEY (ProductID)REFERENCES Dimproduct(ProductKey);
1018AlTER TABLE FactProductSales ADD CONSTRAINT FK_SalesPersonID FOREIGN KEY (SalesPersonID)REFERENCES Dimsalesperson(SalesPersonID);
1019Go
1020AlTER TABLE FactProductSales ADD CONSTRAINT FK_SalesDateKey FOREIGN KEY (SalesDateKey)REFERENCES DimDate(DateKey);
1021Go
1022AlTER TABLE FactProductSales ADD CONSTRAINT FK_SalesTimeKey FOREIGN KEY (SalesTimeKey)REFERENCES DimTIME(TimeKey);
1023Go
1024
1025--Populate your Fact table with historical transaction values of sales for previous day, with proper values of dimension key values.
1026
1027Insert into FactProductSales(SalesInvoiceNumber,SalesDateKey,SalesTimeKey,SalesTimeAltKey,StoreID,CustomerID,ProductID ,SalesPersonID,Quantity,ProductActualCost,SalesTotalCost,Deviation)values
1028--1-jan-2013
1029--SalesInvoiceNumber,SalesDateKey,SalesTimeKey,SalesTimeAltKey,StoreID,CustomerID,ProductID ,SalesPersonID,Quantity,ProductActualCost,SalesTotalCost,Deviation)
1030(1,20130101,44347,121907,1,1,1,1,2,11,13,2),
1031(1,20130101,44347,121907,1,1,2,1,1,22.50,24,1.5),
1032(1,20130101,44347,121907,1,1,3,1,1,42,43.5,1.5),
1033
1034(2,20130101,44519,122159,1,2,3,1,1,42,43.5,1.5),
1035(2,20130101,44519,122159,1,2,4,1,3,54,60,6),
1036
1037(3,20130101,52415,143335,1,3,2,2,2,11,13,2),
1038(3,20130101,52415,143335,1,3,3,2,1,42,43.5,1.5),
1039(3,20130101,52415,143335,1,3,4,2,3,54,60,6),
1040(3,20130101,52415,143335,1,3,5,2,1,135,139,4),
1041--2-jan-2013
1042--SalesInvoiceNumber,SalesDateKey,SalesTimeKey,SalesTimeAltKey,StoreID,CustomerID,ProductID ,SalesPersonID,Quantity,ProductActualCost,SalesTotalCost,Deviation)
1043(4,20130102,44347,121907,1,1,1,1,2,11,13,2),
1044(4,20130102,44347,121907,1,1,2,1,1,22.50,24,1.5),
1045
1046(5,20130102,44519,122159,1,2,3,1,1,42,43.5,1.5),
1047(5,20130102,44519,122159,1,2,4,1,3,54,60,6),
1048
1049(6,20130102,52415,143335,1,3,2,2,2,11,13,2),
1050(6,20130102,52415,143335,1,3,5,2,1,135,139,4),
1051
1052(7,20130102,44347,121907,2,1,4,3,3,54,60,6),
1053(7,20130102,44347,121907,2,1,5,3,1,135,139,4),
1054
1055--3-jan-2013
1056--SalesInvoiceNumber,SalesDateKey,SalesTimeKey,SalesTimeAltKey,StoreID,CustomerID,ProductID ,SalesPersonID,Quantity,ProductActualCost,SalesTotalCost,Deviation)
1057(8,20130103,59326,162846,1,1,3,1,2,84,87,3),
1058(8,20130103,59326,162846,1,1,4,1,3,54,60,3),
1059
1060
1061(9,20130103,59349,162909,1,2,1,1,1,5.5,6.5,1),
1062(9,20130103,59349,162909,1,2,2,1,1,22.50,24,1.5),
1063
1064(10,20130103,67390,184310,1,3,1,2,2,11,13,2),
1065(10,20130103,67390,184310,1,3,4,2,3,54,60,6),
1066
1067(11,20130103,74877,204757,2,1,2,3,1,5.5,6.5,1),
1068(11,20130103,74877,204757,2,1,3,3,1,42,43.5,1.5)
1069Go