Generate SQL Temp Table of Sequential Dates to Left Outer Join To

generate sql temp table of sequential dates to left outer join to

In SQL Server 2005 and up, you can use something like this (a Common Table Expression CTE) to do this:

DECLARE @DateFrom DATETIME
SET @DateFrom = '2011-01-01'

DECLARE @DateTo DATETIME
SET @DateTo = '2011-01-10'

;WITH DateRanges AS
(
    SELECT @DateFrom AS 'DateValue'
    UNION ALL
    SELECT DATEADD(DAY, 1, DateValue)
    FROM DateRanges
    WHERE DateValue < @DateTo
)
SELECT * FROM DateRanges

You could LEFT OUTER JOIN this CTE against your table and return the result.

How to create a temp table in a Postgres query that contains a range of dates?

generate_series on PostgreSQL

get min date from your range then max and generate series

How to CREATE a Temp table in SQL Server with a SEQUENCE PK Column

Seems like to get what I'm after I simply update the STAGING table with my sequence rather than attempting to CREATE my TEMP TABLE using the SEQUENCE.

DROP TABLE IF exists #t
DROP TABLE IF exists #t_stg 

DROP SEQUENCE IF exists dbo.t_seq

GO

DECLARE @sql NVARCHAR(max);
DECLARE @Count INT = 981518;

CREATE SEQUENCE dbo.t_seq START WITH 1 increment BY 1

SET @sql = N'ALTER SEQUENCE dbo.t_seq RESTART WITH ' + CAST(@Count AS NVARCHAR(20)) + ';';
EXEC SP_EXECUTESQL @sql;
GO

CREATE TABLE #t(id INT, a INT, b INT)

CREATE TABLE #t_stg(id INT, a INT, b INT)

INSERT INTO #t_stg(a,b) VALUES (1,2),(3,3),(4,5)

--SELECT * FROM #t_stg

UPDATE #t_stg SET id = NEXT VALUE FOR t_seq

SELECT * FROM #t_stg

--INSERT INTO #t(id,a,b) 
--SELECT * FROM #t_stg

--SELECT * FROM #t

GO

Is it possible to effect a join against a table of consecutive dates without having to maintain an actual table of consecutive dates?

It would probably be easiest to build out the dates table

For example, if you wanted all dates from Jan 1, 2009 to today you could do the following:

DECLARE @start  AS DATETIME,
        @end    AS DATETIME

SELECT  @start = '2009-01-01',
        @end   = getdate()

DECLARE @dates TABLE    (
                        dt  DATETIME
                        )

WHILE (@start < @end)
BEGIN
    INSERT  @dates
    SELECT  @start

    SELECT @start = DATEADD(day, 1, @start)
END

SELECT  *
FROM    @dates

@dates will have a record for each day from @start to @end.

Fill Missing Dates In a Date-Sequenced in SQL using Tally Table

You could do this using a Tally Table.

Basically, you use the Tally Table to generate sequence of dates from @startDate to @endDate and CROSS JOIN it to DISTINCT Item to generate all Date-Item combination. Then, the result will be LEFT-JOINed to tblSales to achieve the desired output.

SQL Fiddle

DECLARE
    @startDate  DATE = '20140101',
    @endDate    DATE = '20140105';

WITH E1(N) AS(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
,E2(N) AS(SELECT 1 FROM E1 a, E1 b)
,E4(N) AS(SELECT 1 FROM E2 a, E2 b)
,Tally(N) AS(
    SELECT TOP (DATEDIFF(DAY, @startDate, @endDate) + 1) 
        ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
    FROM E4
)
,CteAllDates(Item, dt) AS(
    SELECT x.Item, DATEADD(DAY, N - 1, @startDate)
    FROM Tally
    CROSS JOIN(
        SELECT DISTINCT Item 
        FROM tblSales
        WHERE [Date] BETWEEN @startDate AND @endDate
    ) AS x
)
SELECT d.*, ts.Sales
FROM CteAllDates d
LEFT JOIN tblSales ts
    ON ts.Item = d.Item
    AND ts.Date = d.dt
WHERE
    ts.[Date] BETWEEN @startDate AND @endDate
ORDER BY d.Item, d.dt

---

Here is an alternative. Instead of the cascading CTEs, use sys.columns to generate the Tally Table.:

DECLARE
    @startDate  DATE = '20140101',
    @endDate    DATE = '20140105';

WITH Tally(N) AS(
    SELECT TOP (DATEDIFF(DAY, @startDate, @endDate) + 1) 
        ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
    FROM sys.columns a, sys.columns b
)
,CteAllDates(Item, dt) AS(
    SELECT x.Item, DATEADD(DAY, N - 1, @startDate)
    FROM Tally
    CROSS JOIN(
        SELECT DISTINCT Item 
        FROM tblSales
        WHERE [Date] BETWEEN @startDate AND @endDate
    ) AS x
)
SELECT d.*, ts.Sales
FROM CteAllDates d
LEFT JOIN tblSales ts
    ON ts.Item = d.Item
    AND ts.Date = d.dt
WHERE
    ts.[Date] BETWEEN @startDate AND @endDate
ORDER BY d.Item, d.dt

---

Result

|    Item |         dt |  Sales |
|---------|------------|--------|
| tenant1 | 2014-01-01 |    100 |
| tenant1 | 2014-01-02 |    100 |
| tenant1 | 2014-01-03 |    100 |
| tenant1 | 2014-01-04 |   NULL |
| tenant1 | 2014-01-05 |    100 |
| tenant2 | 2014-01-01 |    100 |
| tenant2 | 2014-01-02 |   NULL |
| tenant2 | 2014-01-03 |   NULL |
| tenant2 | 2014-01-04 |    100 |
| tenant2 | 2014-01-05 |   NULL |
| tenant3 | 2014-01-01 |    100 |
| tenant3 | 2014-01-02 |   NULL |
| tenant3 | 2014-01-03 |    100 |
| tenant3 | 2014-01-04 |   NULL |
| tenant3 | 2014-01-05 |    100 |

How to generate temp table with integers 1-52 for weeks of year to join on?

Without creating temporary table, you can simplify this query using CTE, like below.
- Use recursive CTE to generate week numbers
- Get distinct years from TicketTable
- Cross join distinct years and weeks to get all combinations
- Then left join it with TicketTable to get count for each year-week

;With WEEK_CTE as (
Select 1 as WeekNo
UNION ALL
SELECT 1 + WeekNo from WEEK_CTE 
WHERE WeekNo < 52
)
Select yr.Year AS 'TicketYear' 
, wk.WeekNo AS 'TicketWeek'
, COUNT(t.TicketStatus) AS 'WeekTotal'
from Week_CTE wk
cross join (select distinct year(TicketQueuedDateTime) as [Year] from TicketTable) yr
left join TicketTable t on wk.WeekNo = DATEPART(WEEK, t.TicketQueuedDateTime) and yr.Year = YEAR(t.TicketQueuedDateTime)
group by yr.Year, wk.WeekNo

How can I generate a temporary table filled with dates in SQL Server 2000?

This will quickly populate a table with 170 years worth of dates.

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

You can then use it by LEFT joining on to that table, for the range of dates you require.

---

Related Topics