我想让这个查询成为一个视图，根据所需的时间框架生成一个递归日期cte。现在，为了本例的目的，我将递归的开始日期固定为2019年12月30日，结束日期固定为2020年1月4日。我知道我没有在视图中设置变量的选项，但是，我想知道是否有一种方法可以间接地做到这一点。有没有办法让起始日期和gen gap结束日期依赖于where子句？
这是查询

WITH Dates AS (
    SELECT [GenGapDate] = CONVERT(DATETIME,'12/30/2019') -- // this is the starting date
    UNION ALL 
    SELECT [GenGapDate] = DATEADD(DAY, 1, [GenGapDate])
    FROM Dates
    WHERE GenGapDate < '1/4/2020'
),
StoreXJoinDate as (
    select  d.[GenGapDate],
            s.Store,
            s.OpenDate,
            s.CloseDate
    from Dates  as d
    cross join #Stores s
),
POSQtySum as (
    select 
            p.SaleDate,
            p.Store,
         sum(p.Qty)  as TotQty
    from #POS p
    group by p.SaleDate, p.Store
),
StoreQty as (
    select  distinct 
            x.GenGapDate,
            x.Store,
            p.Store as PosStore,
            x.OpenDate,
            x.CloseDate,
            isnull(p.TotQty,0) as TotQty
    from StoreXJoinDate x
    full outer join POSQtySum p on  p.Store = x.Store
                                and p.SaleDate = x.GenGapDate
),
BaseResultSet as (
    select  GenGapDate,
            Store,
            OpenDate,
            CloseDate,
            TotQty,
            1 as StoreIsMissing
    from StoreQty
    where TotQty = 0
    and (
            CloseDate is null 
            or (CloseDate >= GenGapDate)
    )
    and OpenDate is not null
    and GenGapDate >= OpenDate
    union
    select  GenGapDate,
   Store,
   OpenDate,
   CloseDate,
   TotQty,
   NULL as StoreIsMissing
    from StoreQty
    where TotQty > 0
)
select  GenGapDate,
        Store,
        OpenDate,
        CloseDate,
        TotQty,
        StoreIsMissing
from BaseResultSet
order by GenGapDate desc, Store asc;

下面是生成用于查询的数据的构建脚本。

create table #Stores  -- drop table #Stores
(Store int,
 OpenDate date,
 CloseDate date
 );

 create table #ProdCat -- drop table #ProdCat
 (ProdCatId int,
  ProdCatName varchar(10)
  );

 create table #POS -- drop table #POS
 (SaleDate date,
  Store int,
  ProdCatId int,
  Qty int
  );

-- Store inserts
insert into #Stores
(Store,OpenDate,CloseDate)
values
(123,'2019-12-31',NULL);

insert into #Stores
(Store,OpenDate,CloseDate)
values
(124,'1995-01-01',NULL);

insert into #Stores
(Store,OpenDate,CloseDate)
values
(125,'2000-01-01','2020-01-03');

-- ProdCat inserts
insert into #ProdCat
(ProdCatId,ProdCatName)
values
(1,'Produce');

insert into #ProdCat
(ProdCatId,ProdCatName)
values
(2,'Diary');

-- POS inserts
insert into #POS
(SaleDate,ProdCatId,Store,Qty)
values
('2019-12-30',1,124,420);

insert into #POS
(SaleDate,ProdCatId,Store,Qty)
values
('2019-12-30',2,124,180);

-- --

insert into #POS
(SaleDate,ProdCatId,Store,Qty)
values
('2020-01-02',1,123,10);

insert into #POS
(SaleDate,ProdCatId,Store,Qty)
values
('2020-01-02',2,123,10);

insert into #POS
(SaleDate,ProdCatId,Store,Qty)
values
('2020-01-02',1,124,500);

insert into #POS
(SaleDate,ProdCatId,Store,Qty)
values
('2020-01-02',2,124,200);

insert into #POS
(SaleDate,ProdCatId,Store,Qty)
values
('2020-01-02',1,125,50);

insert into #POS
(SaleDate,ProdCatId,Store,Qty)
values
('2020-01-02',2,125,0);

-- --

insert into #POS
(SaleDate,ProdCatId,Store,Qty)
values
('2020-01-03',1,123,12);

insert into #POS
(SaleDate,ProdCatId,Store,Qty)
values
('2020-01-03',2,123,15);

insert into #POS
(SaleDate,ProdCatId,Store,Qty)
values
('2020-01-03',1,124,510);

insert into #POS
(SaleDate,ProdCatId,Store,Qty)
values
('2020-01-03',2,124,195);

-- --

insert into #POS
(SaleDate,ProdCatId,Store,Qty)
values
('2020-01-04',1,123,6);

insert into #POS
(SaleDate,ProdCatId,Store,Qty)
values
('2020-01-04',2,123,10);

insert into #POS
(SaleDate,ProdCatId,Store,Qty)
values
('2020-01-04',1,124,610);

insert into #POS
(SaleDate,ProdCatId,Store,Qty)
values
('2020-01-04',2,124,220);