I need to create a table that is the result of a customer group for each date period. They have no relationship whatsoever.

So I have 36 months, and 10 customers. What I want as the output is a single table that is 36 months for each customer, meaning I should end up with 360 rows, and two columns.

-- CREATE A TABLE of DATES - will produce 36 rows x 1 col
DECLARE @StartDate DATE = '20210701',
        @EndDate   DATE = '20240630';

WITH cte
     AS (SELECT @StartDate AS myDate
         UNION ALL
         SELECT Dateadd(month, 1, mydate) AS myDate
         FROM   cte
         WHERE  Dateadd(month, 1, mydate) <= @EndDate),
     dates
     AS (SELECT Eomonth(mydate) mydate
         FROM   cte),
     activities

     AS (SELECT 'whatever' as Whatever)


SELECT 
       mydate 
FROM   dates

Get the list of customers.

-- this will produce 1 column of 10 rows. 
select CustomerName from Customers where Region =3; 

THE END RESULT I NEED

How can I do this on SQL Server v11?

CodePudding user response：

A cross join matches all rows from both inputs resulting in the product of all rows, eg

select *
from dates 
cross join (
    select 'customer1' Customer union all
    select 'customer2' Customer union all   
    select 'customer3' -- etc
)Customers
order by customer;