Início / dba / Perguntas / 166024
Accepted
Rob V
Asked: 2017-03-03 13:30:04 +0800 CST

Como criar uma linha para todos os dias em um intervalo de datas usando um procedimento armazenado?

  • 772

Gostaria de criar um procedimento armazenado que criará uma linha em uma tabela para todos os dias em um determinado intervalo de datas. O Stored Procedure aceita duas entradas - Uma data de início e uma data de término do intervalo de datas desejado pelo usuário.

Então, digamos que eu tenha uma tabela assim:

SELECT Day, Currency
FROM ConversionTable

Day é um DateTime e Currency é apenas um inteiro.

Para manter as coisas simples, vamos apenas dizer que eu sempre quero que a coluna Moeda seja 1 para cada uma dessas linhas inseridas. Portanto, se alguém inserir '5 de março de 2017' como data de início e '11 de abril de 2017' como data de término, gostaria que as seguintes linhas fossem criadas:

2017-03-05 00:00:00, 1
2017-03-06 00:00:00, 1
...
2017-04-11 00:00:00, 1

Qual é a melhor maneira de codificar o procedimento armazenado para fazer isso? Estou usando o SQL Server 2008 R2 em meu ambiente de teste, mas nosso ambiente real usa o SQL Server 2012, então posso atualizar minha máquina de teste se houver uma nova funcionalidade introduzida em 2012 que facilite essa tarefa.

sql-server t-sql

6 respostas

  1. Best Answer

    Uma opção é um CTE recursivo:

    DECLARE @StartDate datetime = '2017-03-05'
       ,@EndDate   datetime = '2017-04-11'
;

WITH theDates AS
     (SELECT @StartDate as theDate
      UNION ALL
      SELECT DATEADD(day, 1, theDate)
        FROM theDates
       WHERE DATEADD(day, 1, theDate) <= @EndDate
     )
SELECT theDate, 1 as theValue
  FROM theDates
OPTION (MAXRECURSION 0)
;

    ( MAXRECURSIONdica adicionada graças ao comentário de Scott Hodgin, abaixo.)

    • 24

  2. Outra opção é usar uma função com valor de tabela. Essa abordagem é muito rápida e oferece um pouco mais de flexibilidade. Você fornece o intervalo de data/hora, parte de data e incremento. Também oferece a vantagem de incluí-lo em um CROSS APPLY

    Por exemplo

    Select * from [dbo].[udf-Range-Date]('2017-03-05','2017-04-11','DD',1)

    Devoluções

    RetSeq  RetVal
1   2017-03-05 00:00:00.000
2   2017-03-06 00:00:00.000
3   2017-03-07 00:00:00.000
4   2017-03-08 00:00:00.000
5   2017-03-09 00:00:00.000
...
36  2017-04-09 00:00:00.000
37  2017-04-10 00:00:00.000
38  2017-04-11 00:00:00.000

    A UDF se estiver interessado

    CREATE FUNCTION [dbo].[udf-Range-Date] (@R1 datetime,@R2 datetime,@Part varchar(10),@Incr int)
Returns Table
Return (
    with cte0(M)   As (Select 1+Case @Part When 'YY' then DateDiff(YY,@R1,@R2)/@Incr When 'QQ' then DateDiff(QQ,@R1,@R2)/@Incr When 'MM' then DateDiff(MM,@R1,@R2)/@Incr When 'WK' then DateDiff(WK,@R1,@R2)/@Incr When 'DD' then DateDiff(DD,@R1,@R2)/@Incr When 'HH' then DateDiff(HH,@R1,@R2)/@Incr When 'MI' then DateDiff(MI,@R1,@R2)/@Incr When 'SS' then DateDiff(SS,@R1,@R2)/@Incr End),
         cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
         cte2(N)   As (Select Top (Select M from cte0) Row_Number() over (Order By (Select NULL)) From cte1 a, cte1 b, cte1 c, cte1 d, cte1 e, cte1 f, cte1 g, cte1 h ),
         cte3(N,D) As (Select 0,@R1 Union All Select N,Case @Part When 'YY' then DateAdd(YY, N*@Incr, @R1) When 'QQ' then DateAdd(QQ, N*@Incr, @R1) When 'MM' then DateAdd(MM, N*@Incr, @R1) When 'WK' then DateAdd(WK, N*@Incr, @R1) When 'DD' then DateAdd(DD, N*@Incr, @R1) When 'HH' then DateAdd(HH, N*@Incr, @R1) When 'MI' then DateAdd(MI, N*@Incr, @R1) When 'SS' then DateAdd(SS, N*@Incr, @R1) End From cte2 )

    Select RetSeq = N+1
          ,RetVal = D 
     From  cte3,cte0 
     Where D<=@R2
)
/*
Max 100 million observations -- Date Parts YY QQ MM WK DD HH MI SS
Syntax:
Select * from [dbo].[udf-Range-Date]('2016-10-01','2020-10-01','YY',1) 
Select * from [dbo].[udf-Range-Date]('2016-01-01','2017-01-01','MM',1) 
*/
    • 8

  3. Usando o post de Aaron Bertrand sobre como criar uma tabela de dimensão de data como exemplo, cheguei a isso:

    DECLARE @StartDate DATE ='2017-03-05 00:00:00'
