One column Cross Join with date range

Question

I have a thousand of records in one column and I want to cross join with date range let’s say between ‘2022-01-01 and ‘2022-02-15’. I don’t know how to start with sql server query, anyone can help?

Here’s my data (only one column)

City
Los Angeles
New York
Miami
Berlin
Dublin
Bologna

Desired result: (I shown 2 examples each only but the actual results should be 276 records)

Angeles/Dte-01Jan22
Los Angeles/Dte-02Jan22 
New York/Dte-01Jan22
New York/Dte-02Jan22
Miami/Dte-01Jan22
Miami/Dte-02Jan22 
Berlin/Dte-01Jan22
Berlin/Dte-02Jan22
Dublin/Dte-01Jan22
Dublin/Dte-02Jan22 
Bologna/Dte-01Jan22
Bologna/Dte-02Jan22

What's wrong with a `JOIN` with `BETWEEN` or `>=` and `<(=)` logic here? — Thom A, Oct 11 '22 at 15:44
Hi @Larnu thank you for your reply, sorry, but I don’t quite get your question? By the way, the date range is not part of the record, I was thinking to store in a variable only for beginning date and ending date. — Juan Tamad, Oct 11 '22 at 15:48
From your wording, I *assumed* that the dates are in another table, due to your use of the word "JOIN". If you don't have a table of these tables (like a calendar table) what are you wanting to `JOIN` to? — Thom A, Oct 11 '22 at 15:49
Does this help you with your question https://stackoverflow.com/questions/24063134/cartesian-product-between-items-list-and-date-range — Stewart, Oct 11 '22 at 15:50
@Larnu. No, I just want to combine the records with the dates, example 6 cities cross join with 46 days so the total would be 276 records. — Juan Tamad, Oct 11 '22 at 15:52
Yes, so why doesn't `JOIN dbo.YourCalendarTable YCT ON YCT.YourDateColumn >= {Some Start Date} AND YCT.YourDateColumn <= {Your End Date}` not work? — Thom A, Oct 11 '22 at 15:57

Arash Afshari · Answer 1 · 2022-10-11T16:08:19.880

0

This is how I understood the question, you should use this query:

    SELECT * FROM dbo.Cities AS C
JOIN dbo.Dates AS D
WHERE D.Date BETWEEN '2022-01-01' AND '2022-01-30'
ORDER BY C.Name,D.Date

You can use CROSS APPLY on another queries :) JOINs have better performance on your data.

edited Oct 11 '22 at 16:08

answered Oct 11 '22 at 16:06

Arash Afshari

26
3

Hi @Arash, there’s no actual date field in the table, the date would be passed through the variables only. – Juan Tamad Oct 11 '22 at 16:09

score 0 · Accepted Answer · answered Oct 11 '22 at 17:38

You can do:

with
p (ds, de) as (
  select convert(date, '20220101', 112), -- start date
         convert(date, '20220215', 112)  -- end date
),
r as (
  select ds as d from p
 union all
  select dateadd(day, 1, r.d) from r join p on r.d < p.de 
)
select concat(t.city, '/Dte', format(r.d, 'ddMMMyy')) 
from r
cross join t

See running example at db<>fiddle.

One column Cross Join with date range

2 Answers2