SQL JOIN table with a date range

Question

Say, I have a table with C columns and N rows. I would like to produce a select statement that represents the "join" of that table with a data range comprising, M days. The resultant result set should have C+1 columns (the last one being the date) and NXM rows.

Trivial example to clarify things: Given the table A below:

select * from A;
avalue  |
--------+
"a"     |

And a date range from 10 to 12 of October 2012, I want the following result set:

avalue  |  date
--------+-------
"a"     | 2012-10-10
"a"     | 2012-10-11
"a"     | 2012-10-12

(this is a stepping stone I need towards ultimately calculating inventory levels on any given day, given starting values and deltas)

Answer 1

The Postgres way for this is simple: CROSS JOIN to the function generate_series():

SELECT t.*, g.day::date
FROM   tbl t
CROSS  JOIN generate_series(timestamp '2012-10-10'
                          , timestamp '2012-10-12'
                          , interval  '1 day') AS g(day);

Produces exactly the output requested.

generate_series() is a set-returning function (a.k.a. "table function") producing a derived table. There are a couple of overloaded variants, here's why I chose timestamp input:

• Generating time series between two dates in PostgreSQL

For arbitrary dates, replace generate_series() with a VALUES expression. No need to persist a table:

SELECT *
FROM   tbl t
CROSS  JOIN (
   VALUES
     (date '2012-08-13')  -- explicit type in 1st row
   , ('2012-09-05')
   , ('2012-10-10')
   ) g(day);

Answer 2

If the date table has more dates in it than you're interested in, then do

select a.avalue, b.date from a, b where b.date between '2012-10-10' and '2012-10-12'

Other wise if the date table contained only the dates you were interested in, a cartesian join would accomplish this:

select * from a,b;