2

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.

  • 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
  • 1
    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
  • 2
    Could you add your SQL server version please? Think that should be all that is needed then. – George.Palacios Feb 14 at 12:52
  • 2
    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
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.