2013年9月5日 星期四

利用數值序列產生當月日曆資料

許多應用案例中,如銷售、生產日報表,希望呈現整個月完整日期之報表,但因偶發狀況造成當日並未生產或銷售,若公司也未定義日曆資料表,則可能要在AP端或以其他方式解決,在此說明如何應用數值序列以產生當月日曆資料,SQL如下:
功能
SQL (當月日曆)
說明
MSSQL
SELECT number +1  N
  , DATEADD(DD, number, CONVERT(CHAR(8), GETDATE(), 120)+'01') DT
--INTO #Calendar
FROM master.dbo.spt_values
WHERE name IS NULL
AND number<DAY(
DATEADD(MM
, 1
, CONVERT(CHAR(8),GETDATE(), 120) +'01'
)-1
)
--------
月初: CONVERT(CHAR(8),GETDATE(), 120) +'01'
月底: DATEADD(MM, 1, CONVERT(CHAR(8),GETDATE(), 120) +'01')-1
月初:利用CONVERT函數以120型式,取得局部日期字串(2013-09-),再補上01即為所求。

月底:利用前述月初日期,以DATEADD函數加上一個月可得次月月初日期,減1天即為本月月底。
ORACLE
SELECT LEVEL N
      , TRUNC(SYSDATE, 'MM') + LEVEL-1 DT
FROM DUAL
CONNECT BY LEVEL <=TO_CHAR(LAST_DAY(SYSDATE),'DD')
--------
月底:LAST_DAY(SYSDATE)
另外,TO_CHAR(,'DD') 可得到當日日期數值文字,此處預期應用數值才正確,此為少數允許自動轉型之情況
LAST_DAY為取得月底日期函數


SQL是一種非程序語言,優點在於集合(SET)及大量等運算處理,循序處理或複雜邏輯則否,且程式常用之迴圈(Loop)功能(T-SQLPL/SQL非一般SQL),而一般SQL指令並無支援,可參考本範例之概念,搭配數值序列產生額外資料空間以解決此問題。

另外,將應用前述日曆資料,將日期序列轉換為數值序列。
功能
SQL (當月日曆)
說明
MSSQL
SELECT DT
  , DATEDIFF(DD
              , CONVERT(CHAR(8),GETDATE(), 120) +'01'
              ,  DT
              ) +N
  , DT-CAST(CONVERT(CHAR(8),GETDATE(), 120) +'01' 
             AS datetime) DF
FROM #Calendar
WHERE 1=1
     AND DT >= CONVERT(CHAR(8),GETDATE(), 120) +'01'
ORACLE
SELECT DT
     , TRUNC(SYSDATE, 'MM') BOM --月初
     , DT - TRUNC(SYSDATE, 'MM') +1 N
FROM
   (
    SELECT LEVEL N
          , TRUNC(SYSDATE, 'MM') + LEVEL-1 DT
    FROM DUAL
    CONNECT BY LEVEL <=TO_CHAR(LAST_DAY(SYSDATE),'DD')
   )
ORACLE直接使用日期相減,即可得到數值序列。但MSSQL的日期相減卻仍為日期型態,需使用DATEDIFF函數才正確。另外,ORACLE的月初日期可用TRUNC(SYSDATE,'MM')取得,月底日期則為LAST_DAY(SYSDATE)

沒有留言:

張貼留言