問題描述
我正在嘗試創建一個會計日歷，其中會計年度從 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
這篇關于在 T-SQL 中創建會計日歷的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！