When's Your Anniversary
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/anniversary.asp
-
I thoroughly enjoyed this article. If I remember right, in Henderson's book The Guru's Guide to Transact-SQL he runs into the end of year issue as well with the query he gives, but working out the issue wasn't a burning priority on my issue list. I'm glad you took the time, Steve.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
Actually I personally whave avoided the union the way you did as you techinically query the data twice and build two seperate outputs that are stacked together. Are a large database that will be the largest portion of your performance loss.
First we know @today will by the way you wrote it be your least value and @future or @lastday will be you greatest date. Taking that into account then any value moved to the current year less than our @today value can be rolled forward 1 more year in effect sliding all your dates foward that fit that criteria. So with that I would have done this in all situations:
SELECT
*
FROM
MyTest
WHERE
(CASE
WHEN dateadd( year, datediff( year, birthday, @today), birthday) < @today THEN dateadd( year, datediff( year, birthday, @today) + 1, birthday)
ELSE dateadd( year, datediff( year, birthday, @today), birthday)
END) BETWEEN @today AND @future
Don't roll your eyes at me. I will tape them in place.
-
-
Thanks, interesting solutions is my forte.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
Another solution using OR
declare @today datetime
declare @lastday datetime
select @today = '11/01/2001'
select @lastday = DATEADD(m,4,@today)
select person,birthday
from myTest
WHERE (dateadd( year, datediff( year, birthday, @today), birthday) >= @today and
dateadd( year, datediff( year, birthday, @today), birthday) <= @lastday) OR
(dateadd( year, datediff( year, birthday, @today)+1, birthday) >= @today and
dateadd( year, datediff( year, birthday, @today)+1, birthday) <= @lastday)
Mike
-
Here is one using only DAY(), MONTH() AND YEAR() functions:
SELECT * FROM MyTest WHERE
year(@today)=year(@lastday) and month(@today)<month(@lastday) and
( month(birthday)>month(@today) and month(birthday)<month(@lastday)
or month(birthday)=month(@today) and day(birthday)>=day(@today)
or month(birthday)=month(@lastday) and day(birthday)<=day(@lastday)
)
or year(@today)=year(@lastday) and month(@today)=month(@lastday) and
month(birthday)=month(@today) and day(birthday) between day(@today) and day(@lastday)
or year(@today)=year(@lastday)-1 and month(@today)>month(@lastday) and
( month(birthday)=month(@today) and day(birthday)>=day(@today)
or month(birthday)>month(@today)
or month(birthday)<month(@lastday)
or month(birthday)=month(@lastday) and day(birthday)<=day(@lastday)
)
or year(@today)=year(@lastday)-1 and month(@today)=month(@lastday) and day(@today)>day(@lastday) and
month(birthday)=month(@today) and
( day(birthday)<=day(@today)
or day(birthday)>=day(@lastday)
)
or year(@today)=year(@lastday)-1 and month(@today)=month(@lastday) and day(@today)<=day(@lastday)
or year(@today)=year(@lastday)-1 and month(@today)<month(@lastday)
or year(@today)<year(@lastday)-1
But it is so long that I got bored while writing it, so I tried to make a shorter version starting from the Antares' version:
SELECT * FROM MyTest WHERE dateadd(yy,datediff(yy,birthday,@today)+
CASE WHEN dateadd(yy,datediff(yy,birthday,@today),birthday)<@today THEN 1 ELSE 0 END
,birthday) BETWEEN @today AND @lastday
That's 189 characters and could be even shorter if we rename the variables.
Razvan
-
quote:
Here's Another:select person, birthday from mytest
where
datepart(dy, birthday)<=(datepart(dy, dateadd(mm,@months,@today)))
and
((datepart(dy, birthday)>= datepart(dy, @today))
or
datepart(dy, birthday)> (datepart(dy, dateadd(mm,@months,@today))))
-
Hello Steve,
here is a quick take at your interesting problem using the 'dayofyear'. I have put all variables explicit, but have not had much chance to check thoroughly. The example can be run against the Northwind database. Thank you, you made my lunch break interesting.
Per E
-----
declare @range as int
declare @today as int
declare @yearend as int
declare @yearendday as smalldatetime
declare @newyearrange as int
declare @thisyearrange as int
select @range = 90
select @today = datepart(dy,getdate())
select @yearendday = cast('31.12.'+cast(year(getdate())as char(4))+'''' as char(10))
select @yearend = datepart(dy,@yearendday)
select @newyearrange = case when @today + @range > @yearend then @today + @range - @yearend else 0 end
select @thisyearrange = case when @today + @range > @yearend then @yearend - @today else 0 end
set dateformat dmy
select employeeid, birthdate
,datepart(dy,birthdate) as datepartbirthdate
,@today as today
,@yearendday as yearendday
,@yearend as yearend
,@newyearrange as newyearrange
,@thisyearrange as thisyearrange
from employees
where datepart(dy,birthdate) between
@today and
@today + @range
or
datepart(dy,birthdate) between
1 and
@newyearrange
-
Correction:
select person, birthday
from mytest
where
(datepart(dy, dateadd(mm,@months,@today)) <= datepart(dy, @today) and
(datepart(dy, birthday)>= datepart(dy, @today) or
datepart(dy, birthday)<=datepart(dy, dateadd(mm,@months,@today))))
or
(datepart(dy, dateadd(mm,@months,@today)) >= datepart(dy, @today) and
datepart(dy, birthday) >= datepart(dy, @today) and
datepart(dy, birthday)<= datepart(dy, dateadd(mm,@months,@today)))
-
I really enjoyed it, although I'm inclined to mention that some people out there who are ANSI SQL hardliners would now argue:
quote:
SQL wasn't developed for computation. You are also able to accomplish this task by a dedicated date table and a LEFT OUTER JOIN???
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
We have this view in our database
SELECT
staffID,
DOB,
CONVERT(datetime, '' + CAST(YEAR(GETDATE()) AS VARCHAR) + '-' + CAST(MONTH(DOB) AS VARCHAR) + '-' + CAST(DAY(DOB) AS VARCHAR) + '', 120) AS birthdayThisYear,
CASE WHEN (datediff(d, CONVERT(datetime, '' + CAST(YEAR(GETDATE()) AS VARCHAR) + '-' + CAST(MONTH(DOB) AS VARCHAR) + '-' + CAST(DAY(DOB) AS VARCHAR) + '', 120), GETDATE()) < 0) THEN 0 ELSE 1 END AS hadBirthday,
DATEADD(yyyy, CASE WHEN (datediff(d, CONVERT(datetime, '' + CAST(YEAR(GETDATE()) AS VARCHAR) + '-' + CAST(MONTH(DOB) AS VARCHAR) + '-' + CAST(DAY(DOB) AS VARCHAR) + '', 120), GETDATE()) < 0) THEN 0 ELSE 1 END, CONVERT(datetime, '' + CAST(YEAR(GETDATE()) AS VARCHAR) + '-' + CAST(MONTH(DOB) AS VARCHAR) + '-' + CAST(DAY(DOB) AS VARCHAR) + '', 120)) AS nextBirthday,
DATEDIFF(d, GETDATE(), DATEADD(yyyy, CASE WHEN (datediff(d, CONVERT(datetime, '' + CAST(YEAR(GETDATE()) AS VARCHAR) + '-' + CAST(MONTH(DOB) AS VARCHAR) + '-' + CAST(DAY(DOB) AS VARCHAR) + '', 120), GETDATE()) < 0) THEN 0 ELSE 1 END, CONVERT(datetime, '' + CAST(YEAR(GETDATE()) AS VARCHAR) + '-' + CAST(MONTH(DOB) AS VARCHAR) + '-' + CAST(DAY(DOB) AS VARCHAR) + '', 120))) AS daysTillNextBirthday,
DATEDIFF(yy, DOB, DATEADD(yyyy, CASE WHEN (datediff(d, CONVERT(datetime, '' + CAST(YEAR(GETDATE()) AS VARCHAR) + '-' + CAST(MONTH(DOB) AS VARCHAR) + '-' + CAST(DAY(DOB) AS VARCHAR) + '', 120), GETDATE()) < 0) THEN 0 ELSE 1 END, CONVERT(datetime, '' + CAST(YEAR(GETDATE()) AS VARCHAR) + '-' + CAST(MONTH(DOB) AS VARCHAR) + '-' + CAST(DAY(DOB) AS VARCHAR) + '', 120))) AS ageNextBirthday,
DATEDIFF(yy, DOB, DATEADD(yyyy, CASE WHEN (datediff(d, CONVERT(datetime, '' + CAST(YEAR(GETDATE()) AS VARCHAR) + '-' + CAST(MONTH(DOB) AS VARCHAR) + '-' + CAST(DAY(DOB) AS VARCHAR) + '', 120), GETDATE()) < 0) THEN 0 ELSE 1 END, CONVERT(datetime, '' + CAST(YEAR(GETDATE()) AS VARCHAR) + '-' + CAST(MONTH(DOB) AS VARCHAR) + '-' + CAST(DAY(DOB) AS VARCHAR) + '', 120))) - 1 AS ageNow
FROM staff
it is very handy for all sorts of questions.
-
Thanks for the notes and suggestions. Busy with PASS now, but I'll try to test later.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
-
Just to add one more
DECLARE @base_date DATETIME
DECLARE @end_date DATETIME
DECLARE @offSET int
SET @base_date='01.02.2004'
SET @end_date='31.12.2004'
SET @offSET=1
SELECT * FROM mytest
SELECT
person,
birthday,
DATEADD(yy,@offSET,CAST(CAST(day(birthday)AS CHAR(2))+'.'+CAST(month(birthday)AS CHAR(2))+'.'+CAST(year(getdate())AS CHAR(4))AS DATETIME)) AS [Next Birthday],
FLOOR(CAST(DATEADD(yy,@offSET,CAST(CAST(day(birthday)AS CHAR(2))+'.'+CAST(month(birthday)AS CHAR(2))+'.'+CAST(year(getdate())AS CHAR(4))AS DATETIME))-birthday AS INT)/365.2422) AS Age
FROM
mytest
WHERE
DATEADD(yy,@offSET,CAST(CAST(day(birthday)AS CHAR(2))+'.'+CAST(month(birthday)AS CHAR(2))+'.'+CAST(year(getdate())AS CHAR(4))AS DATETIME))
between
@base_date and @end_date
Adds sometimes 'vital' informations for the poor forgetful ones.
This is a simplified playaround from something I use for cash flow projections.
Frank
sorry, removed the [ code ] formatting
Edited by - Frank Kalis on 11/12/2003 04:07:17 AM
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I love this site!!! This article helped me pull the information I needed with no problems! Thanks Steve!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply