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)

Your use of **C**, **N** and **M** is making me dizzy. Are you asking for a dynamic query? — Kermit, Oct 15 '12 at 15:57
but regardless of RDBMS, i suggest having a calendar table (which you could also use for business day management, for example, if your business model demands so) and the simply cartesian product the calendar table onto table A — amphibient, Oct 15 '12 at 16:01
Thanks, I was trying to avoid a calendar table. I am using PostgreSQL BTW. — Marcus Junius Brutus, Oct 15 '12 at 16:06
@foampile: You rarely need calendar tables with PostgreSQL, use [`generate_series`](http://www.postgresql.org/docs/current/static/functions-srf.html#FUNCTIONS-SRF-SERIES) instead. — mu is too short, Oct 15 '12 at 16:29
seems like legit functionality. i will check it out although i am not a PostgreSQL user — amphibient, Oct 15 '12 at 16:34

Erwin Brandstetter · Accepted Answer · 2019-04-28T21:25:43.370

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);

score 2 · Answer 2 · answered Oct 15 '12 at 16:00

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;

answered Oct 15 '12 at 16:00

cslauritsen

96
1
5

Actually I am trying to avoid having to define a date table, if at all possible. No particular reason, intellectual curiosity. – Marcus Junius Brutus Oct 15 '12 at 16:24

score 1 · Answer 3 · answered Oct 15 '12 at 16:07

declare
    @Date1 datetime = '20121010',
    @Date2 datetime = '20121012';

with Dates
as
(
    select @Date1 as [Date]
    union all
    select dateadd(dd, 1, D.[Date]) as [Date]
    from Dates as D
    where D.[Date] <= DATEADD(dd, -1, @Date2)
)
select 
    A.value, D.[Date]
from Dates as D
    cross join A

score 1 · Answer 4 · answered Oct 15 '12 at 16:09

For MySQL

schema/data:

CREATE TABLE someTable
(
    someCol varchar(8) not null
);

INSERT INTO someTable VALUES ('a');

CREATE TABLE calendar
(
    calDate datetime not null,
    isBus bit
);

ALTER TABLE calendar
ADD CONSTRAINT PK_calendar
PRIMARY KEY (calDate);

INSERT INTO calendar VALUES ('2012-10-10', 1);
INSERT INTO calendar VALUES ('2012-10-11', 1);
INSERT INTO calendar VALUES ('2012-10-12', 1);

query:

select s.someCol, c.calDate from someTable s, calendar c;

score 1 · Answer 5 · answered Oct 15 '12 at 16:16

You really have two options for what you are trying to do.

If your RDBMS supports it (I know SQL Server does, but I don't know any others), you can create a table-valued function which takes in a date range and returns a result set of all the discrete dates within that range. You would do a cartesian join between your table and the function.
You can create a static table of date values and then do a cartesian join between the two tables.

The second option will perform better, especially if you are dealing with large date ranges, however, that solution will not be able to handle arbitrary date ranges. But then, you should know your minimum date, and you can alway add more dates to your table as time goes on.

score 0 · Answer 6 · answered Oct 15 '12 at 16:07

0

I am not very clear about your M table. Providing that you have such a table(M) with dates, following cross join will bring the results.

SELECT C.*, M.date FROM C CROSS JOIN M

answered Oct 15 '12 at 16:07

Kaf

33,101
7
58
78

I am trying to avoid having to use a data table. I would like to join my table with a "data range" that lacks a table representation, if that's possible. – Marcus Junius Brutus Oct 15 '12 at 16:27

SQL JOIN table with a date range

6 Answers6