Hi,
I'm trying to create a query that will tell me when someone's birth day is
approaching from my employee table. I tried Case When
Datediff(dd,b_day,getdate()) < 4 Then 'Remind' Else '' End As B_dayCk
Datepart(dd,b_day) - Datepart(dd,b_day) < 4 (This checks for the day but not
for the month). If I And or Or with Datepart(mm,b_day) - Datepart(mm,b_day)
< 0 doesn't work either.
I'm sure there's a simple way out of this. Can someone help pls.
TIA
MikeThe problem might be with you storing "birthday" and not "date of birth".
This code takes the date of birth, uses the current year an calculates the
difference between now and the birthday for this year:
declare
@.dob datetime
set @.dob = '1960-04-20'
select
datediff (dd, getdate(), str (year (getdate()), 4) +
right (convert (char (8), @.dob, 112), 4))
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Mike_B" <nospam@.yahoo.com> wrote in message
news:ervuHLZYGHA.3848@.TK2MSFTNGP05.phx.gbl...
Hi,
I'm trying to create a query that will tell me when someone's birth day is
approaching from my employee table. I tried Case When
Datediff(dd,b_day,getdate()) < 4 Then 'Remind' Else '' End As B_dayCk
Datepart(dd,b_day) - Datepart(dd,b_day) < 4 (This checks for the day but not
for the month). If I And or Or with Datepart(mm,b_day) - Datepart(mm,b_day)
< 0 doesn't work either.
I'm sure there's a simple way out of this. Can someone help pls.
TIA
Mike|||Thank you for your quick response, Tom.
I tried the following query:
Select
Case When Datediff (dd, Getdate(), str (year (Getdate()), 4) +
Right (convert (char (8), b_day, 112), 4)) < 4 then dbo.employees
.employee_name Else '' End As TEST
From dbo.employees
And as a result I get:
1975-04-19 Bob
1934-03-03 Mary
1956-03-26 David
1958-04-06 Sonia
I expected Bob to be the only results since he has an upcoming bday which is
within 4 days.
Mike
"Tom Moreau" <tom@.dont.spam.me.cips.ca> escribi en el mensaje
news:u%23wOOSZYGHA.3868@.TK2MSFTNGP04.phx.gbl...
> The problem might be with you storing "birthday" and not "date of birth".
> This code takes the date of birth, uses the current year an calculates the
> difference between now and the birthday for this year:
> declare
> @.dob datetime
> set @.dob = '1960-04-20'
> select
> datediff (dd, getdate(), str (year (getdate()), 4) +
> right (convert (char (8), @.dob, 112), 4))
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Mike_B" <nospam@.yahoo.com> wrote in message
> news:ervuHLZYGHA.3848@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I'm trying to create a query that will tell me when someone's birth day is
> approaching from my employee table. I tried Case When
> Datediff(dd,b_day,getdate()) < 4 Then 'Remind' Else '' End As B_dayCk
> Datepart(dd,b_day) - Datepart(dd,b_day) < 4 (This checks for the day but
> not
> for the month). If I And or Or with Datepart(mm,b_day) -
> Datepart(mm,b_day)
> < 0 doesn't work either.
> I'm sure there's a simple way out of this. Can someone help pls.
> TIA
> Mike
>|||Try:
declare @.t table
(
Employee varchar (20) primary key
, DOB datetime not null
)
insert @.t (DOB, Employee) values ('1975-04-19', 'Bob')
insert @.t (DOB, Employee) values ('1934-03-03', 'Mary')
insert @.t (DOB, Employee) values ('1956-03-26', 'David')
insert @.t (DOB, Employee) values ('1958-04-06', 'Sonia')
select
Employee
from
@.t
where
datediff (dd, getdate(), str (year (getdate()), 4) +
right (convert (char (8), DOB, 112), 4)) between 0 and 4
Part of the problem is the filtering. Some of these people have birthdays
that have already passed.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Mike_B" <nospam@.yahoo.com> wrote in message
news:uj%23yBjZYGHA.4916@.TK2MSFTNGP04.phx.gbl...
Thank you for your quick response, Tom.
I tried the following query:
Select
Case When Datediff (dd, Getdate(), str (year (Getdate()), 4) +
Right (convert (char (8), b_day, 112), 4)) < 4 then dbo.employees
.employee_name Else '' End As TEST
From dbo.employees
And as a result I get:
1975-04-19 Bob
1934-03-03 Mary
1956-03-26 David
1958-04-06 Sonia
I expected Bob to be the only results since he has an upcoming bday which is
within 4 days.
Mike
"Tom Moreau" <tom@.dont.spam.me.cips.ca> escribi en el mensaje
news:u%23wOOSZYGHA.3868@.TK2MSFTNGP04.phx.gbl...
> The problem might be with you storing "birthday" and not "date of birth".
> This code takes the date of birth, uses the current year an calculates the
> difference between now and the birthday for this year:
> declare
> @.dob datetime
> set @.dob = '1960-04-20'
> select
> datediff (dd, getdate(), str (year (getdate()), 4) +
> right (convert (char (8), @.dob, 112), 4))
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Mike_B" <nospam@.yahoo.com> wrote in message
> news:ervuHLZYGHA.3848@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I'm trying to create a query that will tell me when someone's birth day is
> approaching from my employee table. I tried Case When
> Datediff(dd,b_day,getdate()) < 4 Then 'Remind' Else '' End As B_dayCk
> Datepart(dd,b_day) - Datepart(dd,b_day) < 4 (This checks for the day but
> not
> for the month). If I And or Or with Datepart(mm,b_day) -
> Datepart(mm,b_day)
> < 0 doesn't work either.
> I'm sure there's a simple way out of this. Can someone help pls.
> TIA
> Mike
>|||That's it. Thank you much.
Mike.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> escribi en el mensaje
news:O$0JFtZYGHA.128@.TK2MSFTNGP05.phx.gbl...
> Try:
> declare @.t table
> (
> Employee varchar (20) primary key
> , DOB datetime not null
> )
> insert @.t (DOB, Employee) values ('1975-04-19', 'Bob')
> insert @.t (DOB, Employee) values ('1934-03-03', 'Mary')
> insert @.t (DOB, Employee) values ('1956-03-26', 'David')
> insert @.t (DOB, Employee) values ('1958-04-06', 'Sonia')
> select
> Employee
> from
> @.t
> where
> datediff (dd, getdate(), str (year (getdate()), 4) +
> right (convert (char (8), DOB, 112), 4)) between 0 and 4
> Part of the problem is the filtering. Some of these people have birthdays
> that have already passed.
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Mike_B" <nospam@.yahoo.com> wrote in message
> news:uj%23yBjZYGHA.4916@.TK2MSFTNGP04.phx.gbl...
> Thank you for your quick response, Tom.
> I tried the following query:
>
> Select
> Case When Datediff (dd, Getdate(), str (year (Getdate()), 4) +
> Right (convert (char (8), b_day, 112), 4)) < 4 then dbo.employees
> .employee_name Else '' End As TEST
> From dbo.employees
>
>
> And as a result I get:
>
> 1975-04-19 Bob
> 1934-03-03 Mary
> 1956-03-26 David
> 1958-04-06 Sonia
>
> I expected Bob to be the only results since he has an upcoming bday which
> is
> within 4 days.
> Mike
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> escribi en el mensaje
> news:u%23wOOSZYGHA.3868@.TK2MSFTNGP04.phx.gbl...
>|||Hey,
Just be aware that leap year birthdays will break this code. Run the
following and see (note that I used a variable to change the comparison
date):
DECLARE @.CompDate datetime
--SET @.CompDate = GETDATE()
SET @.CompDate = '20060225'
declare @.t table
(
Employee varchar (20) primary key
, DOB datetime not null
)
insert @.t (DOB, Employee) values ('1975-04-19', 'Bob')
insert @.t (DOB, Employee) values ('1934-03-03', 'Mary')
insert @.t (DOB, Employee) values ('1956-03-26', 'David')
insert @.t (DOB, Employee) values ('1958-04-06', 'Sonia')
insert @.t (DOB, Employee) values ('2000-02-29', 'Leap')
select
Employee
from
@.t
where
datediff (dd, @.CompDate, str (year (@.CompDate), 4) +
right (convert (char (8), DOB, 112), 4)) between 0 and 4
There must be a better way, but here's my quick patch for Tom's code:
select
Employee
from
@.t
where
datediff (dd, @.CompDate, str (year (@.CompDate), 4) +
REPLACE(right (convert (char (8), DOB, 112), 4), '0229', '0228'))
between 0 and 4
HTH,
Stu|||You already have a Calendar table, right? Of course you do; it is one
of the first things you put in a schema.
So add a column for "(n) business days in the future". This kind of
column is used to get due dates for business purposes, where n = {30,
60, 90, 120}; you just want to have (n=4) from your example.|||Joe,
I prefer to do it slightly differently: I have a business_day_number
column in my Calendar table. That done, 17 business days after 20060420
is selected as
select later.business_day
from Calendar c1 join Calendar later
on later.business_day_number = c1.business_day_number + 17
where c1.business_day = '20060420'
For a non business day, such as Saturday, business_day is set to next
work day, otherwise business_day coincides with the date.
Advantages: I don't need to add another column for each and every value
of n, and I have less maintenance when all of a sudden they say January
2 is a work day - I need to modify only 2 columns, business_day and
business_day_number.
Di
vantage: lookups are just a tad slower.
Makes sence?|||Good point Stu.
So you're replacing the leap year to a non leap year?
"Stu" <stuart.ainsworth@.gmail.com> escribi en el mensaje
news:1145230290.066560.260910@.j33g2000cwa.googlegroups.com...
> Hey,
> Just be aware that leap year birthdays will break this code. Run the
> following and see (note that I used a variable to change the comparison
> date):
> DECLARE @.CompDate datetime
> --SET @.CompDate = GETDATE()
> SET @.CompDate = '20060225'
> declare @.t table
> (
> Employee varchar (20) primary key
> , DOB datetime not null
> )
> insert @.t (DOB, Employee) values ('1975-04-19', 'Bob')
> insert @.t (DOB, Employee) values ('1934-03-03', 'Mary')
> insert @.t (DOB, Employee) values ('1956-03-26', 'David')
> insert @.t (DOB, Employee) values ('1958-04-06', 'Sonia')
> insert @.t (DOB, Employee) values ('2000-02-29', 'Leap')
>
> select
> Employee
> from
> @.t
> where
> datediff (dd, @.CompDate, str (year (@.CompDate), 4) +
> right (convert (char (8), DOB, 112), 4)) between 0 and 4
> There must be a better way, but here's my quick patch for Tom's code:
>
> select
> Employee
> from
> @.t
> where
> datediff (dd, @.CompDate, str (year (@.CompDate), 4) +
> REPLACE(right (convert (char (8), DOB, 112), 4), '0229', '0228'))
> between 0 and 4
> HTH,
> Stu
>|||Yeah, Tom's code would create a comparison value of '20060229', which
is a non-existant date; mine simply ignores all dates of '0229', making
them '0228' instead. Note that even in a leap year, the comparison
will still go against '0228' rather than the correct date.
However, the idea of a calendar table is probably the best way to go; I
especially like Alexander's join concept.
Stu