DECLARE @EndDate DATE ='2017-04-11 00:00:00'

Declare @DateTable table ([date]       DATE PRIMARY KEY);

-- use the catalog views to generate as many rows as we need

INSERT @DateTable ([date])
SELECT d
FROM (
    SELECT d = DATEADD(DAY, rn - 1, @StartDate)
    FROM (
        SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate)) rn = ROW_NUMBER() OVER (
                ORDER BY s1.[object_id]
                )
        FROM sys.all_objects AS s1
        CROSS JOIN sys.all_objects AS s2
        -- on my system this would support > 5 million days
        ORDER BY s1.[object_id]
        ) AS x
    ) AS y;

SELECT *
FROM @DateTable
ORDER BY [date]

    Você deve poder colocar esse tipo de lógica em seu procedimento armazenado e adicionar o que mais precisar.

    • 3

  4. Eu tive que resolver um problema semelhante recentemente no Redshift, onde eu só tinha acesso de leitura e, portanto, precisava de uma solução puramente baseada em SQL (sem procedimentos armazenados) para obter uma linha para cada hora em um intervalo de datas como ponto de partida para meu conjunto de resultados. Tenho certeza de que outros podem tornar isso mais elegante e modificá-lo para seus propósitos, mas para aqueles que precisam, aqui está minha solução hackeada:

    with hours as
   (select 0 clockhour union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10 union select 11 union select 12 
    union select 13 union select 14 union select 15 union select 16 union select 17 union select 18 union select 19 union select 20 union select 21 union select 22 union select 23)
, days as
   (select *
    from 
       (select to_number(n0.number || n1.number, '99') daynum
        from
           (select 0 as number union select 1 union select 2 union select 3) as n0
           cross join
           (select 1 as number union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0) as n1)
    where daynum between 1 and 31)
, months as
   (select 1 as monthnum, 'jan' as themonth, 31 as numdays union select 2, 'feb', 28 union select 3, 'mar', 31 union select 4, 'apr', 30 union select 5, 'may', 31 union select 6, 'jun', 30 
    union select 7, 'jul', 31 union select 8, 'aug', 31 union select 9, 'sep', 30 union select 10, 'oct', 31 union select 11, 'nov', 30 union select 12, 'dec', 31)
, years as
   (select century || decade || yr as yr
    from 
       (select 19 century union select 20) 
    cross join
       (select 0 decade union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) 
    cross join
       (select 0 yr union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9))
select cast(daynum || '-' || themonth || '-' || yr || ' ' || clockhour || ':00:00' as timestamp) dayhour
from hours
cross join days
cross join months
cross join years
where cast(daynum || '-' || themonth || '-' || yr || ' ' || clockhour || ':00:00' as timestamp) 
between date_trunc('month', dateadd('month', -$MONTHS_AGO, getdate()))
and     date_trunc('month', dateadd('month', $MONTHS_AHEAD, getdate()))
and   daynum <= numdays
order by yr, monthnum, daynum, clockhour;
    • 2

  5. Outra opção é inserir os registros de data na tabela usando um loop.

    O código abaixo os adiciona a uma tabela temporária, mas isso pode ser facilmente adaptado para ser executado em um proc armazenado com uma tabela permanente.

    DECLARE @d DATE
DECLARE @StartDate DATE = '2020-01-01'
DECLARE @EndDate DATE = '2022-12-31'

DROP TABLE IF EXISTS #d
CREATE TABLE #d (Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, Date DATE)

SET @d = @StartDate
WHILE @d <= @EndDate BEGIN

    INSERT INTO #d (Date) VALUES (@d)

    SET @d = DATEADD(DAY, 1, @d)

END
    • 0
    1. Preencha cerca de 10 registros nas tabelas Item e 3 nas tabelas Categoria e, em seguida, preencha a tabela Ledger com dados históricos de 01.01.10 a 31.12.20 como 10 registros por dia usando um procedimento armazenado.
    • -2

relate perguntas