Computed 'Days to go' Column

Given a column of dates, I would like to create a computed column showing
how many days from the current date until that date (ignoring the year) next
occurs.
E.G. given 3 rows:
DateID StartDate
1 2 January 1954
2 1 March 1978
3 30 December 2001
if today is 1st Jan 2005 (non-leap year) I would like a resultset like:
DateID StartDate DaysToGo
1 2 January 1954 1
2 1 March 1978 59
3 31 December 2001 364
and on 1st Jan 2008 (leap year):
DateID StartDate DaysToGo
1 2 January 1954 1
2 1 March 1978 60
3 31 December 2001 365
I have a stored procedure that does the calculation correctly (I think ;),
however it requires parameters, and I need a computed column or view. ANy
help much appreciated
TIA,
Paul Bryant
===================
ALTER PROCEDURE DaysToGo
@.DateID int
AS
DECLARE @.OldDay nvarchar(2), @.OldMonth nvarchar(20), @.NextDate DateTime
SET @.OldDay =
(SELECT CAST(DATEPART(d, tblDates.StartDate) AS NVARCHAR) AS OldDay
FROM tblDates
WHERE tblDates.DateID = @.DateID)
SET @.OldMonth =
(SELECT DATENAME(m,tblDates.StartDate) AS OldMonth
FROM tblDates
WHERE tblDates.DateID = @.DateID)
SELECT @.NextDate = @.OldDay + ' ' + @.OldMonth + ', ' + CAST(YEAR(GETDATE())
AS NVARCHAR)
IF DATEDIFF(d, GETDATE(), @.NextDate) < 0
SELECT DATEDIFF(d, GETDATE(), DATEADD(yy, 1, @.NextDate)) AS DaysToGo
ELSE
SELECT DATEDIFF(d, GETDATE(), @.NextDate) AS DaysToGo
================================================== =========
You could use a calendar table for this... just populate it as far out as
you need, then you can add DATEADD(YEAR, YEAR(GETDATE()), '19540102') to get
January 2, 1954, then take the datediff in days between today and that date.
To see how to populate the calendar table:
http://www.aspfaq.com/2519
If you decide to go that route, we can help you with more specific code.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
(Reverse e-mail to reply.)
"Paul Bryant" <paul@.gap66.com> wrote in message
news:%23x%23t0jlREHA.2520@.TK2MSFTNGP11.phx.gbl...
> Given a column of dates, I would like to create a computed column showing
> how many days from the current date until that date (ignoring the year)
> next
> occurs.
> E.G. given 3 rows:
> DateID StartDate
> 1 2 January 1954
> 2 1 March 1978
> 3 30 December 2001
> if today is 1st Jan 2005 (non-leap year) I would like a resultset like:
> DateID StartDate DaysToGo
> 1 2 January 1954 1
> 2 1 March 1978 59
> 3 31 December 2001 364
> and on 1st Jan 2008 (leap year):
> DateID StartDate DaysToGo
> 1 2 January 1954 1
> 2 1 March 1978 60
> 3 31 December 2001 365
> I have a stored procedure that does the calculation correctly (I think ;),
> however it requires parameters, and I need a computed column or view. ANy
> help much appreciated
> TIA,
> Paul Bryant
> ===================
> ALTER PROCEDURE DaysToGo
> @.DateID int
> AS
> DECLARE @.OldDay nvarchar(2), @.OldMonth nvarchar(20), @.NextDate DateTime
> SET @.OldDay =
> (SELECT CAST(DATEPART(d, tblDates.StartDate) AS NVARCHAR) AS OldDay
> FROM tblDates
> WHERE tblDates.DateID = @.DateID)
> SET @.OldMonth =
> (SELECT DATENAME(m,tblDates.StartDate) AS OldMonth
> FROM tblDates
> WHERE tblDates.DateID = @.DateID)
> SELECT @.NextDate = @.OldDay + ' ' + @.OldMonth + ', ' + CAST(YEAR(GETDATE())
> AS NVARCHAR)
> IF DATEDIFF(d, GETDATE(), @.NextDate) < 0
> SELECT DATEDIFF(d, GETDATE(), DATEADD(yy, 1, @.NextDate)) AS DaysToGo
> ELSE
> SELECT DATEDIFF(d, GETDATE(), @.NextDate) AS DaysToGo
>
> ================================================== =========
>
|||BTW and FWIW, I added this exact example to the article.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
(Reverse e-mail to reply.)
sqlsql