在 T-SQL 中创建会计日历

2021-09-10 00:00:00 sql tsql sql-server

我正在尝试创建一个会计日历，其中会计年度从 7 月 1 日开始，一周被定义为周一至周日.

I am trying to create a fiscal calendar in which the fiscal year starts July 1 and a week is defined as Monday to Sunday.

但是例如；如果一个月中一周的第一天是星期六，那么星期六到星期日将被视为该月的第 1 周，新的一周从星期一开始，到星期日结束，依此类推.

But for example; if the 1st day in a week in a month is a Saturday, then Saturday to Sunday will be seen as 1 week in that month and the new week starts on Monday and ends on Sunday and so on.

请参阅下面我要创建的表格示例:

See sample of the table I want to create below:

  • 期间是指会计年度的月份.
  • Week 是该月的周数.
  • 开始日期(周开始日期)是一周开始的那一天
  • 结束日期(周结束日期)是一周结束的那一天.
  • 星期几是开始日期和结束日期之间的日期.
  • 年份

我想我需要一个程序，它可能需要会计年度的第一天，然后遍历一年中的所有日子，添加列开始和结束日期、周数、期间和日期所属的年份.

I am thinking that I need a procedure that maybe takes the first day of the fiscal year then iterates through all the days of the year adding the columns start and end date, week number, period and year the day belongs to.

推荐答案

另一种选择.这将生成 50 年是 0.703 秒

Yet another option. This will generate 50 years is 0.703 seconds

示例

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

相关文章