una - tablas temporales vs variables tipo tabla sql server

¿Cómo puedo generar una tabla temporal llena de fechas en SQL Server 2000?

Question

Necesito hacer una tabla temporal que contenga rango de fechas, así como un par de columnas que contengan valores de marcador (0) para uso futuro. Las fechas que necesito son el primer día de cada mes entre $ startDate y $ endDate, donde estas variables pueden tener varios años de diferencia.

Mi declaración sql original se veía así:

select dbo.FirstOfMonth(InsertDate) as Month, 0 as Trials, 0 as Sales
into #dates
from customer
group by dbo.FirstOfMonth(InsertDate)

"FirstOfMonth" es una función definida por el usuario que hice, que hace lo que dice, devolviendo el primer día del mes para la fecha prevista con la hora exactamente a la medianoche.

Esto produjo casi exactamente lo que necesitaba hasta que descubrí que de vez en cuando había lagunas en mis fechas en las que tenía unos meses si no había registros insertos. Como mi resultado aún debe tener los meses faltantes, necesito un enfoque diferente.

He agregado las siguientes declaraciones al procedimiento almacenado anticipando su necesidad del rango de las fechas que necesito ...

declare $startDate set $startDate = select min(InsertDate) from customer
declare $endDate set $endDate = select max(InsertDate) from customer

... pero no tengo idea de qué hacer desde aquí.

Sé que esta pregunta es similar a esta pregunta pero, francamente, esa respuesta está por encima de mi cabeza (no suelo trabajar con SQL y cuando lo hago tiende a ser en versiones anteriores de SQL Server) y hay algunos pocos diferencias que me están tirando.

Answer 1

Esto rellenará rápidamente una tabla con 170 años de fechas.

CREATE TABLE CalendarMonths (
  date DATETIME,
  PRIMARY KEY (date)
)

DECLARE
  @basedate DATETIME,
  @offset   INT
SELECT
  @basedate = ''01 Jan 2000'',
  @offset = 1

WHILE (@offset < 2048)
BEGIN
  INSERT INTO CalendarMonths SELECT DATEADD(MONTH, @offset, date) FROM CalendarMonths
  SELECT @offset = @offset + @offset
END

A continuación, puede utilizarlo IZQUIERDA uniéndose a esa tabla, para el rango de fechas que necesita.

Answer 2

Esto, por supuesto, no funcionará en SQL-Server 2000, sino en una base de datos moderna en la que no desea crear una tabla permanente. Puede usar una variable de tabla en lugar de crear una tabla para que pueda unir los datos intente esto. Cambia el DÍA a la HORA, etc. para cambiar el tipo de incremento.

declare @CalendarMonths table (date DATETIME,  PRIMARY KEY (date)
)

DECLARE
  @basedate DATETIME,
  @offset   INT
SELECT
  @basedate = ''01 Jan 2014'',
  @offset = 1
  INSERT INTO @CalendarMonths SELECT @basedate

WHILE ( DATEADD(DAY, @offset, @basedate) < CURRENT_TIMESTAMP)
BEGIN
  INSERT INTO @CalendarMonths SELECT DATEADD(HOUR, @offset, date) FROM @CalendarMonths where DATEADD(DAY, @offset, date) < CURRENT_TIMESTAMP
  SELECT @offset = @offset + @offset
END

Answer 3

Necesitaba algo similar, pero todos los DÍAS en lugar de todos los MESES.

Usando el código de MatBailie como punto de partida, aquí está el SQL para crear una tabla permanente con todas las fechas del 2000-01-01 al 2099-12-31:

CREATE TABLE _Dates (
  d DATE,
  PRIMARY KEY (d)
)
DECLARE @dIncr DATE = ''2000-01-01''
DECLARE @dEnd DATE = ''2100-01-01''

WHILE ( @dIncr < @dEnd )
BEGIN
  INSERT INTO _Dates (d) VALUES( @dIncr )
  SELECT @dIncr = DATEADD(DAY, 1, @dIncr )
END

Answer 4

Para SQL Server 2000, esta publicación de parece prometedora para una forma de generar temporalmente fechas calculadas a partir de una fecha de inicio y finalización. No es exactamente lo mismo, pero bastante similar. Esta publicación tiene una respuesta muy detallada sobre truncar fechas, si es necesario.

En caso de que alguien se tropiece con esta pregunta y esté trabajando en PostgreSQL en lugar de SQL Server 2000, aquí es cómo puede hacerlo allí ...

PostgreSQL tiene una función de generación de series ingeniosas. Para su ejemplo, puede usar esta serie de todos los días en lugar de generar una tabla de calendario completa, y luego hacer agrupaciones y enfrentamientos desde allí.

