Finding Due Days - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Finding Due Days

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2007-06-11 : 18:10:24

Iam facing a problem to find the due days:

Declare @next_payment_date datetime

Case 1:
SET @next_payment_date = '2007-05-11'
Today its : '2007-06-11' which means that they are due for 2 months

Case 2:
SET @next_payment_date = '2007-05-20'
Today its : '2007-06-11' which means that they are due for 1 month

How can I calculate how many days are they due when it compares different months.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-11 : 20:08:27

What is you definition of 1 month ?
How about @next_payment_date = '2007-04-11' and today is '2007-05-11' ?

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2007-06-11 : 20:36:09

There is no fixed days to calculate the month.
Based on the above statement :The person is due for 2 months because the first due month was '2007-04-11' and the second is '2007-05-11'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-11 : 21:01:09

[code]
declare @today datetime

select @today = '2007-06-11'

select next_payment_date,
due_mth = case when pymt_this_mth > @today
then datediff(month, next_payment_date, @today)
else datediff(month, next_payment_date, @today) + 1
end
from
(
select next_payment_date,
pymt_this_mth = dateadd(month, datediff(month, 0, @today), day(next_payment_date) - 1)
from
(
select '2007-05-11' as next_payment_date union all
select '2007-04-11' as next_payment_date union all
select '2007-05-20'
) p
) d
/* RESULT :
next_payment_date due_mth
----------------- -----------
2007-05-11 2
2007-04-11 3
2007-05-20 1
*/
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources