2

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!

1
  • 1
    No, not a left join but a cross join.
    – Andriy M
    Commented Aug 22, 2016 at 20:45

2 Answers 2

2

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'
0
5

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

2
  • 1
    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! :) Commented Aug 26, 2016 at 19:27
  • 1
    my pleasure, @user1330974
    – Hannah Vernon
    Commented Aug 26, 2016 at 20:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.