SELECT current_date + s.a AS dates FROM generate_series(0,14,7) AS s(a);
   dates
------------
 2004-02-05
 2004-02-12
 2004-02-19
(3 rows)

SELECT * FROM generate_series(''2008-03-01 00:00''::timestamp,
                              ''2008-03-04 12:00'', ''10 hours'');
   generate_series   
---------------------
 2008-03-01 00:00:00
 2008-03-01 10:00:00
 2008-03-01 20:00:00
 2008-03-02 06:00:00
 2008-03-02 16:00:00
 2008-03-03 02:00:00
 2008-03-03 12:00:00
 2008-03-03 22:00:00
 2008-03-04 08:00:00
(9 rows)

También buscaría en date_trunc de PostgreSQL usando ''month'' para el campo truncator para quizás refactorizar su consulta original para que coincida fácilmente con una versión date_trunc de la serie de calendario.

Answer 5

Probablemente usaría una tabla de calendario. Cree una tabla permanente en su base de datos y llénela con todas las fechas. Incluso si cubrieras un rango de 100 años, la tabla solo tendría ~ 36,525 filas.

CREATE TABLE dbo.Calendar (
    calendar_date    DATETIME    NOT NULL,
    is_weekend       BIT         NOT NULL,
    is_holiday       BIT         NOT NULL,
    CONSTRAINT PK_Calendar PRIMARY KEY CLUSTERED (calendar_date)
)

Una vez que se crea la tabla, solo rellene una vez en un bucle, de modo que siempre esté disponible y disponible para usted.

Su consulta podría ser algo como esto:

SELECT
    C.calendar_date,
    0 AS trials,
    0 AS sales
FROM
    dbo.Calendar C
WHERE
    C.calendar_date BETWEEN @start_date AND @end_date AND
    DAY(C.calendar_date) = 1

Puede unirse a la tabla Customers como lo necesite, uniéndose FirstOfMonth(InsertDate) = C.calendar_date a FirstOfMonth(InsertDate) = C.calendar_date si eso es lo que desea.

También puede incluir una columna para day_of_month si lo desea, lo que evitaría la sobrecarga de llamar a la función DAY() , pero eso es bastante trivial, por lo que probablemente no importe de una forma u otra.

Answer 6

Probado a continuación y funciona, aunque es un poco intrincado.

Asigné valores arbitrarios a las fechas de la prueba.

DECLARE @SD smalldatetime,
        @ED smalldatetime,
        @FD smalldatetime,
        @LD smalldatetime,
        @Mct int,
        @currct int = 0

SET @SD = ''1/15/2011''
SET @ED = ''2/02/2012''


SET @FD = (DATEADD(dd, -1*(Datepart(dd, @SD)-1), @sd))
SET @LD = (DATEADD(dd, -1*(Datepart(dd, @ED)-1), @ED))

SET @Mct = DATEDIFF(mm, @FD, @LD)

CREATE TABLE #MyTempTable (FoM smalldatetime, Trials int, Sales money)

WHILE @currct <= @Mct
BEGIN
    INSERT INTO #MyTempTable (FoM, Trials, Sales)
    VALUES
    (DATEADD(MM, @currct, @FD), 0, 0)
    SET @currct = @currct + 1
END


SELECT * FROM #MyTempTable

DROP TABLE #MyTempTable

Answer 7

Un punto de partida de un kludge útil para especificar un rango o una lista específica de fechas:

SELECT *
FROM 
    (SELECT CONVERT(DateTime,''2017-1-1'')+number AS [Date]
     FROM master..spt_values WHERE type=''P'' AND number<370) AS DatesList
WHERE DatesList.Date IN (''2017-1-1'',''2017-4-14'',''2017-4-17'',''2017-12-25'',''2017-12-26'')

Puedes obtener 0 a 2047 fuera de master..spt_values WHERE type=''P'' , ¡así que eso es cinco años y medio de fechas si lo necesitas!

Answer 8

declare @start datetime
set @start = ''2016-09-01''
declare @end datetime
set @end = ''2016-09-30''

create table #Date
(
    table_id int identity(1,1) NOT NULL,
    counterDate datetime NULL
);

insert into #Date select top (datediff(D,@start,@end)) NULL from SOME_TABLE
update #Date set counterDate = dateadd(D,table_id - 1, @start)

El código anterior debe llenar la tabla con todas las fechas entre el inicio y el final. Entonces simplemente te unirías a esta mesa para obtener todas las fechas necesarias. Si solo necesita un cierto día de cada mes, puede agregar un mes en su lugar.

Answer 9

select top (datediff(D,@start,@end)) dateadd(D,id-1,@start)
from BIG_TABLE_WITH_NO_JUMPS_IN_ID