13期间日历4-4-5日历T-SQL MSSQL

Question

我试图在mssql中创建一个13期的日历，但我有点卡住了。我不确定我的方法是否是实现这一目标的最佳方式。我有我的基本脚本，如下所示：

Set DateFirst  1

Declare @Date1 date = '20180101' --startdate should always be start of 
financial year
Declare @Date2 date = '20181231' --enddate should always be start of 
financial year

SELECT * INTO #CalendarTable
FROM dbo.CalendarTable(@Date1,@Date2,0,0,0)c
DECLARE @StartDate datetime,@EndDate datetime

SELECT @StartDate=MIN(CASE WHEN [Day]='Monday' THEN [Date] ELSE NULL END),
@EndDate=MAX([Date])
FROM #CalendarTable

;With Period_CTE(PeriodNo,Start,[End])
AS
(SELECT 1,@StartDate,DATEADD(wk,4,@StartDate) -1
UNION ALL
SELECT PeriodNo+1,DATEADD(wk,4,Start),DATEADD(wk,4,[End])
FROM Period_CTE
WHERE DATEADD(wk,4,[End])< =@EndDate
OR PeriodNo+1 <=13
)
select * from Period_CTE

这给了我这样的结论：

PeriodNo Start End 1 2018-01-01 00:00:00.000 2018-01-28 00:00:00.000 2 2018-01-29 00:00:00.000 2018-02-25 00:00:00.000 3 2018-02-26 00:00:00.000 2018-03-25 00:00:00.000 4 2018-03-26 00:00:00.000 2018-04-22 00:00:00.000 5 2018-04-23 00:00:00.000 2018-05-20 00:00:00.000 6 2018-05-21 00:00:00.000 2018-06-17 00:00:00.000 7 2018-06-18 00:00:00.000 2018-07-15 00:00:00.000 8 2018-07-16 00:00:00.000 2018-08-12 00:00:00.000 9 2018-08-13 00:00:00.000 2018-09-09 00:00:00.000 10 2018-09-10 00:00:00.000 2018-10-07 00:00:00.000 11 2018-10-08 00:00:00.000 2018-11-04 00:00:00.000 12 2018-11-05 00:00:00.000 2018-12-02 00:00:00.000 13 2018-12-03 00:00:00.000 2018-12-30 00:00:00.000

我想要得到的结果是

​

即使我不得不采取不同的方法，我也不会介意，只要结果和上面的一样。

dbo.CalendarTable()是一个返回以下结果的函数。如果需要，我可以分享代码。

​

​

Answer 1

我将创建一个通用数字的表like suggested here并添加一个列Periode13。

获得平铺的诀窍是整数除法：

DECLARE @PeriodeSize INT=28; --13 "moon-months" a 28 days

SELECT TOP 100 (ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1)/@PeriodeSize
FROM master..spt_values --just a table with many rows to show the principles

您可以使用简单的update语句将其添加到现有的numbers表中。

更新完整的工作示例(使用上面链接的逻辑)

DECLARE @RunningNumbers TABLE  (Number INT NOT NULL
                               ,CalendarDate DATE NOT NULL
                               ,CalendarYear INT NOT NULL
                               ,CalendarMonth INT NOT NULL
                               ,CalendarDay INT NOT NULL
                               ,CalendarWeek INT NOT NULL
                               ,CalendarYearDay INT NOT NULL
                               ,CalendarWeekDay INT NOT NULL);

DECLARE @CountEntries INT = 100000;
DECLARE @StartNumber INT = 0;


WITH E1(N) AS(SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)), --10 ^ 1
    E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b), -- 10 ^ 2 = 100 rows
    E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b), -- 10 ^ 4 = 10,000 rows
    E8(N) AS(SELECT 1 FROM E4 a CROSS JOIN E4 b), -- 10 ^ 8 = 10,000,000 rows
    CteTally AS
    (
        SELECT TOP(ISNULL(@CountEntries,1000000)) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) -1 + ISNULL(@StartNumber,0) As Nmbr
        FROM E8
    )
INSERT INTO @RunningNumbers
SELECT CteTally.Nmbr,CalendarDate.d,CalendarExt.*
FROM CteTally
CROSS APPLY
(
    SELECT DATEADD(DAY,CteTally.Nmbr,{ts'2018-01-01 00:00:00'})
) AS CalendarDate(d)
CROSS APPLY
(
    SELECT YEAR(CalendarDate.d) AS CalendarYear
          ,MONTH(CalendarDate.d) AS CalendarMonth
          ,DAY(CalendarDate.d) AS CalendarDay
          ,DATEPART(WEEK,CalendarDate.d) AS CalendarWeek
          ,DATEPART(DAYOFYEAR,CalendarDate.d) AS CalendarYearDay
          ,DATEPART(WEEKDAY,CalendarDate.d) AS CalendarWeekDay
) AS CalendarExt;

--上面的样本表现在已经填充，可以查询了

 WITH AddPeriode AS
(
    SELECT     Number/28 +1 AS PeriodNumber
              ,CalendarDate
              ,CalendarWeek
              ,r.CalendarDay 
              ,r.CalendarMonth 
              ,r.CalendarWeekDay 
              ,r.CalendarYear 
              ,r.CalendarYearDay 
    FROM @RunningNumbers AS r
)
SELECT TOP 100 p.*
          ,(SELECT MIN(CalendarDate) FROM AddPeriode AS x WHERE x.PeriodNumber=p.PeriodNumber) AS [Start]
          ,(SELECT MAX(CalendarDate) FROM AddPeriode AS x WHERE x.PeriodNumber=p.PeriodNumber) AS [End]
          ,(SELECT MIN(CalendarDate) FROM AddPeriode AS x WHERE x.PeriodNumber=p.PeriodNumber AND x.CalendarWeek=p.CalendarWeek) AS [wkStart]
          ,(SELECT MAX(CalendarDate) FROM AddPeriode AS x WHERE x.PeriodNumber=p.PeriodNumber AND x.CalendarWeek=p.CalendarWeek) AS [wkEnd]
          ,(ROW_NUMBER() OVER(PARTITION BY PeriodNumber ORDER BY CalendarDate)-1)/7+1 AS WeekOfPeriode
FROM AddPeriode AS p
ORDER BY CalendarDate

试试看...

提示:不要使用VIEW或iTVF。

这是不变的数据，最好放在具有适当索引的物理存储表中。

Answer 2

不是非常确定外部链接在这里被接受，但我写了一篇文章，拉了一个5-4-4“作物年度”财政年度与所有的代码。请随意使用这些文章中的所有代码。

SQL Server Calendar Table

SQL Server Calendar Table: Fiscal Years