使用 Oracle XE，如何創建 CASE 語句來返回特定日期

我正在嘗試基於名為 Business Payment Calendar 視圖的業務日曆表創建一個視圖，其中包含兩個新的計算列：

1. 付款警告日期 ( Payment_Warning_Date)
2. 付款到期日 ( Payment_Due_Date)

而且，還有未計算的Transaction_Date

這些日期基於從業務日曆的交易日期算起的業務天數，即 7 和 10 個工作日。Business Calendar 表包含為年份填充的列。

CAL_Date (which is our datestamp),
Day_Name,
Month_Name,
Cal_Year,
Month_Num,
Weekend (True/False),
Holiday (True/False),
Bus_Working_Day (True/False).

例如，假設您今天購買了商品，輸出將是：

Transaction_Date: 6/26/2017 
Payment Warning Date: 7/6/2017
Payment Due Date: 7/11/2017

圖我們週末快到了，下週一（7 月 3 日）是工作日，但周二（7 月 4 日）是美國假期，然後還有 3 個工作日。

我只是沒有運氣建構一個正確考慮 SYSDATE+7 和 +10 的 Bus_Working_Day 欄位然後返回正確日期的案例語句。

以下將生成一個顯示目前日期的列表，以及目前日期的警告和截止日期。

CREATE TABLE myCal (CAL_Date date,
Bus_Working_Day int)

INSERT INTO myCal
SELECT DATE '2017-06-27', 1 FROM DUAL UNION ALL
SELECT DATE '2017-06-28', 1 FROM DUAL UNION ALL
SELECT DATE '2017-06-29', 1 FROM DUAL UNION ALL
SELECT DATE '2017-06-30', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-01', 0 FROM DUAL UNION ALL
SELECT DATE '2017-07-02', 0 FROM DUAL UNION ALL
SELECT DATE '2017-07-03', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-04', 0 FROM DUAL UNION ALL
SELECT DATE '2017-07-05', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-06', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-07', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-08', 0 FROM DUAL UNION ALL
SELECT DATE '2017-07-09', 0 FROM DUAL UNION ALL
SELECT DATE '2017-07-10', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-11', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-12', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-13', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-14', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-15', 0 FROM DUAL UNION ALL
SELECT DATE '2017-07-16', 0 FROM DUAL UNION ALL
SELECT DATE '2017-07-17', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-18', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-19', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-20', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-21', 1 FROM DUAL
;

SELECT cal.CAL_Date
     ,warn.Warning_Date
     ,due.Due_Date
 FROM myCal cal
        LEFT  JOIN (SELECT mn.CAL_Date
                          ,ROW_NUMBER() OVER (PARTITION BY mn.CAL_Date ORDER BY alt.CAL_DATE) as rn
                          ,alt.CAL_Date as Warning_Date
                      FROM myCal mn
                             INNER JOIN myCal alt ON (alt.CAL_Date > mn.CAL_Date)
                     WHERE alt.Bus_Working_Day = 1
                   ) warn ON (cal.CAL_Date = warn.CAL_Date AND warn.rn = 7)
        LEFT  JOIN (SELECT mn.CAL_Date
                          ,ROW_NUMBER() OVER (PARTITION BY mn.CAL_Date ORDER BY alt.CAL_DATE) as rn
                          ,alt.CAL_Date as Due_Date
                      FROM myCal mn
                             INNER JOIN myCal alt ON (alt.CAL_Date > mn.CAL_Date)
                     WHERE alt.Bus_Working_Day = 1
                   ) due ON (cal.CAL_Date = due.CAL_Date AND due.rn = 10)
ORDER BY CAL_Date
;                      

dbfiddle在這裡

它需要的不僅僅是 CASE 語句，因為您需要評估接下來的每一天是否是工作日，以找到您需要的日子。

以下是它的工作原理： - 將表與自身連接（mn作為第一個實例和alt第二個實例）；- 忽略日期小於或等於日期的所有alt行（因為警告和到期日期當然總是在目前日期之後），並且日期不是工作日；- 對於每個日期，按升序對日期進行編號；- 取日曆表，並將其外連接到上述兩次（連接到日期）的結果中，如和；- 對於加入，取日曆日期與日期匹配的第 7 個日期，對於，第 10 個日期。alt``mn``alt``mn``alt``mn``warn``due``warn``alt``mn``due``alt

您可能需要考慮（如果可能）向現有日曆表添加warning_date和due_date- 一旦日期的基本資訊到位，您可以修改上面的程式碼以填充值。使您免於將日曆表加入自身五次以獲得所需的內容。（好的，如果你有一個特定的日期，那麼你可以直接點擊warnanddue子查詢，並且不必真的加入到日曆表中 - 我這樣做是為了展示目的。所以，只將它加入到自身4次） .

注意：在較新版本的 Oracle 中，在哪裡CROSS APPLY可用OUTER APPLY，您可以將它們更改為更直接（並且可能性能更好）的查詢 - 但是，不知道您有什麼版本可用（並且需要使用 dbfiddle我的測試，版本是 11g），我選擇了這個解決方案。

您應該能夠修改它以滿足您的需要。

更新：您詢問需要進行哪些更改才能在觸發器中使用查詢。

實際上，現在我考慮一下，我傾向於不直接使用觸發器。您會看到，即使添加了一行，也需要更新所有行（或者至少是日期在任何更改日期之前的 20 天內的所有行） - 這意味著我們將有一個（至少在技術上）遞歸的觸發器。

為了避免這種情況，最好做一些更複雜的事情（當然！）。

1. 創建一個新表，我們稱之為FLAG_JOB_TO_RUN，並給它一列，JOB_NAME。
2. 在您的觸發器中，INSERT值Update_Warn_Due_Datesinto FLAG_JOB_TO_RUN.JOB_NAME（如果它尚不存在）。
3. 安排一個腳本（或者更好的是，創建一個儲存過程並安排它）來執行以下操作：

• 檢查是否有“Update_Warn_Due_Dates”條目FLAG_JOB_TO_RUN

• 如果有：

  • 執行上面UPDATE的查詢版本SELECT以設置Warning_Date和Due_Date中的所有行myCal，並且
  • DELETE那一排從FLAG_JOB_TO_RUN盡可能頻繁地執行此作業。每晚執行一次就足夠了，並且在您知道發生了重大變化後立即手動執行它；或者，您可能需要每 5 分鐘執行一次。

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