One column Cross Join with date range-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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.