喉嚨痛 聲都失埋 好痛苦@@
Oh My God

Imperial Ice Star



http://www.imperialicestars.com/

http://www.fotop.net/DonaldChin/Cinderella_On_Ice

/* Start JOB */
 SELECT CURR.EMPLID
 , CURR.EMPL_RCD
 , CURR.EFFDT
 , CURR.EFFSEQ
 , CURR.C_POST_TITLE
 , (
 SELECT MIN(T.EFFDT)
  FROM PS_JOB T
 WHERE T.EMPLID = CURR.EMPLID
   AND T.EMPL_RCD = CURR.EMPL_RCD
   AND T.C_POST_TITLE = CURR.C_POST_TITLE
   AND T.EFFDT > NVL(PRE_JOB.PRE_POST_EFFDT, TO_DATE('1990-01-01','YYYY-MM-DD'))
   AND T.EFFDT < NVL(POST_JOB.NEXT_POST_EFFDT, TO_DATE('9990-12-31','YYYY-MM-DD')) ) "CURR_POST_STARTDT" , POST_JOB.NEXT_POST_EFFDT - 1 "CURR_POST_ENDDT" , PRE_JOB.PRE_POST_EFFDT, PRE_JOB.PRE_POST, POST_JOB.NEXT_POST_EFFDT, POST_JOB.NEXT_POST
  FROM PS_JOB CURR LEFT OUTER JOIN /* ------- BEGIN Pre Job TABLE */ (
 SELECT B.EMPLID
 , B.EMPL_RCD
 , B.EFFDT
 , B.C_POST_TITLE
 , B.PRE_POST_EFFDT
 , A.C_POST_TITLE "PRE_POST"
  FROM PS_JOB A
  , (
 SELECT JOB1.EMPLID
 , JOB1.EMPL_RCD
 , JOB1.EFFDT
 , JOB1.C_POST_TITLE
 , MAX(PRE_POST1.EFFDT) PRE_POST_EFFDT
  FROM PS_JOB JOB1
  , PS_JOB PRE_POST1
 WHERE PRE_POST1.EMPLID = JOB1.EMPLID
   AND PRE_POST1.EMPL_RCD = JOB1.EMPL_RCD
   AND PRE_POST1.C_POST_TITLE <> JOB1.C_POST_TITLE
   AND PRE_POST1.EFFDT < JOB1.EFFDT
   AND PRE_POST1.JOB_INDICATOR = JOB1.JOB_INDICATOR
   AND PRE_POST1.JOB_INDICATOR = 'P'
  GROUP BY JOB1.EMPLID, JOB1.EMPL_RCD, JOB1.EFFDT, JOB1.C_POST_TITLE ) B
 WHERE A.EMPLID = B.EMPLID
   AND A.EMPL_RCD = B.EMPL_RCD
   AND A.JOB_INDICATOR = 'P'
   AND A.EFFDT = B.PRE_POST_EFFDT
   AND A.EFFSEQ = (
 SELECT MAX(T.EFFSEQ)
  FROM PS_JOB T
 WHERE A.EMPLID = T.EMPLID
   AND A.EMPL_RCD = T.EMPL_RCD
   AND A.EFFDT = T.EFFDT
   AND T.JOB_INDICATOR = 'P' ) ) PRE_JOB /*------- END Pre Job TABLE */ ON CURR.EMPLID = PRE_JOB.EMPLID
   AND CURR.EMPL_RCD = PRE_JOB.EMPL_RCD
   AND CURR.EFFDT = PRE_JOB.EFFDT
   AND CURR.C_POST_TITLE = PRE_JOB.C_POST_TITLE LEFT OUTER JOIN /*------- BEGIN NEXT Job TABLE */ (
 SELECT B.EMPLID
 , B.EMPL_RCD
 , B.EFFDT
 , B.C_POST_TITLE
 , B.NEXT_POST_EFFDT
 , A.C_POST_TITLE "NEXT_POST"
  FROM PS_JOB A
  , (
 SELECT JOB1.EMPLID
 , JOB1.EMPL_RCD
 , JOB1.EFFDT
 , JOB1.C_POST_TITLE
 , MIN(NEXT_POST1.EFFDT) NEXT_POST_EFFDT
  FROM PS_JOB JOB1
  , PS_JOB NEXT_POST1
 WHERE NEXT_POST1.EMPLID = JOB1.EMPLID
   AND NEXT_POST1.EMPL_RCD = JOB1.EMPL_RCD
   AND NEXT_POST1.C_POST_TITLE <> JOB1.C_POST_TITLE
   AND NEXT_POST1.EFFDT > JOB1.EFFDT
   AND NEXT_POST1.JOB_INDICATOR = JOB1.JOB_INDICATOR
   AND NEXT_POST1.JOB_INDICATOR = 'P'
  GROUP BY JOB1.EMPLID, JOB1.EMPL_RCD, JOB1.EFFDT, JOB1.C_POST_TITLE ) B
 WHERE A.EMPLID = B.EMPLID
   AND A.EMPL_RCD = B.EMPL_RCD
   AND A.JOB_INDICATOR = 'P'
   AND A.EFFDT = B.NEXT_POST_EFFDT
   AND A.EFFSEQ = (
 SELECT MIN(T.EFFSEQ)
  FROM PS_JOB T
 WHERE A.EMPLID = T.EMPLID
   AND A.EMPL_RCD = T.EMPL_RCD
   AND A.EFFDT = T.EFFDT
   AND T.JOB_INDICATOR = 'P' ) ) POST_JOB ON CURR.EMPLID = POST_JOB.EMPLID
   AND CURR.EMPL_RCD = POST_JOB.EMPL_RCD
   AND CURR.EFFDT = POST_JOB.EFFDT
   AND CURR.C_POST_TITLE = POST_JOB.C_POST_TITLE /*------- END NEXT Job TABLE */
 WHERE CURR.JOB_INDICATOR = 'P'
  ORDER BY CURR.EMPLID, CURR.EMPL_RCD, CURR.EFFDT /*----- END JOB*/

http://hk.news.yahoo.com/article/090706/8/d1ml.html

http://www.miniforum.org/showpost.fcgi?MGID=2637069&page=1&tempid=2