Author Topic: MSSQL - Mohon komentarnya...  (Read 1292 times)

0 Members and 1 Guest are viewing this topic.

Offline deddyw

  • Newbie
  • *
  • Posts: 7
    • http://
MSSQL - Mohon komentarnya...
« on: April 06, 2009, 02:20:14 PM »
Ceritanya sy coba2 bikin script utk menampilkan payment plan sederhana. Kasus yg saya ambil sih utk properti.
Kalau berkenan dikasih kritik, komentar, saran utk improvement...

Code: [Select]
Declare @PM_Sch Table(
Cust_ID VarChar(10),
Pay_Type VarChar(10),
Pay_Freq Int,
Pay_Amt Float,
Pay_Time Char(1))

Declare @Pm_Trx_Sch Table(
Cust_ID VarChar(10),
Pay_Type VarChar(10),
Pay_Cnt Int,
Pay_Date SmallDateTime,
Pay_Amt Float)

Insert Into @Pm_Sch Values ('C001', 'BF', 1, 10000000, 'D' )
Insert Into @Pm_Sch Values ('C001', 'DP', 3, 30000000, 'M' )
Insert Into @Pm_Sch Values ('C001', 'PY', 10, 50000000, 'M' )

Declare @StartDate SmallDateTime, @PayDate SmallDateTime
Set @StartDate = '1/1/9'
Set @PayDate = '1/1/9'

Declare @F_Cust_ID VarChar(10), @F_Pay_Type VarChar(10),
@F_Pay_Freq Int, @F_Pay_Amt Float, @F_Pay_Time Char(1)

Declare @F_Cnt Int, @F_Cnt1 Int
Set @F_Cnt = 1
Set @F_Cnt1 = 1

Declare Schedule_Cursor Cursor For
Select Cust_ID, Pay_Type, Pay_Freq, Pay_Amt, Pay_Time From @Pm_Sch
Open Schedule_Cursor

Fetch Next From Schedule_Cursor
Into @F_Cust_ID, @F_Pay_Type,
@F_Pay_Freq, @F_Pay_Amt, @F_Pay_Time

While @@Fetch_Status = 0
Begin
While @F_Cnt <= @F_Pay_Freq
Begin
If @F_Cnt1 = 1 And @F_Pay_Type = 'D' Set @PayDate = @StartDate
If @F_Pay_Time = 'D' And @F_Cnt1 > 1 Set @PayDate = DateAdd(Day, 1, @StartDate)
If @F_Pay_Time = 'W' And @F_Cnt1 > 1 Set @PayDate = DateAdd(Week, 1, @StartDate) 
If @F_Pay_Time = 'M' And @F_Cnt1 > 1 Set @PayDate = DateAdd(Month, 1, @StartDate) 
If @F_Pay_Time = 'Y' And @F_Cnt1 > 1 Set @PayDate = DateAdd(Year, 1, @StartDate)
Insert Into @PM_Trx_Sch Values(@F_Cust_ID, @F_Pay_Type, @F_Cnt, @PayDate, @F_Pay_Amt)
Set @F_Cnt1 = @F_Cnt1 + 1
Set @F_Cnt = @F_Cnt + 1
Set @StartDate = @PayDate
End
Fetch Next From Schedule_Cursor 
Into @F_Cust_ID, @F_Pay_Type, 
@F_Pay_Freq, @F_Pay_Amt, @F_Pay_Time
Set @F_Cnt = 1
End

Close Schedule_Cursor
Deallocate Schedule_Cursor
Select * From @PM_Trx_Sch

thx sebelumnya... :icon_biggrin: