社区首页 >问答首页 >在T中创建一个财政日历

问在T中创建一个财政日历
EN

Stack Overflow用户

提问于 2017-07-13 07:00:23

回答 3查看 6.2K关注 0票数 3

我正在努力创建一个财政日历，其中财政年度从7月1日开始，一个星期被定义为星期一到星期天。

但是，例如，如果一个月中一周的第一天是星期六，那么星期六到星期日将被视为那个月的一周，而新的一周从星期一开始，到星期天结束等等。

请参阅下面要创建的表的示例：

期间是指财政年度的月份。
星期是那个月的星期数。
开始日期(周开始日期)是一周开始的日子。
结束日期(周结束日期)是周结束的一天。
一周中的一天是开始日期和结束日期之间的日期。
年

我在想，我需要一个程序，可能需要在财政年度的第一天，然后遍历一年中所有的日子，加上开始和结束日期、周数、期间和日所属的年份。

sql-server

tsql

sql

回答 3

Stack Overflow用户

回答已采纳

发布于 2017-07-13 08: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 D

票数 2

Stack Overflow用户

发布于 2017-07-13 07: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。

票数 0

Stack Overflow用户

发布于 2017-07-13 07: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 |
+--------+------+-------------------------+-------------------------+------------+------+