SQL

SELECT stk.PART_NO AS 商品條碼, stk.PARTNAME2 AS 商品名稱, BASEDB.dbo.fnGetRetailItemPackingSpec(stk.part_no) AS 包裝規格, r.unit AS 單位, r.QTY AS 數量, r.PRICE 
       * (1 -r.discount / 100.0) AS 單價, r.PRICE * (1 -r.discount / 100.0) * r.QTY AS 金額, r.CUST_NAME AS 生產單位, stk.intShelfLifeInMonth * 30 AS 保質期_天, 
  CASE WHEN   r.dtmSuppManuDate IS NOT NULL
  THEN CONVERT(NVARCHAR(10), r.dtmSuppManuDate, 111)  
  ELSE
    CASE WHEN   r.dtmExpDate IS NOT NULL
    THEN CONVERT(NVARCHAR(10),DATEADD(d, stk.intShelfLifeInMonth * 30, r.dtmExpDate),111)  
    ELSE '未輸入' 
    END
  END AS 生產日期
 FROM 
(
    SELECT r.PART_NO, r.unit, r.QTY, r.PRICE, r.discount, rh.CUST_NAME, MAX(sl.dtmExpDate) AS dtmExpDate, MAX(sl.dtmSuppManuDate) AS dtmSuppManuDate
    FROM   RNOTEH rh
    INNER JOIN RNOTE r ON  rh.RNOTE_NO = r.RNOTE_NO
    INNER JOIN stlotldg slg ON  slg.doc_no = rh.RNOTE_NO AND slg.PART_NO = r.PART_NO AND slg.PN_REFNO = r.PN_REFNO
    INNER JOIN STOCKLOT sl ON  sl.LOT_NO = slg.LOT_NO
    WHERE  rh.rnote_no = '2006101987'
    GROUP BY r.PART_NO, r.unit, r.QTY, r.PRICE, r.discount, rh.CUST_NAME
)r
INNER JOIN basedb..stock stk ON stk.part_no = r.part_no