Início / dba / Perguntas / 321136
Accepted
Rishav Ghosh
Asked: 2022-12-19 06:44:00 +0800 CST

Gerar datas usando cte recursivo

  • 772

tenho uma mesa de vendas

product_id  period_start  period_end  sale
----------------------------------------
1          2019-01-25    2019-02-28   100
2          2018-12-01    2020-01-01   10
3          2019-12-01    2020-01-31   1

Eu quero uma saída como:

product_id  period_start  period_end  sale
----------------------------------------
1          2019-01-25    2019-02-28   100
1          2019-01-26    2019-02-28   100
1          2019-01-27    2019-02-28   100
.               .            .         .
.               .            .         .
1          2019-02-28    2019-02-28   100
2          2018-12-01    2020-01-01   10
2          2018-12-02    2020-01-01   10
.               .            .         .
.               .            .         .
2          2020-01-01    2020-01-01   10
3          2019-12-01    2020-01-31   1
3          2019-12-02    2020-01-31   1
.               .            .         .
.               .            .         .
3          2020-01-31    2020-01-31   1

Eu tentei o abaixo, mas não parece funcionar

with alldates as(
  select * from sales
  union all
  select product_id, dateadd(day, 1, period_start) as period_start, period_end, average_daily_sales
  from  alldates
  where period_start < period_end
)

Qualquer maneira de conseguir isso usando cte recursivo?

DDL:

create table sales (
product_id int,
period_start date,
period_end date,
average_daily_sales int
);

insert into sales values(1,'2019-01-25','2019-02-28',100),(2,'2018-12-01','2020-01-01',10),(3,'2019-12-01','2020-01-31',1);
sql-server

1 respostas

  1. Best Answer

    Basta adicionar option(maxrecursion 0)ao seu finalselect

    with alldates as(
  select * from sales
  union all
  select product_id, dateadd(day, 1, period_start) as period_start, period_end, average_daily_sales
  from  alldates
  where period_start < period_end
)
SELECT *
FROM allDates
option(maxrecursion 0);

    dbfiddle

    Mas, sugiro ter uma tabela de calendário ou uma tabela de contagem e usar essa tabela de utilidade para escrever a consulta.

    Eu usei master..spt_values apenas como amostra, mas você deve usar uma tabela utilitária

    ;with tallyTable as 
(select number
  from master..spt_values
   where type ='p'
)
SELECT s.product_id,dateadd(day,tt.number, s.period_start) as period_start, s.period_end,s.average_daily_sales
FROM tallyTable as tt
     INNER JOIN sales as s
           on dateadd(day,tt.number, s.period_start)<= s.period_end
ORDER BY product_id,period_start

    veja mais neste link
    https://www.mssqltips.com/sqlservertip/5111/fix-sql-server-cte-maximum-recursion-exhausted-error/
    para tally table
    https://www.sqlservercentral.com/blogs/tally -tables-in-t-sql

    https://sqlbenjamin.wordpress.com/2018/02/06/sql-tip-the-tally-table/

    • 2

relate perguntas