Início / dba / Perguntas / 229736
Accepted
Murali Dhar Darshan
Asked: 2019-02-15 04:36:56 +0800 CST

Dividindo um ano em pedaços

  • 772

Eu quero dividir um determinado ano em determinado número de partes do intervalo de datas usando SQL. Os seguintes pontos precisam ser considerados ao dividi-lo:

  1. Um determinado ano sempre começará em 01-01 e terminará em 12-31 e deve ser dividido em um determinado número de peças com base em meses. Por exemplo:

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

2019  3            2019-01-01
                   2019-05-01
                   2019-09-01
  2. Se o usuário pedir para dividir determinado ano em 12 partes, ele deve dividir o ano inteiro em 12 meses.
  3. Se o usuário pedir para dividir um determinado ano mais de 12 peças, ele deve dividi-lo em semanas

Por favor sugira.

sql-server sql-server-2008

1 respostas

  1. Best Answer

    Tente isso:. Ele usa DateFromParts(year, month, 1) para encontrar o mês correto para qualquer valor 1..12 ou usa DateAdd(week, date) para calcular qualquer valor 13..52. Dessa forma, deixamos o mecanismo de banco de dados se preocupar com coisas irritantes como ano bissexto para nós, em vez de fazer esse tipo de matemática por conta própria.

    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://learn.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

    este

    Select * from udf_SplitYear(2019, 3)

    Dá:

    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

    este 

    Select * from udf_SplitYear(2019, 6)

    Dá:

    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

    este

    Select * from udf_SplitYear(2019, 26)

    Dá: 

    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
    • 1

relate perguntas