我需要创建一个表,它是每个日期段的客户组的结果。他们没有任何关系。
所以我有36个月,10个客户。
作为输出,我想要的是一个表,每个客户有36个月的时间,这意味着我应该有360行和两列。
-- 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
获取客户名单。
-- this will produce 1 column of 10 rows.
select CustomerName from Customers where Region =3;
我需要的最终结果
|
MyDate
|
顾客
|
|
2022-04-30
|
顾客1
|
|
2022-05-31
|
顾客1
|
|
2022-06-30
|
顾客1
|
|
.....
|
.......
|
|
2022-04-30
|
顾客2
|
|
2022-05-31
|
顾客2
|
|
2022-06-30
|
顾客2
|
如何在SQL Server v11上执行此操作?