DimDate Revisited（ 视图和嵌套 While 的强大功能）
4.75/5 (4投票s)
直接生成 DimDate 值并确保其准确性
引言
随着时间的推移，我曾处理过多个 DimDate 的实现。其中一些存在细微的错误，尤其是在季度相关的数据中，在财政年度相关的值中更为突出。本文提供的 SQL，只需轻松修改“开始日期”和“结束日期”，就能以一种直接的方式，准确地生成您 DimDate 实现所需的值。相关的“DimDate.zip”文件包含了本文提供的所有 SQL。SQL 文件包括创建表和视图、填充表值、检查结果以及与其他实现进行比较的代码。
背景
为了以准确的数据方便地生成 DimDate 的值，我遵循以下两个总体设计原则。
DimDate实际上是一个视图 (vwDimDate)，它连接了适当的表，例如CalendarYear、FiscalYear、HolidayCalendar、国家或文化特定的日历、个人提醒日历等。这使得我们可以专注于生成领域特定的表，而无需复杂的逻辑将“许多”值适当地放入单个表中。然后，可以创建视图来呈现所需的数据。- 具有物理日历特征的表（年、季度、月、周、星期几）可以通过嵌套的
while循环准确生成，无需依赖复杂的if/else逻辑、内部支持表、表示季度不同配置的表等。考虑到任何一年都有季度，一季度有月份，一月有周，一周有星期几。使用嵌套的“While”语句是生成这种嵌套结构值的绝佳方法。
在使用相关的 SQL（SQL Server T-SQL）时，以下几点将非常有帮助：
- yyyymmdd – 以年、月（01..12）、日（01..31）的形式表示日期（这也恰好是一个有用的格式，可以用作文件名的前缀，以方便排序和后续专注于文件名）。
- mm/dd/yyyy – 以月（01..12）、日（01..31）、年的形式表示日期。
- 闰年（例如，二月有 29 天而不是 28 天）– 当年份为 yy00 时，yyyy 必须能被 400 整除才能成为闰年。否则，yyyy 必须能被 4 整除才能成为闰年。如果不能被 400 或 4 整除（即有余数），则该年不是闰年，该年的二月有 28 天。
- 世纪 – 通常认为 1..100 年（包括首尾）是第 1 世纪。101..200 年是第 2 世纪，以此类推。因此，1901..2000 年是 20 世纪，2001..2100 年是 21 世纪。
- 财政年度 – 一个 12 个月的会计周期，通常不与日历年（1/1/yyyy..12/31/yyyy）重合。例如，2017 财政年度可能指 2016 年 5 月 1 日至 2017 年 4 月 30 日。通常，当人们提到某个特定年份时，他们指的是该财政年度结束的日历年。**但是，绝不要假设一个特定的财政年度包含哪些日期。要问：“这个财政年度具体包含哪些日期？”这一点非常重要。**
- 月份 – 一月、二月……十一月、十二月
- 星期几 – 星期日、星期一……星期五、星期六。星期日被视为每周的第一天。
- 创建的属性是最小化的，应根据您的个人或组织需求进行修改（删除、添加、更改）。
生成日历值
考虑一个日历（CalendarYear 或 FiscalYear）由年组成。在每个日历年或财政年度中，都有季度（连续 3 个月），每个季度中有月，每个月中有周（行），每周中有星期几（列）。从物理上讲，每一年可以被看作是 12 个月，每个月有 6 周。每周有 7 天。这是一个物理“占位符”描述。它并不说明 6 周的每一行和每周的星期几都有值。实际值（月份中的天数）在周内、月内以及季度/年内的月内是“不规则”的。也就是说，每个月的第一天不一定总是在每周的同一天开始，月份的天数也不尽相同。使用嵌套的 while 循环是生成日历适当数据行的绝佳方法。逻辑只需正确处理任何“不规则性”。
数据保留策略
可以理解，DimDate 每年或更可能以年为单位不断增长。我们中的许多人往往会忽略删除“过时”的、不再需要的行。每当我们处理这个问题时，都需要遵守组织的“数据保留”策略。而且，重要的是，我们还需要确定在删除“不再需要”的行后，哪些“下游”报告、流程等可能会失败或产生不准确的结果。
Using the Code
首先，让我们创建 CalendarYear 和 FiscalYear 表。然后创建视图 - vwDimDate。CalendarYear 将被视为主要表，其他表（例如 FiscalYear）将与之连接。只要连接表中的属性名称（列名）是唯一的，视图就可以很简单，如所示。
-- Create CalendarYear
BEGIN TRY
DROP TABLE CalendarYear
END TRY
BEGIN CATCH
print 'Attempting to DROP TABLE ''CalendarYear'' - NOT FOUND.'
print 'continuing... to create table ''CalendarYear''.'
END CATCH
CREATE TABLE CalendarYear
( DateKey int primary key -- ccyy0m0d (ccyy, zero m or mm, zero d or dd)
,[Date] date
,[Date_mm/dd/yyyy] varchar(10) -- mm/dd/yyyy (leading zeros in mm, dd removed)
,Date_ddMMMyyyy CHAR(09) -- dMMMYYYY or ddMMMYYYY
,Century int -- 1901..2000 - 20th century,
-- 2001..2100 - 21st century, etc.
,[Year] int -- ccyy
,IsLeapYear bit -- 0 = not a leap year, 1 = is a leap year
,QuarterOfYear int -- cardinal number of quarter of year (1..4)
,QuarterOfYearName varchar(06) -- First, Second, Third, Fourth
-- (ordinal name for calendar quarter)
,FirstOfQuarter date -- first date in quarter of year
,LastOfQuarter date -- last date in quarter of year
,MonthOfQuarter int -- 1..3 (calendar quarter)
,WeekOfQuarter int -- 1..14 (calendar quarter)
,DayOfQuarter int -- 1..92 (calendar quarter)
,MonthOfYear int -- cardinal number of month of year as d or dd
,MonthOfYearNameLong varchar(09) -- January.February.March.April.May.June.
-- July.August.September.October.
-- November.December
,MonthOfYearNameShort char(03) -- Jan.Feb.Mar.Apr.May.Jun.Jul.Aug.
-- Sep.Oct.Nov.Dec
,WeekOfMonth int -- 1..6
,[DayOfMonth] int -- cardinal number of day of month
,DayOfMonthOrdinal varchar(04) -- 1st, 2nd, 3rd, 4th, etc.
-- (ordinal number of day of month)
,WeekOfYear int -- 1..53
,[DayOfYear] int -- cardinal number of day of year
,IsWeekDay bit -- 0 = Saturday or Sunday, 1 = Monday..Friday
,[DayOfWeek] int -- 1..7 1 = Sunday...7 = Saturday
,DayOfWeekNameLong VARCHAR(09) -- Sunday.Monday.Tuesday.Wednesday.
-- Thursday.Friday.Saturday
,DayOfWeekNameShort char(03) -- Sun.Mon.Tue.Wed.Thu.Fri.Sat
)
GO
-- CreateFiscalYear
BEGIN TRY
DROP TABLE FiscalYear
END TRY
BEGIN CATCH
print 'Attempting to DROP TABLE ''FiscalYear'' - NOT FOUND.'
print 'continuing... to create table ''FiscalYear''.'
END CATCH
CREATE TABLE FiscalYear
( FYDateKey int primary key -- ccyy0m0d (ccyy, zero m or mm, zero d or dd)
,FYDate date
,FYYear int -- fiscal year as ccyy
,FYQuarterOfYear int -- cardinal number of quarter of year (1..4)
,FYQuarterOfYearName varchar(06) -- First, Second, Third, Fourth
-- (ordinal name for fiscal quarter)
,FYFirstOfQuarter date -- first date in quarter of fiscal year
,FYLastOfQuarter date -- last date in quarter of fiscal year
,FYMonthOfQuarter int -- 1..3 (fiscal quarter)
,FYWeekOfQuarter int -- 1..14 (fiscal quarter)
,FYDayOfQuarter int -- 1..92 (fiscal quarter)
,FYMonthOfYear int -- cardinal number of month of
-- fiscal year as d or dd
,FYWeekOfYear int -- 1..53
,FYDayOfYear int -- cardinal number of day of fiscal year
)
go
-- Create vwDimDate
BEGIN TRY
DROP VIEW vwDimDate
END TRY
BEGIN CATCH
print 'Attempting to DROP VIEW ''vwDimDate'' - NOT FOUND.'
print 'continuing... to create view ''vwDimDate''.'
END CATCH
go
create view vwDimDate
as
select CalendarYear.*, FiscalYear.*
from CalendarYear
left join FiscalYear
on CalendarYear.DateKey = FiscalYear.FYDateKey
go
Using the Code
其次，让我们填充 CalendarYear 表。仔细检查带有“<-- Modify!”的所有语句，并根据您的实现修改值。@StartDate 是您用来指定要为其生成值的第一个日历年的 1 月 1 日的变量。@EndDate 是您用来指定要为其生成值的最后一个日历年的 12 月 31 日的变量。提供的 SQL 为 2017..2021 年的日历年生成值。
-- Populate CalendarYear
set nocount on -- Do NOT show number of rows affected.
set datefirst 7 -- Specify Sunday as the first day of the week.
set noexec off -- Specify execution of compiled SQL.
-- mm/dd/yyyy
declare @StartDate date = '01/01/2017' -- Starting value of Date Range as
-- month, day, year <-- Modify!
declare @EndDate date = '12/31/2021' -- Ending Value of Date Range as
-- month, day, year <-- Modify!
-- Produce calendar years for: 2017, 2018, 2019, 2020, 2021 <-- Modify!
-- Nothing below here to modify.
-- Calendar years cover 1/1/yyyy..12/31/yyyy.
if @StartDate > @EndDate -- If true, ensures production of 0 rows.
begin
print 'StartDate ' +
convert(char(08),@StartDate,112) +
' is greater than EndDate ' +
convert(char(08),@EndDate,112) +
'.'
set noexec on -- End execution.
end
if datepart(month,@StartDate) <> 1 or datepart(day,@StartDate) <> 1 -- starting date
-- is not 1/1/yyyy?
begin -- yes, ensure
-- production of 0 rows
print 'Month ' +
cast(datepart(month,@StartDate) as varchar) +
' or day ' +
cast(datepart(day,@StartDate) as varchar) +
' in ' +
convert(char(08),@StartDate,112) +
' is not ''1''. Both must be ''1'' for calendar year.'
set noexec on -- End execution.
end
if ((datediff(month,@StartDate,@EndDate)+1) % 12) <> 0 -- Number of months
-- not a multiple of 12?
begin -- yes, ensure
-- production of 0 rows
print convert(char(08),@StartDate,112) +
'...' +
convert(char(08),@EndDate,112) +
' covers ' +
cast(datediff(month,@StartDate,@EndDate) + 1 as varchar) +
' months. Not a multiple of 12.'
set noexec on -- End execution.
end
declare @CurrentDate date = @StartDate
declare @RunningYear int = datepart(year,@StartDate)
declare @EndingYear int = datepart(year,@EndDate)
declare @QuarterOfYear int
declare @MonthOfYear int
declare @MonthOfQuarter int
declare @WeekOfYear int
declare @WeekOfQuarter int
declare @WeekOfMonth int
declare @DayOfYear int
declare @DayOfQuarter int
declare @DayOfMonth int
declare @FirstOfQuarter date
while @RunningYear <= @EndingYear
begin -- Begin year loop.
set @QuarterOfYear = 1
set @MonthOfYear = 1
set @WeekOfYear = 1
set @DayOfYear = 1
set @FirstOfQuarter = @CurrentDate
while @QuarterOfYear <= 4
begin -- Begin quarter loop.
set @MonthOfQuarter = 1
set @WeekOfQuarter = 1
set @DayOfQuarter = 1
while @MonthOfQuarter <= 3
begin -- Begin month loop.
set @WeekOfMonth = 1
set @DayOfMonth = 1
while @WeekOfMonth <= 6
-- Since weeks in months are ragged this loop will never end on WeekOfMonth = 7.
-- Rather, an arbitrary large value (32767) will force loop end when month changes.
-- The minimum value 6 is coded above to ensure the correct number of loop iterations.
begin -- Begin week loop.
-- Begin insert row.
INSERT INTO CalendarYear
SELECT
CONVERT (char(8),@CurrentDate,112) -- DateKey
,@CurrentDate -- Date
,cast(datepart(mm,@CurrentDAte) as varchar) +
'/' +
cast(datepart(dd,@CurrentDate) as varchar) +
'/' +
cast(datepart(yy,@CurrentDate) as varchar) -- Date_mm/dd/yyyy
,cast(datepart(dd,@CurrentDate) as varchar) +
upper(left(datename(mm,@CurrentDate),3)) +
cast(datepart(yy,@CurrentDate) as varchar) -- Date_ddMMMyyyy
,case
when datepart(year,@CurrentDate) % 100 = 0
then datepart(year,@CurrentDate) / 100
else (datepart(year,@CurrentDate) / 100) + 1
end -- Century
,datepart(year,@CurrentDate) -- Year
,case -- 1900 is not a leap year, 2000 is a leap year
when(DATEPART(year,@CurrentDate) % 4 = 0
and
DATEPART(year,@CurrentDAte) % 100 <> 0)
or
DATEPART(year,@CurrentDAte) % 400 = 0
then 1 else 0
end -- IsLeapYear
,@QuarterOfYear -- QuarterOfYear -- either this or ...
-- ,DATEPART(quarter,@CurrentDate) -- QuarterOfYear -- ... this works
-- for calendar year
,case @QuarterOfYear
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
WHEN 4 THEN 'Fourth'
END -- QuarterOfYearName
,@FirstOfQuarter -- FirstOfQuarter
,dateadd(day,-1,dateadd(month,3,@FirstOfQuarter)) -- LastOfQuarter
,@MonthOfQuarter -- MonthOfQuarter
,@WeekOfQuarter -- WeeekOfQuarter
,@DayOfQuarter -- DayOfquarter
,@MonthOfyear -- MonthOfyear -- either this or ...
-- ,datepart(mm,@CurrentDate) -- MonthOfYear -- ... this works for
-- calendar year
,DATENAME(MM, @CurrentDate) -- MonthOfYearNameLong
,LEFT(DATENAME(MM, @CurrentDate), 3)-- MonthOfYearNameShort
,@WeekOfMonth -- WeekOfMonth
,@DayOfMonth -- DayOfMonth
,CASE
WHEN DATEPART(day,@CurrentDate) IN (11,12,13)
THEN CAST(DATEPART(day,@CurrentDate) AS VARCHAR) + 'th'
WHEN RIGHT(DATEPART(day,@CurrentDate),1) = 1
THEN CAST(DATEPART(day,@CurrentDate) AS VARCHAR) + 'st'
WHEN RIGHT(DATEPART(day,@CurrentDate),1) = 2
THEN CAST(DATEPART(day,@CurrentDate) AS VARCHAR) + 'nd'
WHEN RIGHT(DATEPART(day,@CurrentDate),1) = 3
THEN CAST(DATEPART(day,@CurrentDate) AS VARCHAR) + 'rd'
ELSE CAST(DATEPART(day,@CurrentDate) AS VARCHAR) + 'th'
END -- DayOfMonthOrdinal
,@WeekOfYear -- WeekOfYear
,@DayOfYear -- DayOfYear
,CASE DATEPART(weekday, @CurrentDate) -- dependent on 'set datefirst 7'
-- to indicate week begins on Sunday
WHEN 1 THEN 0 -- Sunday
WHEN 2 THEN 1 -- Monday
WHEN 3 THEN 1 -- Tuesday
WHEN 4 THEN 1 -- Wednesday
WHEN 5 THEN 1 -- Thursday
WHEN 6 THEN 1 -- Friday
WHEN 7 THEN 0 -- Saturday
END -- IsWeekday
,DATEPART(weekday,@CurrentDate) -- DayOfWeek
,DATENAME(weekday, @CurrentDate)-- DayOfWeekNameLong
,LEFT(DATENAME(weekday, @CurrentDate), 3) -- DayOfWeekNameShort
-- End insert row.
set @DayOfYear = @DayOfYear + 1 -- increment for next
-- loop interation
set @DayOfQuarter = @DayOfQuarter + 1 -- ...
set @DayOfMonth = @DayOfMonth + 1 -- ...
if DATEPART(weekday,@CurrentDate) = 7 -- Did a Sat just get inserted?
begin -- yes, it's a new week for next row
set @WeekOfMonth = @WeekOfMonth + 1
set @WeekOfQuarter = @WeekOfQuarter + 1
set @WeekOfYear = @WeekOfYear + 1
end
-- Look ahead to see if next day is in next month.
if datepart(month,@CurrentDate) <> _
datepart(month,dateadd(dd,1,@CurrentDate)) -- Month change?
begin -- yes
set @MonthOfYear = @MonthOfYear + 1
set @MonthOfQuarter = @MonthOfQuarter + 1
set @WeekOfMonth = 32767 -- Force end of week loop.
end
set @CurrentDate = dateadd(DD, 1, @CurrentDate) -- Increment to next day.
end -- End week loop.
set @WeekOfMonth = @WeekOfMonth + 1 -- increment for next iteration
-- in month loop
end --End month loop.
set @QuarterOfYear = @QuarterOfYear + 1 -- increment for next iteration
-- in quarter loop
set @FirstOfQuarter = dateadd(month,3,@FirstOfQuarter) -- new FirstOfQuarter
end -- End quarter loop.
set @RunningYear = @RunningYear + 1 -- increment for next iteration
-- in year loop
end -- End year loop.
go
Using the Code
第三，让我们填充 FiscalYear 表。仔细检查带有“<-- Modify!”的所有语句，并根据您的实现修改值。@StartDate 是您用来指定要为其生成值的第一个财政年度的第 1 个月第 1 天的变量。@EndDate 是您用来指定要为其生成值的最后一个财政年度的最后一个月的最后一天的变量。提供的 SQL 为 2017..2022 年的财政年度生成值，其中每个财政年度从 5 月 1 日开始，到 4 月 30 日结束。@StartFiscalYear 和 @EndFiscalYear 是您分别指定第一个和最后一个财政年度的变量。因此，提供的 SQL 生成的值覆盖 20160501..20220430。
现在，您和其他人可以开始受益于使用 vwDimDate 了。
-- Populate FiscalYear
set nocount on -- Do NOT show number of rows affected.
set datefirst 7 -- Specify Sunday as the first day of the week.
set noexec off -- Specify execution of compiled SQL.
-- mm/dd/yyyy
declare @StartDate date = '05/01/2016' -- Starting value of Date Range as month,
-- day, year <-- Modify!
declare @EndDate date = '04/30/2022' -- Ending Value of Date Range as month,
-- day, year <-- Modify!
declare @StartFiscalYear int = 2017 -- Starting value of fiscal year <-- Modify!
declare @EndFiscalYear int = 2022 -- Ending value of fiscal year <-- Modify!
-- Produce fiscal years for: 2017, 2018, 2019, 2020, 2021, 2022 <-- Modify!
-- Fiscal years cover 5/1/yyyy..4/30/yyyy+1. <-- Modify!
-- Nothing below here to modify.
if @StartDate > @EndDate -- If true, ensures production of 0 rows.
begin
print 'StartDate ' +
convert(char(08),@StartDate,112) +
' is greater than EndDate ' +
convert(char(08),@EndDate,112) +
'.'
set noexec on -- End execution.
end
if datepart(day,@StartDate) <> 1 -- starting date is not mm/1/yyyy?
begin -- yes, ensure production of 0 rows
print 'Day ' +
cast(datepart(day,@StartDate) as varchar) +
' in ' +
convert(char(08),@StartDate,112) +
' is not ''1''. Must be ''1'' for fiscal year.'
set noexec on -- End execution.
end
if ((datediff(month,@StartDate,@EndDate)+1) % 12) <> 0 -- Number of months not a
-- multiple of 12?
begin -- yes, ensure production
-- of 0 rows
print convert(char(08),@StartDate,112) +
'...' +
convert(char(08),@EndDate,112) +
' covers ' +
cast(datediff(month,@StartDate,@EndDate) + 1 as varchar) +
' months. Not a multiple of 12.'
set noexec on -- End execution.
end
declare @CurrentDate date = @StartDate
declare @RunningYear int = @StartFiscalYear
declare @EndingYear int = @EndFiscalYear
declare @QuarterOfYear int
declare @MonthOfYear int
declare @MonthOfQuarter int
declare @WeekOfYear int
declare @WeekOfQuarter int
declare @WeekOfMonth int
declare @DayOfYear int
declare @DayOfQuarter int
declare @DayOfMonth int
declare @FirstOfQuarter date
while @RunningYear <= @EndingYear
begin -- Begin year loop.
set @QuarterOfYear = 1
set @MonthOfYear = 1
set @WeekOfYear = 1
set @DayOfYear = 1
set @FirstOfQuarter = @CurrentDate
while @QuarterOfYear <= 4
begin -- Begin quarter loop.
set @MonthOfQuarter = 1
set @WeekOfQuarter = 1
set @DayOfQuarter = 1
while @MonthOfQuarter <= 3
begin -- Begin month loop.
set @WeekOfMonth = 1
set @DayOfMonth = 1
while @WeekOfMonth <= 6
-- Since weeks in months are ragged this loop will never end on WeekOfMonth = 7.
-- Rather, an arbitrary large value (32767) will force loop end when month changes.
-- The minimum value 6 is coded above to ensure the correct number of loop iterations.
begin -- Begin week loop.
-- Begin insert row.
INSERT INTO FiscalYear
SELECT
CONVERT (char(8),@CurrentDate,112) -- FYDateKey
,@CurrentDate -- FYDate
,@RunningYear -- FYYear
,@QuarterOfYear -- FYQuarterOfYear
,case @QuarterOfYear
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
WHEN 4 THEN 'Fourth'
END -- FYQuarterOfYearName
,@FirstOfQuarter -- FYFirstOfQuarter
,dateadd(day,-1,dateadd(month,3,@FirstOfQuarter)) -- FYLastOfQuarter
,@MonthOfQuarter -- FYMonthOfQuarter
,@WeekOfQuarter -- FYWeeekOfQuarter
,@DayOfQuarter -- FYDayOfquarter
,@MonthOfyear -- FYMonthOfyear
,@WeekOfYear -- FYWeekOfYear
,@DayOfYear -- FYDayOfYear
-- End insert row.
set @DayOfYear = @DayOfYear + 1 -- increment for
-- next loop interation
set @DayOfQuarter = @DayOfQuarter + 1 -- ...
set @DayOfMonth = @DayOfMonth + 1 -- ...
if DATEPART(weekday,@CurrentDate) = 7 -- Did a Sat just get inserted?
begin -- yes, it's a new week for next row
set @WeekOfMonth = @WeekOfMonth + 1
set @WeekOfQuarter = @WeekOfQuarter + 1
set @WeekOfYear = @WeekOfYear + 1
end
-- Look ahead to see if next day is in next month.
if datepart(month,@CurrentDate) <> _
datepart(month,dateadd(dd,1,@CurrentDate)) -- Month change?
begin
set @MonthOfYear = @MonthOfYear + 1 -- yes
set @MonthOfQuarter = @MonthOfQuarter + 1
set @WeekOfMonth = 32767 -- Force end of week loop.
end
set @CurrentDate = dateadd(DD, 1, @CurrentDate) -- Increment to next day.
end -- End week loop.
set @WeekOfMonth = @WeekOfMonth + 1 -- increment for next iteration
-- in month loop
end --End month loop.
set @QuarterOfYear = @QuarterOfYear + 1 -- increment for next iteration
-- in quarter loop
set @FirstOfQuarter = dateadd(month,3,@FirstOfQuarter) -- new FirstOfQuarter
end -- End quarter loop.
set @RunningYear = @RunningYear + 1 -- increment for next iteration
-- in year loop
end -- End year loop.
go
Using the Code
让我们检查填充表的结果。
-- CheckResults
select 'Data for today requested at ' +
convert(char(05),getdate(),114) +
'h -->' RequestTStamp, *
from vwDimDate
where DateKey
=
convert(char(08),getdate(),112)
select * from vwDimDate order by DateKey
Using the Code
将此（vwDimDate）实现与其他实现进行比较。下面的 SQL 是将此 vwDimdate 实现与其他 'DimDate' 实现进行比较的框架。
-- CompareToOther
-- This SQL can be modified to compare specific column values of
-- THIS (vwDimDate implementation)
-- to an existing DimDate.
-- Modify table name or column name appearing under string of 'v's as needed.
-- This will show discrepancies for the columns specified in the where clause below.
-- Any differences should be reviewed and action taken as necessary.
select This.DateKey
,This.FYYear
-- vvvvvvv
,Other.DateKey -- <-- Modify!
-- vvvvvvvvvv
,Other.FiscalYear -- <-- Modify!
from vwDimDate This
join
-- vvvvvvv
DimDate Other -- <-- Modify!
-- vvvvvvv
on This.DateKey = Other.DateKey -- <-- Modify!
-- vvvvvvvvvv
where This.FYYear <> Other.FiscalYear -- <-- Modify!
关注点
- 使用视图有助于专注开发和准确填充领域特定的表。
- 嵌套的“
While”循环有助于生成准确的值，尤其对于嵌套的数据结构。 - “准确第一，整洁第二，速度会随之而来。”
历史
- 2017 年 12 月 11 日：初始版本