□取得保單(當時)對應的所有的賠案編號
SELECT   OSTD.OID AS OID_OSTD
,iclaim =  case when  LOSS.iclaim is null then  OSTD.iclaim
                else LOSS.iclaim
                end
,Flag = case when  LOSS.iclaim is null then  '0'   --/'未決金額= CLMM_CM_CLAIM
             when  LOSS.dclose is null then  '1'  --'未決金額= CLMM_CM_SETTLE
             else '2'    --   '已決金額= CLMM_CM_SETTLE
             end
,iclaim_type =  case when  LOSS.iclaim is null then  OSTD.iclaim_type
                         else LOSS.iclaim_type
                         end         
 FROM (( select distinct   iclaim , iclaim_type , dclose  from  CLMM_CM_SETTLE   WHERE DCLOSE <= '2009/5/15'  ) LOSS   
	right outER JOIN (select  distinct   OID, iclaim , iclaim_type, ipolicy, dclose  ,dclaim  from CLMM_CM_CLAIM 
	where ipolicy='ZP96GLG20502' AND DCLAIM <='2009/5/15' )  OSTD  
	ON Loss.iclaim = OSTD.iclaim AND Loss.iclaim_type = OSTD.iclaim_type );

OUTPUT EXAMPLE:
OID_OSTD, ICLAIM,     FLAG, ICLAIM_TYPE
5000048	04GA070411	2	0
5000067	04GA070411	2	1
5000088	04GA070411	2	2
5000142	ZSGA070261	2	0
5002082	ZSGA070908	2	0
5003544	ZSGA070908	2	1


□CASE
SELECT FYear, FLevel,FPoint,
 SUM(CASE FMonth WHEN 1 THEN FCount ELSE 0 END) AS M01,
 SUM(CASE FMonth WHEN 2 THEN FCount ELSE 0 END) AS M02,
 SUM(CASE FMonth WHEN 3 THEN FCount ELSE 0 END) AS M03,
 SUM(CASE FMonth WHEN 4 THEN FCount ELSE 0 END) AS M04,
 SUM(CASE FMonth WHEN 5 THEN FCount ELSE 0 END) AS M05,
 SUM(CASE FMonth WHEN 6 THEN FCount ELSE 0 END) AS M06,
 SUM(CASE FMonth WHEN 7 THEN FCount ELSE 0 END) AS M07,
 SUM(CASE FMonth WHEN 8 THEN FCount ELSE 0 END) AS M08,
 SUM(CASE FMonth WHEN 9 THEN FCount ELSE 0 END) AS M09,
 SUM(CASE FMonth WHEN 10 THEN FCount ELSE 0 END) AS M10,
 SUM(CASE FMonth WHEN 11 THEN FCount ELSE 0 END) AS M11,
 SUM(CASE FMonth WHEN 12 THEN FCount ELSE 0 END) AS M12,
FROM TVolumeSum
Where FType='R1'
GROUP BY FYear, FLevel, FPoint
ORDER BY FLevel, FYear, FPoint

□CASE NULL
SELECT    'Price Category' = 
      CASE 
         WHEN price IS NULL THEN 'Not yet priced'
         WHEN price < 10 THEN 'Very Reasonable Title'
         WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
         ELSE 'Expensive book!'
      END,
   CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price

□CASE SUBSTRING
SELECT SUBSTRING((RTRIM(a.au_fname) + ' '+ 
   RTRIM(a.au_lname) + ' '), 1, 25) AS Name, a.au_id, ta.title_id,
   Type = 
  CASE 
    WHEN SUBSTRING(ta.title_id, 1, 2) = 'BU' THEN 'Business'
    WHEN SUBSTRING(ta.title_id, 1, 2) = 'MC' THEN 'Modern Cooking'
    WHEN SUBSTRING(ta.title_id, 1, 2) = 'PC' THEN 'Popular Computing'
    WHEN SUBSTRING(ta.title_id, 1, 2) = 'PS' THEN 'Psychology'
    WHEN SUBSTRING(ta.title_id, 1, 2) = 'TC' THEN 'Traditional Cooking'
  END
FROM titleauthor ta JOIN authors a ON ta.au_id = a.au_id