How to choose one day of name from first name and date of birth?
Hey there. I would really appreciate your help in creating a query that will accomplish the following. The problem is the dynamic promotion of what we call "names" in Europe. Nameday is a tradition in many countries in Europe and Latin America, celebrating on a specific day of the year associated with one name ( http://en.wikipedia.org/wiki/Name_day ).
I have a table of all names and their corresponding date (I am storing the date in this format "1900-08-22", but we really need the month and day)
namede table:
name date
----------------
Bob 1900-04-22
Bob 1900-09-04
Frank 1900-01-02
...
The trick is that there can be multiple entries for each name, and someones "nameday" is always found first after one birthday. So let's say Bob # 1 was born on August 5, his name will fall on September 4, but if we are born after September 4, his name will be April 22.
Obviously I have a table with my users
user :
id first_name birth_date
------------------------------------
1 Bob 1975-08-05
2 Frank 1987-01-01
...
So, whatever is based on date of birth, we need to find when a given person is celebrating their "name" based on the first name and date of birth. When I need it, it's a SQL Server query that can get me names for people in my database ;;
If you think it would be easier to split the dates in the "nameday" table by: month and day, let me know, we can do that. I need to be able to find the most efficient solution for this.
Any help is greatly appreciated,
Wojo
I would recommend that you maintain your dates 1904
as 1900
it was not a leap year and did not have Feb 29th
.
However, here's a request with your data:
WITH users AS
(
SELECT 1 AS id, 'Bob' AS first_name, CAST('1975-08-05' AS DATETIME) AS birth_date
UNION ALL
SELECT 2 AS id, 'Frank' AS first_name, CAST('1987-01-01' AS DATETIME) AS birth_date
),
namedays AS
(
SELECT 'Bob' AS name, CAST('1900-04-22' AS DATETIME) AS date
UNION ALL
SELECT 'Bob' AS name, CAST('1900-09-04' AS DATETIME) AS date
UNION ALL
SELECT 'Frank' AS name, CAST('1900-01-02' AS DATETIME) AS date
)
SELECT *
FROM users u
OUTER APPLY
(
SELECT COALESCE(
(
SELECT TOP 1 date
FROM namedays nd
WHERE nd.name = u.first_name
AND nd.date >= DATEADD(year, 1900 - YEAR(u.birth_date), birth_date)
ORDER BY
nd.date
),
(
SELECT TOP 1 date
FROM namedays nd
WHERE nd.name = u.first_name
ORDER BY
nd.date
)
) AS date
) dates
Check out this blog post for details on performance:
source to share
Here you go:
WITH "nameday_long" ("name", "date", "p_month_day") AS (
SELECT n."name",
n."date",
DATEPART(month, n."date") * 100 + DATEPART(day, n."date")
FROM "nameday" n
UNION ALL
SELECT n."name",
n."date",
DATEPART(month, n."date") * 100 + DATEPART(day, n."date") + 10000
FROM "nameday" n
)
SELECT u."id",
u."first_name",
u."birth_date",
n."date" AS nameday
FROM "user" u
LEFT JOIN "nameday_long" n
ON u."first_name" = n."name"
AND n."p_month_day" = (SELECT MIN(x."p_month_day") FROM "nameday_long" x WHERE x."p_month_day" > DATEPART(month, u."birth_date") * 100 + DATEPART(day, u."birth_date"))
Trick:
- convert dates to a format that has no years and is easy to compare. I chose an integer that would be similar to representing MMDD in daytime parts of the day.
- I would even suggest adding a (constant) computed column to the "nameday" table using this formula, but it shouldn't be heavy on computation anyway
- extend these names by 2 years so that it is easy to find the first one if the person's name is before his birth (according to the calendar). In this trick, I have added 1000 to the MMDD numeric representation to keep it natural
- for each person, the first name that comes after his / her birthday is chosen.
- use the LEFT JOIN so people with cool names like "Wojo" will still show up in the results list even if they don't have a name, right? :)
source to share
This seems like a simple solution:
select u.id, u.first_name, min(isnull(n.date, n2.date)) as nameday
from users u
left join namedays n on u.first_name = n.name and dateadd(year, year(u.birth_date) - 1900, n.date) > u.birth_date
left join namedays n2 on u.first_name = n2.name and dateadd(year, year(u.birth_date) - 1899, n2.date) > u.birth_date
group by u.id, u.first_name
Explanation: Join the table with a users
table namedays
(adjust the column namedays.date
in the same year as the birthday). If the date is set after the birthday, that date is used.
If not, the table users
joins the table again namedays
, this time adjusting the column date
for the year after their birthday and using the minimum date that is after their birthday.
This solution returns the correct dates as per your example data and the situations listed in the problem (Bob's birthday changed after 9/4 when Bob called 4/22).
source to share
WITH namedays AS (
SELECT UPPER(nd.name),
nd.date,
MONTH(nd.date) 'month',
DAY(nd.date) 'day'
FROM NAMEDAYS nd)
birthdays AS (
SELECT u.id,
UPPER(u.first_name) 'first_name',
MONTH(u.birth_date) 'month',
DAY(u.birth_date) 'day'
FROM USERS u)
SELECT t.id,
t.first_name,
MIN(nd.date) 'name_day'
FROM USERS t
JOIN birthdays bd ON bd.id = t.id
JOIN namedays nd ON nd.month >= bd.month AND nd.day >= bd.day AND nd.name = bd.firstname
GROUP BY t.id, t.first_name
source to share
DatePart (DayOfYear) is a great way to compare dates. And who knew I would ever learn something culturally new from SO? :-)
declare @nameday table
(
Name varchar(30),
Date smalldatetime
)
declare @user table
(
UserName varchar(30),
Birthday smalldatetime
)
insert into @nameday
select 'Bob', '1900-04-22' union
select 'Bob', '1900-09-04' union
select 'Frank', '1900-01-02'
insert into @user
select 'Bob', '1975-08-05' union
select 'Frank', '1987-01-01'
select UserName,
NameDayDate = Date
from @user as u
inner join @nameday as nd
on nd.Name = u.UserName
and datepart(dayofyear, nd.Date) >= datepart(dayofyear, u.Birthday)
and not exists (select *
from @nameday as ndOlder
where ndOlder.Name = nd.Name
and datepart(dayofyear, ndOlder.Date) >= datepart(dayofyear, u.Birthday)
and ndOlder.Date < nd.Date)
source to share