產生債券折溢價 TABLE，之後可直接查表

fn_cal_bond_prum.sql

/*---------------------------------
@tradeno 交易單號
@opt : 0 直線法, 1 利息法
---------------------------------*/

CREATE FUNCTION fn_CalPrum(@tradeno varchar(40), @opt smallint)
RETURNS @discount TABLE (sday smalldatetime, eday smalldatetime, qty numeric(20,5), payint numeric(20,5), in_int numeric(20,5), out_int numeric(20,5), prum numeric(20,5), dailyPrum numeric(20,5), amount numeric(20,5))
BEGIN

  DECLARE @r numeric(20,10), @y numeric(20,10), @code varchar(40), @qty numeric(20,5), @payint numeric(20,5), @amount numeric(20,5), @calDate smalldatetime

  --在交易檔抓取相關數值
  SELECT @r = face_rate/100.0, @y = yield_rate/100.0, @code = code, @qty = qty,  @payint = payint, @calDate = [date], @amount = cost - payint
  FROM bnd_deal WHERE trade_no = @tradeno

  --抓付息日
  INSERT @discount     
  SELECT pCDay, nCDay, @qty, 0, 0, 0, 0, 0, 0
  FROM fn_GetAllCouponInterval(@code, 0) WHERE nCDay > @calDate

  DECLARE @dMin smalldatetime, @dMin2 smalldatetime, @dMax smalldatetime
  SELECT @dMin = MIN(sday), @dMin2 = MIN(eday), @dMax = MAX(eday) FROM @discount

  --起息日之前購買
  IF @dMin > @calDate SET @calDate = @dMin

  --直線法
  IF @opt = 0 UPDATE @discount SET dailyPrum = (@qty-@amount) / DATEDIFF(D, @calDate, @dMax)    
  --利息法
  ELSE
  BEGIN       
    UPDATE @discount SET in_int = @qty * c.amount / 100000.0
    FROM cmn_bond_return c WHERE eday = return_date AND tran_type = 2 AND code = @code

    --每年付息次數
    DECLARE @f int
    SELECT @f = dbo.fn_CalCouponFrequency(@code, 0)
    SELECT @r = @r / @f, @y = @y / @f

    DECLARE @m numeric(8,5)       
    SELECT @m = SUM(molecular) FROM cmn_bond_return WHERE return_date < @dMin2 AND code = @code AND tran_type = 2
    SET @m = ISNULL(@m,0)

    UPDATE @discount SET payint = @payint WHERE sday = @dMin      

    DECLARE @sd smalldatetime, @ed smalldatetime
    DECLARE #c cursor fast_forward for (SELECT sday, eday FROM @discount)

    OPEN #c
    FETCH NEXT FROM #c INTO @sd, @ed
    WHILE @@fetch_status = 0
    BEGIN       
      UPDATE @discount SET qty = qty * (100.0-@m) / 100.0 WHERE sday = @sd
      UPDATE @discount SET out_int = @amount * @y WHERE sday = @sd
      UPDATE @discount SET prum = out_int-in_int WHERE sday = @sd

      IF (@sd = @dMin)
      BEGIN
        DECLARE @dRatio float = (DATEDIFF(D, @calDate, @dMin2) * 1.0) / DATEDIFF(D, @dMin, @dMin2)

        UPDATE @discount
        SET in_int = in_int * @dRatio, out_int = out_int * @dRatio, prum = prum * @dRatio           
        WHERE sday = @sd

        UPDATE @discount
        SET dailyPrum = prum*1.0/DATEDIFF(D, @calDate, eday)
        WHERE sday = @sd
      END         
      ELSE UPDATE @discount SET dailyPrum = prum*1.0/DATEDIFF(D,sday, eday) WHERE sday = @sd

      UPDATE @discount SET amount = @amount + prum WHERE sday = @sd

      SELECT @amount = amount FROM @discount WHERE sday = @sd

      --還本部分
      SELECT @m = @m + molecular FROM cmn_bond_return WHERE return_date = @ed AND code = @code AND tran_type = 4

      FETCH NEXT FROM #c INTO @sd, @ed
    END
    CLOSE #c
    DEALLOCATE #c     
  END
  UPDATE @discount SET sday = @calDate WHERE eday = @dMin2    
  RETURN
END