SQL Server – Splitting a Year into Pieces in SQL Server 2008

datesql serversql-server-2008

I want to split a given year into given number of date range pieces using SQL. Following points need to be considered while splitting it:

  1. Given year will always start from 01-01 and end at 12-31 and it should be split into given number of pieces based on months. For example:

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

2019  3            2019-01-01
                   2019-05-01
                   2019-09-01
  2. If user asks to split given year into 12 pieces, it should split the whole year into 12 months.
  3. If user asks to split given year more then 12 pieces, it should split it into week wise

Please suggest.

Best Answer

Try this:. It uses DateFromParts(year, month, 1) to find the correct month for any value 1..12 or it uses DateAdd(week, date) to figure for any value 13..52. This way, we let the database engine worry about pesky things like leap year for us rather than doing that kind of math on our own.

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

This

Select * from udf_SplitYear(2019, 3)

Gives:

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

This 

Select * from udf_SplitYear(2019, 6)

Gives:

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

This

Select * from udf_SplitYear(2019, 26)

Gives: 

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
Related Question