主页 / dba / 问题 / 308773
Accepted
ahmed elbarbary
Asked: 2022-03-16 16:30:27 +0800 CST

如何编写选择语句，根据partid 从最后一个日期到当前日期获取数据并填补空白？

  • 772

select statment我在 sql server 2012 上工作，我遇到了从上个月到本月无法获取 Partid 的问题march (3)。

基于last date存在每partid

并在同一时间，如果有的话，gaps between dates然后根据最后日期填写

因此，如果

我找到了最后日期为 2022-01-08 的 partid，然后我将添加08-02-2022并08-03-2022作为 partid6070

如果partid在一个月7和下个月有日期，并且在第 8个月10没有日期part id

和 9 然后它必须gaps根据上个月显示这个，因为 partid 1234 有 gap 。

这两种情况都必须适用于所有基于partid.

Createddate下面在甲酸盐上使用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')

我需要按预期结果select statment显示parts

green rows只是为了更清楚，这些零件必须在旧零件添加之前存在。

预期结果

与上次日期的日期的文件间隙

我尝试什么

更新后的帖子

下面的代码给了我预期结果的一部分

因为它给了我剩余日期之间的空白以获取当前月份的日期

那么该怎么做呢？

;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 个回答

  1. Best Answer

    看起来您遇到的问题是缺少数据。当给定日期没有结果集时，就没有分组依据的日期。

    要解决此问题，您可以创建一个跨越数据范围的间隔 CTE，然后使用 LEFT OUTER 连接将其连接到您的数据。这将您范围内的所有间隔提供给查询

    首先让我们模拟一些数据进行测试：

    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 现在包含 300 行随机生成的数据，日期为今年。使用它，我们可以汇总每天的总发票，如果有的话：

    ;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)

    名为 dateRange 的 CTE 包含从年初到明天的每个日期。将它连接到您的数据（或 mockTable 数据）现在会为该范围内的每一天生成一个结果，当该日期没有发票时，totalInvoiceAmount 为 NULL，totalInvoices 计数为 0。

    演示屏幕抓取

    您可以对数据应用类似的技术。hth

    • 1

相关问题