Início / dba / Perguntas / 308773
Accepted
ahmed elbarbary
Asked: 2022-03-16 16:30:27 +0800 CST

Como escrever uma declaração selecionada que obtém dados com base no partid da última data até a data atual com o preenchimento de lacunas?

  • 772

Eu trabalho no sql server 2012, enfrento um problema que não consigo select statmentobter o Partid do mês passado até o mês atual march (3).

com base em last dateexistir porpartid

e ao mesmo tempo, se houver algum gaps between dates, preencha-o com base na última data

então se

Encontrei partid com a última data 2022-01-08, então adicionarei 08-02-2022e 08-03-2022como partid6070

e se partidcom data no mês 7e mês seguinte 10e sem data part idno mês 8

e 9 então ele deve exibir isso gapsde acordo com o mês passado, pois partid 1234 tem lacuna.

ambos os casos devem ser aplicados para todos os dados baseados em partid.

Createddateusado abaixo no formato yyyy-mm-dd.

 create table Parts
 (
    
 PartId int,
 CreatedDate date
 )
 insert into Parts(PartId,CreatedDate)
 values
 (1234,'2021-07-03'),
 (1234,'2021-10-05'),
 (1234,'2021-11-05'),
 (5981,'2021-11-15'),
 (5981,'2021-12-03'),
 (6070,'2021-12-12'),
 (6070,'2022-01-08')

eu preciso fazer a select statmentexibição partscomo resultado esperado

green rowsapenas para ficar mais claro que essas peças devem ser adicionadas com peças antigas existentes antes.

Resultado esperado

lacuna de arquivo com datas da última data

o que eu tento

postagem atualizada

código abaixo me dá parte do meu resultado esperado

porque me dá intervalos nulos entre as datas restantes para obter datas até o mês atual

então como fazer isso por favor?

;with cte as (
   select partid, createddate,
          dateadd(month, -1,
                  coalesce(lead(createddate) over (partition by partid order by createddate),
                           max(createddate) over ()
                          )
                 ) as end_month
   from Parts
   union all
   select partid, dateadd(month, 1, createddate), end_month
   from cte
   where createddate <end_month
  )
 select *
 from cte
 order by partid, createddate
sql-server t-sql

1 respostas

  1. Best Answer

    Parece que o problema que você está tendo são dados ausentes. Quando não há resultado definido para uma determinada data, não há data para agrupar.

    Para contornar isso, você pode criar um CTE de intervalos que abrange seu intervalo de dados e, em seguida, unir-se a seus dados com uma junção LEFT OUTER. Isso fornece todos os intervalos em seu intervalo para a consulta

    Primeiro, vamos simular alguns dados para testar:

    DECLARE @mockTable TABLE (InvoiceID INT IDENTITY, invoiceDate DATE, invoiceAmount MONEY)
SET NOCOUNT ON
WHILE (SELECT COUNT(*) FROM @mockTable) < 300
BEGIN
 INSERT INTO @mockTable (invoiceDate, invoiceAmount) VALUES (DATEADD(DAY, -ROUND(((89 - 0 -1) * RAND() + 1), 0),CAST(GETDATE() AS DATE)), ((100 - 1 -1) * RAND() + 1) - 1)
END

    @mockTable agora contém 300 linhas de dados gerados aleatoriamente, com datas deste ano. Usando isso, podemos agregar o total de faturas por dia, se houver:

    ;WITH dateRange AS (
SELECT CAST(CAST(DATEPART(YEAR,CAST(GETDATE() AS DATE)) AS VARCHAR)+'-1-1' AS DATE) AS Interval
UNION ALL
SELECT DATEADD(DAY,1,Interval)
  FROM dateRange
 WHERE Interval <= CAST(GETDATE() AS DATE)
)

SELECT dr.interval, SUM(invoiceAmount) AS totalInvoiceAmount, COUNT(invoiceID) AS totalInvoices
  FROM dateRange dr
    LEFT OUTER JOIN @mockTable mt
      ON dr.Interval = mt.invoiceDate
 GROUP BY dr.Interval
 OPTION (MAXRECURSION 0)

    O CTE chamado dateRange contém todas as datas desde o início do ano, até amanhã. A junção dele com seus dados (ou os dados da mockTable) agora produz um resultado para todos os dias nesse intervalo, com uma contagem NULL totalInvoiceAmount e 0 totalInvoices quando não havia faturas para essa data.

    Captura de tela de demonstração

    Você pode aplicar uma técnica semelhante aos seus dados. hth

    • 1

relate perguntas