February 12, 2010 at 1:43 pm
Hello,
Most of this code, I didn't write. I got the bulk of it off the net from some SQL forum. It shows me the birthdays for the next 2 weeks.
The employees have not had a problem with this. However, one employee just showed me that she can't retrieve any records for 2/15/10.
If I am just getting the date and adding 14 days, why does it matter that February only has 28 days?
ALTER procedure spGetBirthdays
( @birthdateSTR as varchar(10),
@thisClubID AS integer,
@thisEmployeeid as integer)
as
declare @birthdate as datetime
set @birthdate = cast(@birthdatestr as datetime)
select b.*
from
(
SELECT guestid, cast(cast(month(birthdate) as varchar(2))+'/'+cast(day(birthdate) as varchar(2))+'/'+cast(year(@birthdate) as varchar(4)) as datetime) as BirthSearch,
BIRTHDATE AS BIRTHDAY
,FLOOR(DATEDIFF(dd,G.BiRTHDATE,@birthdate) / 365.25) AS AGE_NOW
,FLOOR(DATEDIFF(dd,G.BiRTHDATE,@birthdate+14) / 365.25) AS AGE_ONE_WEEK_FROM_NOW
FROM
tblGuest g
WHERE 1 = (FLOOR(DATEDIFF(dd,g.BiRTHDATE,@birthdate+14) / 365.25))
-
(FLOOR(DATEDIFF(dd,g.BiRTHDATE,@birthdate) / 365.25))
) a
inner join tblguest b
on a.guestid = b.guestid
where
b.employeeid = @thisemployeeid
and
b.clubid = @thisClubid
order by a.birthsearch
Not sure if this will help, but here is the script for the table:
CREATE TABLE [dbo].[tblGuest] (
[GuestID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BookDate] [datetime] NULL ,
[Telephone] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmailAddress] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AltPhone] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Birthdate] [datetime] NULL ,
[Address] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GuestMemo] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmployeeID] [int] NULL ,
[valid] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[clubID] [int] NULL ,
[inviteid] [int] NULL ,
[City] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Zip] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fax] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TransDate] [datetime] NULL ,
[KeyEmployeeID] [int] NULL ,
[TransUser] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Thanks for any and all help!
Tony
Things will work out. Get back up, change some parameters and recode.
February 12, 2010 at 5:01 pm
Could you supply some sample data, using the format described in the article in my signature block .... that is
INSERT INTO .......................
SELECT ' '. ''' ,.... UNION ALL
With that you are more likely to get tested help. Where someone wanting to assist you does not have to make up the test data.
Of course the names/dates etc in the test data should NOT be real values ... do not want you to post anything about personnel that could be used for example for identity theft.
February 13, 2010 at 1:31 am
I'll echo BitBucket's advice to post formatted test data with expected results.
But meanwhile, it seems likely to me that the approach of trying to calculate the age as of now and the age two weeks hence and comparing them to see if the person is a year older, is just over the top complicated for what you're doing. Add that to the less than fully accurate divide days lived by 365.25 for an age calculation, and I would expect inaccurate resullts.
Seems that this algorithm would return only a portion of the target birthday celebrants. If you run your formula with an input parm @birthdateSTR of 2/01/2010, it would calculate the age of someone born 2/15/1965 as 44.960985 years on 2/1/2010 and 44.999315 years on 2/15/2010. Someone born ten years later on 2/15/1975 would be calcuated as 34.962354 years old on 2/1 and 35.000684 years old on 2/15. So, with the FLOOR function truncating the decimals, the 45 year-old looks to be 44 both today and two weeks later.
(While we're at it, why do you label a date +14 as "ONE week from now"?)
So..... you'll want to re-work both ends of this. Identifying upcoming birthdays should be as simple as looking for
right(convert(char(6,tblguest.birthdate,12),4)
between right(convert(char(6,@birthdate,12),4)
and right(convert(char(6,@birthdate+14,12),4)caveat: that's not tested code.
Calculating an accurate age is another story. It's getting late here, so I'll let you look for a thread of discussion that appeared a few months ago here on SSC about how to do that. If you can't find it, say so and I or someone should be able to dredge it up.
February 13, 2010 at 8:40 am
WebTechie38
Now without any data supplied by you, I searched the SSC site and found this very interesting article and sample code:
When's Your Anniversary
By Steve Jones, 2003/11/11
http://www.sqlservercentral.com/articles/Advanced+Querying/anniversary/496/]
True it returns birthday in the coming MONTH not in the 2 week time period you asked for, but I believe it will lead you to the T-SQL code that you require, albeit with a few modifications or additions by yourself.
I suggest you give it a try.
February 13, 2010 at 11:43 am
Good find, Bit. The link has an extra close-bracket. Here it is without...
http://www.sqlservercentral.com/articles/Advanced+Querying/anniversary/496/
As I posted last night, I do recall a more recent discussion thread on this as well -- may have been a QOD.
February 13, 2010 at 12:03 pm
John Many thanks for the correction .. next time I will pay more attention to this line in my signature block:
If everything seems to be going well, you have obviously overlooked something.
I liked Steve's article with all the discussion prior to the finally solution, much like a professor teaching the "newbies"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply