DATEADD Function not working like I am expecting
-
No, notice the if statements... if the date i pass in doesn't equal the end of the month then do something else.... and the other if...
-
Problem I see is if you are basing this off the last payment. Do you ever look back at the initial payment date?
-
I'm not given the Origination Date... I have a column AS_OF_DATE so we now when the table was created, I have the PREVIOUS_PAYMENT_DATE, NEXT_PAYMENT_DATE AND MATURITY_DATE.
Then we move forward from there to get what our PRINCIPAL_PAYMENTS are... I guess because of that, is why the Origination Date isn't important...
Does that make sense?
Thanks,
John
-
Here is what I am trying to get at:
what you are getting what you want
2008-01-30 first payment 2008-01-30
2008-02-29 second payment 2008-02-29
2008-03-29 third payment 2008-03-30
2008-04-29 fourth payment 2008-04-30
...
2009-01-29 13th payment 2009-01-30
2009-02-28 14th payment 2009-02-28
2009-03-28 15th payment 2009-03-30
...
based on previous payment date, I don't see how you will get what you want without knowing the first payment date.
-
Ok, row 85 is a new ID, 101 and 102 are new ID's and so is 103 ending @ 111...
when i get the data, it's not ballooned out like this yet, i do that in another stage.
Notice the NEXT_PAYMENT_DATE; rows 85-100 are given to me, and rows 101 thru 111 are not. so to be consitent I created CALULTD_NEXT_PAY_DATE to give the actual dates to see when I hit the MATURITY_DATE.
From this data I get, it doesn't matter that I don't have an Origination Date, we only care about future Payment Info...
But also I think another thing you may be trying to get out of me is this:
notice the last rows in each ID 100, 101, 102 and 111... 111 is over... I am using Set Based Query's in an UPDATE STATEMENT (like a loop) so I have a variable to track my previous calculated Payment Date to give me the Next payment Date, thus when I hit Maturity Date, I add the special sauce...lol
Am I getting better??? LOL
John
-
john.steinbeck (11/21/2008)
Ok, row 85 is a new ID, 101 and 102 are new ID's and so is 103 ending @ 111...when i get the data, it's not ballooned out like this yet, i do that in another stage.
Notice the NEXT_PAYMENT_DATE; rows 85-100 are given to me, and rows 101 thru 111 are not. so to be consitent I created CALULTD_NEXT_PAY_DATE to give the actual dates to see when I hit the MATURITY_DATE.
From this data I get, it doesn't matter that I don't have an Origination Date, we only care about future Payment Info...
But also I think another thing you may be trying to get out of me is this:
notice the last rows in each ID 100, 101, 102 and 111... 111 is over... I am using Set Based Query's in an UPDATE STATEMENT (like a loop) so I have a variable to track my previous calculated Payment Date to give me the Next payment Date, thus when I hit Maturity Date, I add the special sauce...lol
Am I getting better??? LOL
John
Huh?? I'm not seeing the embedded image you posted.
-
Yes, I got the image via PM. You may want to see if you can get it into this forum thread so others can see it. If needed, zip it and uploaded as a zip file.
What I see in the image, however, are payment dates on the first of the month. You said earlier that the payment dates are any time during the month. We really need to see the variety of dates you have to deal with. As you can see from using the DATEADD function, everything works great as long as you are working with dates that exist in all the months. the problem creeps in when you are dealing with dates near the end of a month.
-
I never saw the attachments... doh;
ok, so i ran a couple of the ID's with variety thru the fuction I posted early... this should give you a visual of what I am doing... the pic Example2, isn't blown out with all the dates, they will because I have the right NEXT_PAYMENT_DATE, when i do that...
Thanks,
John
UPDATE[A] --[dbo].[Amortization]
SET
[A].[NEXT_PAYMENT_DATE]= [dbo].[fn_AM_CALC_NEXT_PAY_DATE]
(
[A].[PMT_FREQ_MULT],
[A].[PMT_FREQ],
[A].[LAST_PAYMENT_DATE],
0
)
FROM
[dbo].[Amortization] [A]
WHERE
[A].[TBL_SOURCEID] = '1'
-
More work for you. Would you please explain the PMT_FREQ, PMT_FREQ_MULT, any gotcha's we may need to know (like no payments due on Saturdays or Sundays, or official holidays).
I'd rather have this explained than try to figure it out based on code alone if possible.
-
Nothing like that at all, at least they didn't require that yet...lol
[A].[PMT_FREQ_MULT] = @PMT_FREQ_MULT: this tells me which DATEADD to use in the CASE Statement M Month, D Day, Y Year or Date_Part
[A].[PMT_FREQ] = @PMT_FREQ: this tell me the Number in the DATEADD to use... or the Interval
SET
[A].[NEXT_PAYMENT_DATE]= [dbo].[fn_AM_CALC_NEXT_PAY_DATE]([A].[PMT_FREQ_MULT], [A].[PMT_FREQ], [A].[LAST_PAYMENT_DATE], 0)
CASE @PMT_FREQ_MULT
WHEN 'M' THEN DATEADD(MONTH, @PMT_FREQ, @NEXT_PAY_DATE)
WHEN 'D' THEN DATEADD(DAY , @PMT_FREQ, @NEXT_PAY_DATE)
WHEN 'Y' THEN DATEADD(YEAR ,@PMT_FREQ, @NEXT_PAY_DATE)
END
-
john.steinbeck (11/21/2008)
Am I getting better??? LOLNot even close.
Not even looking into right direction.
Your task does not have solution with the data you've given.
Simple question.
Last payment date is 29 Feb 2008.
What must be the next payment date?
29 Mar, 30 Mar or 31 Mar?
_____________
Code for TallyGenerator -
Well it seems to be working... would you care to give me a date that wouldn't work and I can go from there...
No offense, I come here to try and help others and to get help; but you seem to like to antagonize rather than help... or maybe this is your way, not sure... if I am wrong I apologize...
-
Sergiy's question
Simple question.
Last payment date is 29 Feb 2008.
What must be the next payment date?
29 Mar, 30 Mar or 31 Mar?
Actually, quite reasonable, and I'd like to see your answer. I can also see where Sergiy is going and I am anticipating is next question, which will ask you the same thing, but will provide some background information. Just watch, I'll bet money on it if I could.
-
jsteinbeck (11/21/2008)
Well it seems to be working... would you care to give me a date that wouldn't work and I can go from there...No offense, I come here to try and help others and to get help; but you seem to like to antagonize rather than help... or maybe this is your way, not sure... if I am wrong I apologize...
I antagonize only stupidity.
You asking for date?
I just gave you.
Sorry for you you did not get it.
What should be the next March pay date for a loan initiated on 30 Oct 2008?
And what you method is working out?
What about 29 Oct 2008? Any way you can get 29 March 2009 from last payment date 28 Feb 2009?
_____________
Code for TallyGenerator -
Sergiy (11/22/2008)
jsteinbeck (11/21/2008)
Well it seems to be working... would you care to give me a date that wouldn't work and I can go from there...No offense, I come here to try and help others and to get help; but you seem to like to antagonize rather than help... or maybe this is your way, not sure... if I am wrong I apologize...
I antagonize only stupidity.
You asking for date?
I just gave you.
Sorry for you you did not get it.
What should be the next March pay date for a loan initiated on 30 Oct 2008?
And what you method is working out?
What about 29 Oct 2008? Any way you can get 29 March 2009 from last payment date 28 Feb 2009?
I was right, too bad I couldn't put money on the bet. That is right where I expected Sergiy to go with his question.
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply