Generating time series based on existing table

Question

I have a table that has something like this:

| Zip | Population |
+-----------+-------------------+
| 00001 | 100 |
| 00002 | 200 |
| 00003 | 250 |

and so on. I would like to generate a new table from the above table which looks something like this:

| Date | Zip | Population |
+---------------+-----------+-------------------+
| 01-01-2016 | 00001 | 100 |
| 01-02-2016 | 00001 | 100 |
| 01-03-2016 | 00001 | 100 |
| ... | 00001 | 100 |
| 31-12-2016 | 00001 | 100 |
| 01-01-2016 | 00002 | 200 |
| 01-02-2016 | 00002 | 200 |
| 01-03-2016 | 00002 | 200 |
| ... | 00002 | 200 |
| 31-12-2016 | 00002 | 200 |

and so on for all the rows in the original table at the top.

I have found a way to generate dates between a specific range (e.g., as outlined here). I also think that I should use LEFT JOIN to achieve that?? I'm fairly new to SQL (or SQL Server, which is what I'm using at the moment). If so, what should I use as the join key? If someone could guide me through the process, I'd greatly appreciate the help. :) Thank you in advance for the answers!

• Andriy M: No, not a left join but a cross join.

Answer 1

Well - if you just want a quick way to do this, the following should give you what you're looking for

declare @OriginalTable table (zip varchar(5),population int)
declare @NewTable table (DateColumn date, zip varchar(5),population int)
insert into @OriginalTable values('00001',100)
insert into @OriginalTable values('00002',200)
insert into @OriginalTable values('00003',250)
declare @WorkDate Date = '2016-01-01'
declare @EndDate date = '2016-12-31'
WHILE @WorkDate <= @EndDate
BEGIN
 INSERT INTO @NewTable
 SELECT @WorkDate
 ,zip
 ,population
 FROM @OriginalTable
 SET @WorkDate = dateadd(day, 1, @WorkDate)
END
select * from @NewTable where zip='00001'

Answer 2

You may be interested in learning an efficient way of doing this using a set-based-methodology instead of a procedural (loop-based) methodology.

SQL Server typically performs set-based operations far more quickly than procedural operations.

The code below shows how to use a select statement to generate 366 rows containing dates from 2016年01月01日 to 2016年12月31日 (2016 is a leap-year). We then combine each of those rows with the "Zip" and "Population" columns of your source table, using a CROSS JOIN.

First, we setup a test-bed with your table, and a new destination table:

USE tempdb;
IF OBJECT_ID('dbo.Zips') IS NOT NULL
DROP TABLE dbo.Zips;
CREATE TABLE dbo.Zips
(
 Zip VARCHAR(5) NOT NULL
 , Pop INT NOT NULL
);
INSERT INTO dbo.Zips (Zip, Pop)
VALUES ('00001', 100)
 , ('00002', 200)
 , ('00003', 250);
IF OBJECT_ID('dbo.ZipsWithDates') IS NOT NULL
DROP TABLE dbo.ZipsWithDates;
CREATE TABLE dbo.ZipsWithDates
(
 Zip VARCHAR(5) NOT NULL
 , Pop INT NOT NULL
 , ZipDate DATE NOT NULL
);

This inserts rows, one for each day in the year, for each row in Zips:

;WITH dates AS (
SELECT TOP(366) /* 366 days in a leap-year, which 2016 is */
 TheDate = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY o1.object_id, o2.object_id), CONVERT(DATE, N'2015-12-31'))
FROM sys.objects o1
 CROSS JOIN sys.objects o2
)
INSERT INTO dbo.ZipsWithDates (Zip, Pop, ZipDate)
SELECT z.Zip
 , z.Pop
 , d.TheDate
FROM dates d
 CROSS JOIN dbo.Zips z

This shows the contents of the new table:

SELECT *
FROM dbo.ZipsWithDates;

And this is the output from the first few rows shows:

enter image description here

Note, you should always specify the schema, and please don't use reserved words for column names

• user1330974: Wow, thank you very, very much for your detailed and easy-to-understand explanation! I accepted an earlier (procedural) solution already, but I upvoted your solution as well so that others can learn two different ways of doing things. Thank you very much for writing this up! :)
• Hannah Vernon: my pleasure, @user1330974