在T中创建一个财政日历
在T中创建一个财政日历
提问于 2017-07-13 15:00:23
我正在努力创建一个财政日历，其中财政年度从7月1日开始，一个星期被定义为星期一到星期天。
但是，例如，如果一个月中一周的第一天是星期六，那么星期六到星期日将被视为那个月的一周，而新的一周从星期一开始，到星期天结束等等。
请参阅下面要创建的表的示例：
- 期间是指财政年度的月份。
- 星期是那个月的星期数。
- 开始日期(周开始日期)是一周开始的日子。
- 结束日期(周结束日期)是周结束的一天。
- 一周中的一天是开始日期和结束日期之间的日期。
- 年
我在想，我需要一个程序，可能需要在财政年度的第一天，然后遍历一年中所有的日子，加上开始和结束日期、周数、期间和日所属的年份。
回答 3
Stack Overflow用户
回答已采纳
发布于 2017-07-13 16:03:14
还有另一种选择。这将产生50年是0.703秒
示例
Set DateFirst 1
Declare @Date1 date = '2017-07-01'
Declare @Date2 date = '2019-06-30'
Select Period = Dense_Rank() over (Partition By FY Order By FM)
,Week = Dense_Rank() over (Partition By FY,FM Order By FW)
,StartDate = Min(D) over (Partition By FY,FM,FW )
,EndDate = Max(D) over (Partition By FY,FM,FW )
,DayOfWeek = D
,Year = FY
From (
Select FY = DatePart(Year,@Date1)-1+sum(case when convert(varchar(5),@Date1,101)=convert(varchar(5),D,101) then 1 else 0 end) over (Order By D)
,FM = sum(case when DatePart(Day,D)=DatePart(Day,@Date1) then 1 else 0 end) over (Order By D)
,FW = sum(case when DatePart(WeekDay,D)=1 then 1 else 0 end) over (Order By D)
,D
From (
Select Top (DateDiff(DAY,@Date1,@Date2)+1)
D = DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),@Date1)
From master..spt_values n1,master..spt_values n2
) A1
) A
Order By DStack Overflow用户
发布于 2017-07-13 15:20:35
您不需要一个过程，但是有些函数可以方便地使用。
首先，请记住，一些日期时间函数依赖于@@DATEFIRST。要使您的代码独立于此设置，您可以使用一个使天数正常化的函数。
CREATE FUNCTION getNormalizedWeekDay
(
@inputDate DATE
, @dateFirst TINYINT = 1
)
RETURNS SMALLINT
AS
BEGIN
RETURN ((DATEPART(WEEKDAY, @inputDate) + @@DATEFIRST - 1 - @dateFirst) % 7) + 1;
END在这种情况下，您可以判断要使用哪一天，并根据该日期计算结果。例如，确定一天是否是星期天是很方便的。
如果我正确地理解了你，你想要一个周函数，它的行为类似于内置函数，但是把7月1日当作一年的第一天。
像这样的事情应该可以做到：
CREATE FUNCTION getFiscalWeekNumber
(
@inputDate DATE
, @firstFiscalMonth TINYINT = 7
, @dateFirst TINYINT = 1
)
RETURNS TINYINT
AS
BEGIN
/* NULL Input handling. */
IF (@inputDate IS NULL) BEGIN
RETURN NULL
END
DECLARE @inputDateWeekDay TINYINT = getNormalizedWeekDay(@inputDate, @dateFirst);
DECLARE @inputWeekStartDate DATE = DATEADD(DAY, -(@inputDateWeekDay-1), @inputDate);
DECLARE @firstDayOfYear DATE = DATEFROMPARTS(getFiscalYear(@inputDate, @firstFiscalMonth), @firstFiscalMonth, 1);
DECLARE @firstWeekStartDate DATE = @firstDayOfYear;
DECLARE @weekDayOfFirstDayOfYear TINYINT = getNormalizedWeekDay(@firstDayOfYear, @dateFirst);
-- The day is between the first day of year and the beginning of the second week -> 1st (partial) week for non-iso style
IF (@inputDate >= @firstDayOfYear AND @inputDate < DATEADD(DAY, -(@weekDayOfFirstDayOfYear-1), DATEADD(DAY, 7, @firstWeekStartDate))) BEGIN
RETURN 1;
END
-- Adjust the first day of the weeks to match with @dateFirst.
SET @firstWeekStartDate = DATEADD(DAY, -(@weekDayOfFirstDayOfYear-1), @firstWeekStartDate);
RETURN (DATEDIFF(DAY, @firstWeekStartDate, @inputWeekStartDate) / 7) + 1;
END请记住，一些函数可能不存在于Server的旧版本中，我使用的是Server 2016。
Stack Overflow用户
发布于 2017-07-13 15:31:24
您可以使用common table expression (简称cte)计数表创建此表。这涉及到手动指定前10行( select t from values...位)，然后在第二个cte中将其多次提交给自己，以指数方式创建更多行。因为我有6个交叉联接( t t1, t t2, t t3...部分)，所以我正在生成100万行(10^6)，然后计算出top子句中实际需要多少行，然后使用row_number生成一个迭代列表，该列表将作为天数添加到您的期间开始日期。
它的输出是一个可以应用函数的日期表，甚至可以跨多个财政年度工作。我建议您使用它创建一个Dates查找表，而不是每次运行一次：
declare @DateStart date = '20160701'
,@DateEnd date = '20170630';
with t(t) as (select t from (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(t))
,d(d) as (select top(datediff(d,@DateStart,@DateEnd)+1) dateadd(d,row_number() over (order by (select null))-1,@DateStart) from t t1,t t21,t t31,t t4,t t5,t t6)
select ((dense_rank() over (order by dateadd(m,datediff(m,0,d),0)) - 1) % 12) + 1 as [Period]
,dense_rank() over (partition by dateadd(m,datediff(m,0,d),0)
order by case when dateadd(d,1-datepart(dw,d),d) < dateadd(m,datediff(m,0,d),0)
then dateadd(m,datediff(m,0,d),0)
else dateadd(d,1-datepart(dw,d),d)
end) as [Week]
,case when dateadd(d,1-datepart(dw,d),d) < dateadd(m,datediff(m,0,d),0)
then dateadd(m,datediff(m,0,d),0)
else dateadd(d,1-datepart(dw,d),d)
end as StartDate
,case when dateadd(d,7-datepart(dw,d),d) > dateadd(m,datediff(m,0,d)+1,0)
then dateadd(d,-1,dateadd(m,datediff(m,0,d)+1,0))
else dateadd(d,7-datepart(dw,d),d)
end as EndDate
,d as DayOfWeek
,year(d) as [Year]
from d
order by d;输出：
+--------+------+-------------------------+-------------------------+------------+------+
| Period | Week | StartDate | EndDate | DayOfWeek | Year |
+--------+------+-------------------------+-------------------------+------------+------+
| 1 | 1 | 2016-07-01 00:00:00.000 | 2016-07-02 00:00:00.000 | 2016-07-01 | 2016 |
| 1 | 1 | 2016-07-01 00:00:00.000 | 2016-07-02 00:00:00.000 | 2016-07-02 | 2016 |
| 1 | 2 | 2016-07-03 00:00:00.000 | 2016-07-09 00:00:00.000 | 2016-07-03 | 2016 |
| 1 | 2 | 2016-07-03 00:00:00.000 | 2016-07-09 00:00:00.000 | 2016-07-04 | 2016 |
| 1 | 2 | 2016-07-03 00:00:00.000 | 2016-07-09 00:00:00.000 | 2016-07-05 | 2016 |
| 1 | 2 | 2016-07-03 00:00:00.000 | 2016-07-09 00:00:00.000 | 2016-07-06 | 2016 |
| 1 | 2 | 2016-07-03 00:00:00.000 | 2016-07-09 00:00:00.000 | 2016-07-07 | 2016 |
| 1 | 2 | 2016-07-03 00:00:00.000 | 2016-07-09 00:00:00.000 | 2016-07-08 | 2017 |
.
.
.
| 12 | 5 | 2017-06-25 00:00:00.000 | 2017-07-01 00:00:00.000 | 2017-06-28 | 2017 |
| 12 | 5 | 2017-06-25 00:00:00.000 | 2017-07-01 00:00:00.000 | 2017-06-29 | 2017 |
| 12 | 5 | 2017-06-25 00:00:00.000 | 2017-07-01 00:00:00.000 | 2017-06-30 | 2017 |
+--------+------+-------------------------+-------------------------+------------+------+页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/45084425
复制相关文章
相似问题
腾讯云开发者
