把一年分成幾塊

我想使用 SQL 將給定的年份拆分為給定數量的日期範圍。拆分時需要考慮以下幾點：

1. 給定的年份總是從 01-01 開始，到 12-31 結束，它應該根據月份分成給定數量的片段。例如：

year  SplitCount   output  
2019  2            2019-01-01
                  2019-07-01

2019  3            2019-01-01
                  2019-05-01
                  2019-09-01

2. 如果使用者要求將給定的年份分成 12 部分，則應將全年分成 12 個月。
3. 如果使用者要求將給定年份拆分超過 12 件，則應按週拆分

請建議。

試試這個：。它使用 DateFromParts(year, month, 1) 為任何值 1..12 找到正確的月份，或者它使用 DateAdd(week, date) 來計算任何值 13..52。這樣，我們讓數據庫引擎為我們擔心諸如閏年之類的煩人的事情，而不是我們自己做那種數學。

Create or alter function udf_SplitYear(
   @TargetYear numeric(4,0),
   @SplitCount tinyint )
returns @ReturnVals table (
   [Year] numeric(4,0),
   SplitCount numeric(3,0),
   SplitNum numeric(3,0),
   StartDate datetime,
   WeekNum numeric(2,0)
)
as
-- Split this into evenly divisible blocks based on months or weeks.
begin
   -- Valid dates for SQL Server are 1/1/1753 to 12/31/9999  https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-2017
   If @TargetYear < 1753
       Return;
   if @splitcount < 2      --cannot split something into an unsplit thing...
       Return;
   if @SplitCount > 52     --not going to divide this year into days or worse, hours...
       Return;

   declare @rowNumber int = 1;
   declare @PreviousDate datetime = DateFromPartS(@TargetYear, 1, 1);
   declare @ThisDate datetime = DATEFROMPARTS(@TargetYear, 1, 1);
   declare @dateGap int = 0;
   declare @Divisor int;
   declare @ThisSplit int = 1;

   -- 01 Jan <year>
   insert @ReturnVals ([Year], SplitCount, SplitNum, StartDate, WeekNum) values (@TargetYear, @SplitCount, 1, @ThisDate, DatePart(week, @ThisDate));

   if @splitCount < 13   
     Begin
       --split across month boundaries
       set @Divisor = Round(12 / @SplitCount, 0)
       set @ThisSplit =  @Divisor + 1;
       While @rowNumber < @SplitCount 
       Begin
           set @ThisDate = DATEFROMPARTS(@TargetYear, @ThisSplit, 1);
           set @dateGap = DateDiff(day, @PreviousDate, @thisDate);
           insert @ReturnVals ([Year], SplitCount, SplitNum, StartDate, WeekNum) values (@TargetYear, @SplitCount, @ThisSplit, @ThisDate, DatePart(week, @ThisDate));
           set @PreviousDate = @thisDate;
           set @ThisSplit = @ThisSplit + @Divisor;
           set @rowNumber = @rowNumber + 1;
       End;
     end
   else  
     begin
       --split across weeks.
       set @Divisor  = Round(52 / @SplitCount, 0);
       set @ThisSplit = @Divisor ;
       While @rowNumber < @SplitCount 
       Begin
           set @ThisDate = DateAdd(wk, @ThisSplit, DateFromParts(@TargetYear, 1, 1));
           set @dateGap = DateDiff(day, @PreviousDate, @thisDate);
           insert @ReturnVals ([Year], SplitCount, SplitNum, StartDate, WeekNum) values (@TargetYear, @SplitCount, @ThisSplit, @ThisDate, DatePart(week, @ThisDate));
           set @PreviousDate = @thisDate;
           set @ThisSplit = @ThisSplit + @Divisor;
           set @rowNumber = @rowNumber + 1;
       End;
     end;

   Return
end

這

Select * from udf_SplitYear(2019, 3)

給出：

Year   |   SplitCount   |   SplitNum   |   StartDate   |   Days
2019   |   3   |   1   |   2019-01-01 00:00:00.000   |   0
2019   |   3   |   5   |   2019-05-01 00:00:00.000   |   120
2019   |   3   |   9   |   2019-09-01 00:00:00.000   |   123

這

Select * from udf_SplitYear(2019, 6)

給出：

Year   |   SplitCount   |   SplitNum   |   StartDate   |   Days
2019   |   6   |   1   |   2019-01-01 00:00:00.000   |   0
2019   |   6   |   3   |   2019-03-01 00:00:00.000   |   59
2019   |   6   |   5   |   2019-05-01 00:00:00.000   |   61
2019   |   6   |   7   |   2019-07-01 00:00:00.000   |   61
2019   |   6   |   9   |   2019-09-01 00:00:00.000   |   62
2019   |   6   |   11   |   2019-11-01 00:00:00.000   |   61

這

Select * from udf_SplitYear(2019, 26)

給出：

Year   |   SplitCount   |   SplitNum   |   StartDate   |   WeekNum
2019   |   26   |   1   |   2019-01-01 00:00:00.000   |   1
2019   |   26   |   2   |   2019-01-15 00:00:00.000   |   3
2019   |   26   |   4   |   2019-01-29 00:00:00.000   |   5
2019   |   26   |   6   |   2019-02-12 00:00:00.000   |   7
2019   |   26   |   8   |   2019-02-26 00:00:00.000   |   9
2019   |   26   |   10   |   2019-03-12 00:00:00.000   |   11
2019   |   26   |   12   |   2019-03-26 00:00:00.000   |   13
2019   |   26   |   14   |   2019-04-09 00:00:00.000   |   15
2019   |   26   |   16   |   2019-04-23 00:00:00.000   |   17
2019   |   26   |   18   |   2019-05-07 00:00:00.000   |   19
2019   |   26   |   20   |   2019-05-21 00:00:00.000   |   21
2019   |   26   |   22   |   2019-06-04 00:00:00.000   |   23
2019   |   26   |   24   |   2019-06-18 00:00:00.000   |   25
2019   |   26   |   26   |   2019-07-02 00:00:00.000   |   27
2019   |   26   |   28   |   2019-07-16 00:00:00.000   |   29
2019   |   26   |   30   |   2019-07-30 00:00:00.000   |   31
2019   |   26   |   32   |   2019-08-13 00:00:00.000   |   33
2019   |   26   |   34   |   2019-08-27 00:00:00.000   |   35
2019   |   26   |   36   |   2019-09-10 00:00:00.000   |   37
2019   |   26   |   38   |   2019-09-24 00:00:00.000   |   39
2019   |   26   |   40   |   2019-10-08 00:00:00.000   |   41
2019   |   26   |   42   |   2019-10-22 00:00:00.000   |   43
2019   |   26   |   44   |   2019-11-05 00:00:00.000   |   45
2019   |   26   |   46   |   2019-11-19 00:00:00.000   |   47
2019   |   26   |   48   |   2019-12-03 00:00:00.000   |   49
2019   |   26   |   50   |   2019-12-17 00:00:00.000   |   51

引用自：https://dba.stackexchange.com/questions/229736