從 2 個表中計算日期里程碑之間的日期條目

我在表 1 上有一組隨機日期的里程碑，在表 2 上有一組隨機日期的項目（見下文）。

表格1

表2（該表有數百個條目，不同日期跨越全年）

我需要計算表 2 中日期介於每個里程碑之間的條目數（見下文）。

我目前的查詢是：

SELECT
   tbl2.Milestone
   ,COUNT(DISTINCT tbl1.ItemID) 'Items'
FROM
   tbl1
       FULL JOIN tbl2
       ON tbl1.CreateDate = tbl2.MilestoneDate
GROUP BY
   tbl2.Milestone,tbl2.MilestoneDate
ORDER BY
   tbl2.MilestoneDate

我嘗試了許多配置來解決這個問題，包括僅使用日期而不是里程碑名稱、刪除 ORDER BY 以及完全不同的編碼方法。解決此問題的最佳方法是什麼？注意：我不能使用任何一個表中的鍵，因為它們會導致跟踪完全不同的東西。我不是這個數據庫的 dba，所以我不能自己調整表。

目前，我必須通過指定每個日期範圍並執行查詢來手動提取每個值，然後是另一個日期範圍並執行（有幾十個里程碑不斷添加新的里程碑）。

我能夠完成這項工作。該方法相當於按日期對里程碑表進行排序，然後拉出一個表格，其中列出了每個里程碑和日期以及前一行中的里程碑和日期。這允許您在現在每行中的 2 個日期之間提取具有創建日期的 ItemID（這是作為 JOIN 的一部分完成的）。

這種方法還使得在查詢的不同階段插入各種過濾條件變得非常容易。例如，如果您想限制數據中填充的里程碑，您可以將 WHERE 語句添加到最內層或中間子查詢。然後，您可以使用最外層的查詢過濾 tbl1。

SELECT
   A.PreviousMilestone 'Milestone'
   ,A.PreviousMilestoneDate 'Milestone Date'
   ,COUNT(DISTINCT tbl1.ItemID) 'Total'
FROM    
   (
       SELECT
           currentmilestone 'Milestone'
           ,milestonedate 'MilestoneDate'
           ,lastmilestone 'PreviousMilestone'
           ,lastmilestonedate 'PreviousMilestoneDate'
       FROM
           (
               SELECT
                   tbl2.MilestoneDate AS milestonedate
                   ,tbl2.Milestone AS currentmilestone
                   ,LAG(tbl2.Milestone) OVER (ORDER BY tbl2.MilestoneDate) AS lastmilestone
                   ,LAG(tbl2.MilestoneDate) OVER (ORDER BY tbl2.MilestoneDate) AS lastmilestonedate
               FROM
                   tbl2
           ) as M
       WHERE
           --filter criteria here
   ) AS A
       JOIN tbl1
       ON tbl1.CreateDate >= A.PreviousMilestoneDate and tbl1.CreateDate < A.MilestoneDate
WHERE
   --filter criteria here
GROUP BY
   A.PreviousMilestone
   ,A.PreviousMilestoneDate

試試這個，讓我知道你得到了什麼：

SELECT
   tbl2.Milestone,
       (SELECT COUNT(DISTINCT tbl1.ItemID)
        FROM tbl1,
        WHERE tbl1.CreateDate >= tbl2.MilestoneDate AND
              tbl1.CreateDate < LEAD(tbl2.MilestoneDate) OVER (ORDER BY tbl2.MilestoneDate))
FROM
   tbl1
   tbl2
WHERE
   'tbl1.<column> = tbl2.<column> (see below)'
   tbl1.CreateDate >= MIN(tbl2.MilestoneDate) 'AND
   tbl1.CreateDate < <insert max date, if desired>'
GROUP BY
   tbl2.Milestone,tbl2.MilestoneDate
ORDER BY
   tbl2.MilestoneDate

此外，您的完全加入將排除 CreateDate 不等於 MilestoneDate 的任何 ItemID（除非我整天盯著程式碼完全失去理智！）。兩個表中是否有任何列匹配並且總是相同的？（例如：兩個表都有一個“customerID”欄位，應該匹配每一行 - 如果是這樣，請將其添加到您的 WHERE 子句中）您需要來自 tbl1 的任何數據，還是只需要項目計數？

如果上面的方法不起作用，我接下來會試試這個：

SELECT
   tbl2.Milestone,
   COUNT(DISTINCT tbl1.ItemID)
FROM
   tbl2
       JOIN tbl1
       ON tbl1.CreateDate >= tbl2.MilestoneDate AND
          tbl1.CreateDate < LEAD(tbl2.MilestoneDate) OVER (ORDER BY tbl2.MilestoneDate))
GROUP BY
   tbl2.MilestoneDate, tbl2.Milestone, COUNT(DISTINCT tbl1.ItemID)
ORDER BY
   tbl2.MilestoneDate

其他一些有用的參考資料：

獲取下一行（用於查找下一個里程碑日期）

SELECT 子句中帶有 COUNT 的子查詢

引用自：https://dba.stackexchange.com/questions/302478