2018年2月28日 星期三

查詢期間內休假人員(區間查詢)-應用狄摩根定理(DeMorgan’s Theorem)

欲查詢2013-09-162013-09-18此段時間之休假(公出)人員,符合條件者如下表中A01A03A043人,由於符合查詢條件組合繁多,難以撰寫符合各種狀況的查詢條件,尤其以A01已超越查詢區間,此類問題應嘗試以反向思考模式,以數位邏輯設計中之狄摩根定理(DeMorgan’s Theorem)進行簡化,可歸納出非常簡單之結論,推論說明如下

以下列出狄摩根定理(DeMorgan’s Theorem),以供參閱並自行推論。
狄摩根定理(DeMorgan’s Theorem
因符合條件之組合繁多(如下圖中X區域),難以全面考量各種可能狀況,因此應以反向思考,首先列出不符合查詢條件之組合( ,包含 再排除不符合條件者,負負得正,即 

並將執行步驟整理如下:
步驟
圖示
條件式
1.不符合
查詢條件
DayOff.EDate < Query.SDate     --u
  OR DayOff.SDate > Query.EDate --v
(即: 
2.排除
不符合
DayOff.EDate >= Query.SDate     --u
  AND DayOff.SDate <= Query.EDate --v
(即: ……公式3

Step 01不符合查詢條件之組合(
不符合查詢條件包含兩個區域,大於查詢上界時間(UBoundEDate)及小於查詢下界時間(LBoundSDate)SQL及查詢結果,如下。請注意,查詢應包含上界(結束)當天日期,因此查詢條件應採用次日日期而非當日。
SQL
執行結果
SELECT Emp_No
       , CONVERT(CHAR(16), SDate, 120) SDate
       , CONVERT(CHAR(16), EDate, 120) SDate
FROM #DayOff O
WHERE 1=0
OR O.EDate < '2013-09-16'              --(1)
OR O.SDate > DATEADD(dd,1,'2013-09-18')--(2)


Step 02:排除不符合條件者(負負為正,
排除符合前一步驟中之資料,依狄摩根定理將SQL改寫,如下。查詢上界(結束)應採用次日日期,但包含次日該時點,因此公式應修正為小於(<)。
SQL
執行結果
SELECT Emp_No
       , CONVERT(CHAR(16), SDate, 120) SDate
       , CONVERT(CHAR(16), EDate, 120) SDate
FROM #DayOff O
WHERE 1=1
AND O.EDate >= '2013-09-16'             --(1)
AND O.SDate < DATEADD(dd,1,'2013-09-18')--(2)
注意:式(2)為開放式查詢條件,將以FULL TABLE SCAN方式而造成效能問題,可額外增加90天內篩選條件(產假56天)。

由前述運算式中,可歸納出區間查詢基本思考是為任一上限值(U.Bound)必大於另一下限值(L.Bound

最後,以常用(正向)思考模式難以處理區間查詢此類難題,可嘗試以反向思維解決,或者利用數學、數位邏輯(如:卡諾圖)或演算法等概念進行歸維簡化,相信應可找到另類且有效的方法。

資料產生SQL
SELECT * INTO #DayOff
FROM (VALUES ('A01'
              , DATEADD(dd, 0, '2013-09-14 09:00')
                          , DATEADD(dd, 0, '2013-09-23 18:00')    )
           , ('A03', '2013-09-18 09:00', '2013-09-18 18:00')
           , ('A04', '2013-09-18 13:00', '2013-09-23 18:00')
           , ('A07', '2013-09-14 09:00', '2013-09-14 18:00')
           , ('A08', '2013-09-23 09:00', '2013-09-23 18:00')
           , ('A10', '2013-09-13 09:00', '2013-09-13 18:00')
        ) DayOff(Emp_No, SDate, EDate)




沒有留言:

張貼留言