Has a month gone by?

Question

In my database I have a table holding some subscription information. I have among others a StartDate and an EndDate as DateTime.

What I need is to make a Linq query getting all rows due for payment. The payment is supposed to take place each month on the same day they registered (StartDate) and stop on the EndDate. So if they registered on the 23. May, I need to invoice them again 23. June, 23. July and so on.

var query = from c in db.Subscription
            where c.StartDate.Value.Day == DateTime.Now.Day
            // What if today is Feb. 28 and a customer registered January 31.
            // What if....

I am lost...please help!

Best, Jon 2H

Answer 1

There is an AddMonths method on DateTime structs.

http://msdn.microsoft.com/en-us/library/system.datetime.addmonths.aspx

Yeah, I guess I was sort of thinking of only a one month difference too.

What about grabbing rows where StartDate.Value.Day == DateTime.Now.Day Or (StartDate.Value.Day > DateTime.Now.Day And DateTime.Now.Day is the last day of the month, whatever the method for that would be).

Answer 2

select
    *
from
   Subscription s
where
   getdate() > dateadd(month, 1, isnull(s.lastBilledDate, s.StartDate) )

Something like that should work. Hopefully you have a field in the database somewhere that shows the last Billed Date date - which would be better to use than the Subscription start date.

Ron

Answer 3

Why don't you create a seperate table for all due payments.

When a new subscription is taken out, you would calculate all future payment dates for that subscription, and add a number of rows into the DuePayments table with the SubscriptionID, PaymentDate & Amount.

The number of rows would equate to the number of months between the subscription start date and end date, and the payment dates could be easily calculated using DateTime.AddMonths(1) while less than end date.

Answer 4

One way to handle month rollover days is (assuming you want to bill them on the last day of the month in the odd case):

var Tomorrow = DateTime.Today.AddDays(1);

var query = from c in db.Subscription
            where c.EndDate.Value > DateTime.Today &&
                  (c.StartDate.Value.Day == DateTime.Today.Day ||
                   (Tomorrow.Month > DateTime.Today.Month && 
                    c.StartDate.Value.Day > DateTime.Today.Day))
            select c;

You might want to create a new table for due payments and calculate the dates upfront instead, though. That way you can keep track of when payments are made as well as make life easier in the future.

Answer 5

Your problem is not strictly programming-related but rather business-related. Let's suppose I don't even have a computer and a software for this project / product / etc.

What if the plan starts of 31th of January? Will I bill them on 31th of February? Not. Then when? How do I compute?

Well the best thing would be to set a precise number of days (let's say 30) and use that as a business model. On the 30th day since the start of the service you will be billed. Or something like this.

In T-SQL and .NET adding 30 days to a DateTime is no big deal :

.NET :

DateTime value = DateTime.Now;
DateTime billTime = value.AddDays(30);

T-SQL :

DATEADD(DAY,30,value)