Update using if..case ...

[sleezy]

TotalAmount > 0 is credit and a debit = TotalAmount > 0
So if its a credit l want to insert a zero in the Due_Penalty_On_Interest
Else If its a debit l write to the Due_Penalty_On_Interest with the following
conditions to apply.l have to loop through all the loans and allocate accordingly. How do l structure mt query so it works? I need help
If for paid_Penalty_On_Interest the the date from Due_date.Repayment_table > Paiddate
then it goes in the next period eg.
If entry was on the 26/3 it should be allocated to period 25/4


IF c.TotalAmount < 0
And Datepart(year,r.due_date)'' + '' + '' + Datepart(Month,r.due_date)'' + '' + '' + Datepart(Day,c.PaidDate) =
Datepart(Year,c.PaidDate)+ '' + '' + '' + Datepart(Month,c.PaidDate)'' + '' + '' + Datepart(Day,c.PaidDate) OR

Datepart(year,r.due_date)'' + '' + '' + Datepart(Month,r.due_date)'' + '' + '' + Datepart(Day,c.PaidDate)
(it should also check between the first day to the 25th e.g 1/2/2003-25/2/2003 otherwise where date = 25/3)
=
Datepart(Year,c.PaidDate)+ '' + '' + '' + Datepart(Year,c.PaidDate)'' + '' + '' + Datepart(Year,c.PaidDate)
(Day e.g 25/2 - 26/3 of the same month which should be in that range or the day is between 1/2-25/2)

begin
Update r
Set r.paid_Penalty_On_Interest = c.TotalAmount
From Repayment_table r
,@ConsolidatedPayments c
Where r.Object_Key = c.AccountNo And Tran_Type = 'ACCI'



If ...........

[Claire]

Could you explain your if condition more detaily

I dont think the ''+''+'' is making sense here.U should convert the yr,mon,day to character to compare ,instead of adding all the three numbers together

here is a example
if you set the if condition like this
for
2003/3/3 = 1999/8/2 ,I dont think this is what you want.

If you want to compare the date,in the if condition you can do this

If
(c.TotalAmount < 0 And
cast(Datepart(year,r.due_date) as varchar(4))+''+cast(Datepart(Month,r.due_date) as varchar(2))+''+cast(Datepart(Day,c.PaidDate) as varchar(2))
=cast(Datepart(Year,c.PaidDate) as varchar(4))+''+cast(Datepart(Month,c.PaidDate) as varchar(2))+''+cast(Datepart(Day,c.PaidDate) as varchar(2)))
OR
(c.TotalAmount < 0 And
cast(Datepart(year,r.due_date) as varchar(4))+''+cast(Datepart(Month,r.due_date) as varchar(2))+''+cast(Datepart(Day,c.PaidDate) as varchar(2))
=cast(Datepart(Year,c.PaidDate) as varchar(4))+''+cast(Datepart(Year,c.PaidDate) as varchar(2))+''+cast(Datepart(Year,c.PaidDate)as varchar(2)) )