SQL figuring out days remaining based on days interval (using SQL Server 2005)?

Question

I have a table that represents a user's subscription to our system. I have two tables. One represents the subscription for an account and the other represents the type of subscription. The subscription table relates to the subscription type table with a subscriptionTypeID field in the subscription types table. I need to figure out the days remaining for that account. Here is my example:

The user signed up on January 1, 2010 and their term is 30 days. Based on today's date (January 25, 2010) they would have 6 days remaining.

I need help designing the SQL. Here is what I have so far in my stored procedure:

@SubscriptionTypesID int

Declare @term int
Declare remainder int
Set @term = (SELECT subscriptionTerm 
         FROM dbo.SubscriptionTypes 
         where dbo.SubscriptionTypes.SubscriptionTypesID = @SubscriptionTypesID)

Now i need to figure out what to do with the remainder query, or if I can somehow have one SQL statement so I don't have to get the term separately.

Update:

Here is what I got now with all your help, but I still want a more elgant way to pump the term field value into the query:

Select (DateDiff(day,getDate(),DATEADD (day , 30, '01/01/2010' ))) days

Update 2.0 ;)

Sometimes the answer is right in front of me and I can't even see it. I tend to over think problems and make them more complicated than they need to be. Thanks to everyone who helped! HEre is the code:

SELECT (DateDiff(day,getDate(),DATEADD (day , subscriptionTerm, dbo.Subscriptions.subscriptionDate ))) days
FROM
  dbo.Subscriptions
  INNER JOIN dbo.SubscriptionTypes ON (dbo.Subscriptions.subscriptionTypeID = dbo.SubscriptionTypes.SubscriptionTypesID)
WHERE
  dbo.Subscriptions.userID = 129

Answer 1

Err, why don't you just compare the current date to the subscription date +30 days? I believe that would look like

if(GETDATE() > DATEADD(day,30,@SubscriptionDate))
BEGIN

END

In the comments below you asked how to get teh number of days. SQL has a method for that...

SELECT DATEDIFF(datepart,start,end)

To get the value and use it in different places you can use the following syntax:

DECLARE @Difference int
SELECT @Difference = DATEDIFF(day,@SubscriptionDate,GETDATE())

Answer 2

You can use Datadiff functionality from sql:

http://msdn.microsoft.com/en-us/library/ms189794.aspx

You could also solve it using:

DATEADD (datepart , number, date )

You can find more in the same MSDN page, just use the searchbox and type "Dateadd"

Good luck with your remaining time thingie ;)

Answer 3

HEre it is...Thanks all for the insight:

SELECT (DateDiff(day,getDate(),DATEADD (day , subscriptionTerm, dbo.Subscriptions.subscriptionDate ))) days 
FROM 
  dbo.Subscriptions 
  INNER JOIN dbo.SubscriptionTypes ON (dbo.Subscriptions.subscriptionTypeID = dbo.SubscriptionTypes.SubscriptionTypesID) 
WHERE 
  dbo.Subscriptions.userID = 129