# Splitting a year into pieces

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

• When you say "a given year" do you relate this to any existing data? Or are you working literally from a functional perspective of "take 2019 and split into X contiguous time periods"? Also could you add your version of SQL Server as a tag? What do you want to do if the user picks a value that isn't completely divisible by the number of weeks in the year - should it round days up or down? Finally, do you need to take account of leap years? – George.Palacios Feb 14 at 12:44
• I am working from a functional perspective in which user will provide a year and number of pieces it has to be split. – Murali Dhar Darshan Feb 14 at 12:47
• So in this case 12 is a special case where it should return the months for that year? But 1,2,3 should return only the first,second,third weeks respectively? – George.Palacios Feb 14 at 12:48
• Could you add your SQL server version please? Think that should be all that is needed then. – George.Palacios Feb 14 at 12:52
• What if the user picks 5? What should be returned for numbers that don't divide by 12 but are under 12? – George.Palacios Feb 14 at 13:14

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
``````