December 2, 2009 at 6:11 pm
Hello,
I am having trouble getting my head around querying for birthdays in the next two weeks.
Table - Guests
Fields
Firstname varchar(30)
Lastname varchar(30)
Birthdate datetime
Now I thought of getting the date for today and then calculating the date two weeks from now.
declare @startDay, startMonth as integer
declare @endDay, endMonth as integer
@startDay = day(getdate())
@startMonth = Month(getdate())
@endDay = day(dateadd("wk",2,getdate()))
@endMonth = month(dateadd("wk",2,getdate()))
select Firstname, lastname
where day(birthdate) > = @startday and
day(birthdate) <= @endday and
month(birthdate) >=@startMonth and
month(birthdate) <= @endMonth
This works well for sequential months. However, if the date is December 25, then the above query doesn't work.
Admittedly, I may be approaching this from the wrong angle.
Does anyone have a better way to birthdays coming up for the next two weeks?
The birthdate field has the actual birthdate of the guest (month, day and year).
Thanks.
Things will work out. Get back up, change some parameters and recode.
December 3, 2009 at 12:45 am
try this:
-- creating testdata
if object_id('tempdb..#datetab') is not null
drop table #datetab
create table #datetab(
datecol datetime
)
insert into #datetab (datecol) values ('20091203')
insert into #datetab (datecol) values ('20091203')
insert into #datetab (datecol) values ('20091225')
insert into #datetab (datecol) values ('20100525')
insert into #datetab (datecol) values ('20091210')
DECLARE @today datetime
SET @today = convert(varchar(50), getdate(), 112)
-- the query using dateadd function
SELECT *
FROM #datetab
where datecol >= @today
and datecol <= dateadd(day, 14, @today)
December 3, 2009 at 3:57 am
Hi, this should give you what you need.
It looks at the birthdate column, doesn't care about the year as its not age we're interested in, assigns the current year to the month and day elements, then converts back to datetime for the comparison.
CREATE TABLE #Guests(
Firstname varchar(30),
Lastname varchar(30),
Birthdate datetime
)
INSERT INTO #Guests (Firstname,Lastname,Birthdate) VALUES ('Jim','Jones','20051203')--Birthday in next 2wks
INSERT INTO #Guests (Firstname,Lastname,Birthdate) VALUES ('Joe','Bloggs','20011210')--Birthday in next 2wks
INSERT INTO #Guests (Firstname,Lastname,Birthdate) VALUES ('Dave','Thomas','20001212')--Birthday in next 2wks
INSERT INTO #Guests (Firstname,Lastname,Birthdate) VALUES ('Pete','Jones','19851218')-- No birthday in next 2wks
INSERT INTO #Guests (Firstname,Lastname,Birthdate) VALUES ('Mike','Evans','19911225')-- No birthday in next 2wks
INSERT INTO #Guests (Firstname,Lastname,Birthdate) VALUES ('Sally','Evans','19980115')-- No birthday in next 2wks
INSERT INTO #Guests (Firstname,Lastname,Birthdate) VALUES ('Mary','Jones','20001217')--Birthday in next 2wks
INSERT INTO #Guests (Firstname,Lastname,Birthdate) VALUES ('Wendy','Smith','20021225')-- No birthday in next 2wks
DECLARE @today datetime
SET @today = convert(varchar(50), getdate(), 112)
SELECT
A.Firstname,
A.Lastname,
A.Birthday
FROM (
SELECT
Firstname,
Lastname,
CONVERT(DATETIME,
(CAST(DATEPART(dd,Birthdate) as char(2)) + '/' +
CAST(DATEPART(mm,Birthdate) as char(2)) + '/' +
CAST(DATEPART(yy,@today) as char(4)))
,103) AS Birthday
FROM #Guests
) A
WHERE A.Birthday >= @today
AND A.Birthday <= DATEADD(dd, 14, @today)
December 3, 2009 at 5:10 am
DROP TABLE #Guests
CREATE TABLE #Guests(
Firstname varchar(30),
Lastname varchar(30),
Birthdate datetime
)
INSERT INTO #Guests (Firstname,Lastname,Birthdate) VALUES ('Jim','Jones','19611203')--Birthday in next 2wks
INSERT INTO #Guests (Firstname,Lastname,Birthdate) VALUES ('Joe','Bloggs','19621210')--Birthday in next 2wks
INSERT INTO #Guests (Firstname,Lastname,Birthdate) VALUES ('Dave','Thomas','19631212')--Birthday in next 2wks
INSERT INTO #Guests (Firstname,Lastname,Birthdate) VALUES ('Pete','Jones','19641218')-- No birthday in next 2wks
INSERT INTO #Guests (Firstname,Lastname,Birthdate) VALUES ('Mike','Evans','19651225')-- No birthday in next 2wks
INSERT INTO #Guests (Firstname,Lastname,Birthdate) VALUES ('Sally','Evans','19660115')-- No birthday in next 2wks
INSERT INTO #Guests (Firstname,Lastname,Birthdate) VALUES ('Mary','Jones','19671217')--Birthday in next 2wks
INSERT INTO #Guests (Firstname,Lastname,Birthdate) VALUES ('Wendy','Smith','19680125')-- No birthday in next 2wks
DECLARE @today DATETIME
SET @today = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
SELECT *
FROM (
SELECT d.Firstname,
d.Lastname,
d.Birthdate,
NextBirthday = CASE WHEN d.NextBirthday < @today THEN DATEADD(yy, 1, d.NextBirthday) ELSE d.NextBirthday END
FROM (SELECT *, NextBirthday = DATEADD(yy, YEAR(@today)-YEAR(Birthdate), Birthdate)
FROM #Guests) d
) x
WHERE DATEDIFF(dd, @today, x.NextBirthday) < 15
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 3, 2009 at 5:42 pm
Thanks Guys for the help.
I didn't realize how complex the query would be figuring upcoming birthdays.
I found this late night and used it. It worked great.
Thanks again.
SELECT
BIRTHDATE AS BIRTHDAY
,FLOOR(DATEDIFF(dd,g.BIRTHDATE,GETDATE()) / 365.25) AS AGE_NOW
,FLOOR(DATEDIFF(dd,G.BIRTHDATE,GETDATE()+14) / 365.25) AS AGE_ONE_WEEK_FROM_NOW
FROM
TBLGUEST G
WHERE 1 = (FLOOR(DATEDIFF(dd,G.BIRTHDATE,GETDATE()+14) / 365.25))
-
(FLOOR(DATEDIFF(dd,G.BIRTHDATE,GETDATE()) / 365.25))
Things will work out. Get back up, change some parameters and recode.
February 14, 2010 at 9:38 am
did you try using a tally table ?
Check out the articles of Jeff Moden or Lynn Pettis
set nocount on;
declare @myTest table
( person varchar(80)
, birthday datetime
)
insert @myTest
select 'Steve'
, '09/15/1967'
insert @myTest
select 'Tia'
, '02/01/1969'
insert @myTest
select 'Kendall'
, '05/15/2001'
insert @myTest
select 'Delaney'
, '11/18/1998'
insert @myTest
select 'Kyle'
, '06/01/1992'
DECLARE @base_date DATETIME
DECLARE @end_date DATETIME
SET @base_date = '2010-02-01'
SET @end_date = '2010-12-31'
SELECT B.*
, @base_date
, dateadd(yy, Tally.N * (-1), birthday) as ThisYearDate
, Tally.N * (-1)
FROM @myTest B
inner join dbo.ufn_Tally(-120,0) Tally
on B.birthday between dateadd(yy, Tally.N, @base_date)
and dateadd(yy, Tally.N, @end_date)
order by ThisYearDate, B.birthday ;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 15, 2010 at 9:15 am
This could help you.
DECLARE@nDate1int,
@nDate2int,
@dTodaydatetime
SET@dToday = GETDATE()
SET@nDate1 = DATEPART( y, @dToday)
SET@nDate2 = DATEPART( y, DATEADD( ww, 2, @dToday))
IF @nDate2 < @nDate1
BEGIN
SET @nDate2 = DATEPART( y, CAST( YEAR( @dToday) AS char(4)) + '1231') + @nDate2
END
SELECTFirstname, lastname
FROMGuests
WHEREDATEPART( y, Birthdate) BETWEEN @nDate1 AND @nDate2
February 15, 2010 at 9:41 am
Sorry about the late reply but this might form the basis for a birthday check.
SELECT *, AS DaysIntoYear FROM Employees
WHERE ((DATEPART("dy",Birthdate)-DATEPART("dy",GETDATE())) > 0
AND (DATEPART("dy",Birthdate)-DATEPART("dy",GETDATE())) < 15);
Apologies if the syntax is incorrect, but I'm in a bit of a hurry.
Rationale: - all you really need to know is the day number of the birthdate within the birthyear.
Date to calculate from (current date in the above) and the number of days lookahead could all be parameterised.
HTH
Cheers.
February 17, 2010 at 4:02 pm
Thanks everyone.
I appreciate your help and I got the query working now. Here is the code I got to work:
ALTER procedure spGetBirthdays
( @birthdateSTR as varchar(10),
@thisClubID AS integer,
@thisEmployeeid as integer)
as
declare @birthdate as datetime
declare @birthmonth as integer
declare @birthDays as integer
declare @FebMonth as datetime
Declare @YearStr as varchar(4)
Declare @FebStr as varchar(10)
set @birthdate = cast(@birthdatestr as datetime)
select @birthmonth = month(@birthdate)
select @YearStr = cast('2010' as varchar(4))
select @FebStr = cast('2/28' as varchar(4)) + '/' + @Yearstr
if(@birthmonth=2)
BEGIN
select @FebMonth = cast(@Febstr as datetime)
select @BIRTHDAYS = DATEDIFF("dd",@birthdate, @FebMonth )
END
ELSE
BEGIN
select @BIRTHDAYS = 14
END
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,dateadd("dd", @birthdays,@birthdate)) / 365.25) AS AGE_ONE_WEEK_FROM_NOW
FROM
tblGuest g
WHERE 1 = (FLOOR(DATEDIFF(dd,g.BiRTHDATE,dateadd("dd", @birthdays,@birthdate)) / 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
I'll change it later to dynamically give me the year rather using "2010".
Thanks again!
Tony
Things will work out. Get back up, change some parameters and recode.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply