Member Avatar for bnitishpai

Hey guys, I'm working on a job. I need sql statement for the following query: "List all Employees Names with equal ages." (If possible, along with their year of birth, or date of birth.)

  • 3 Contributors
  • 3 Replies
  • 259 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by bnitishpai

Dani AI

Generated

Asking whether "equal ages" means the same calendar birth year or the same numeric age today is the key missing detail in the thread (as hinted). was right to point to GROUP BY — two safe approaches are: group by YEAR(DOB) to collect everyone born in the same calendar year (1985 = 1-Jan-1985..31-Dec-1985), or group by a calculated age (age as of today or as of a specified snapshot). Examples below use a generic Employees(EmployeeID, EmployeeName, DOB) schema — adjust names to match the real table.

Group employees by birth year and list names (groups with more than one person):

WITH YearGroups AS (
  SELECT YEAR(DOB) AS BirthYear, COUNT(*) AS Cnt
  FROM Employees
  WHERE DOB IS NOT NULL
  GROUP BY YEAR(DOB)
  HAVING COUNT(*) > 1
)
SELECT e.EmployeeID, e.EmployeeName, e.DOB, yg.BirthYear
FROM Employees e
JOIN YearGroups yg ON YEAR(e.DOB) = yg.BirthYear
ORDER BY yg.BirthYear, e.EmployeeName;

Group employees by current age (accurate year count) and list names for ages that appear more than once:

WITH EmpAge AS (
  SELECT EmployeeID, EmployeeName, DOB,
    (DATEDIFF(year, DOB, GETDATE())
     - CASE WHEN DATEADD(year, DATEDIFF(year, DOB, GETDATE()), DOB) > GETDATE() THEN 1 ELSE 0 END) AS Age
  FROM Employees
  WHERE DOB IS NOT NULL
), AgeGroups AS (
  SELECT Age, COUNT(*) AS Cnt
  FROM EmpAge
  GROUP BY Age
  HAVING COUNT(*) > 1
)
SELECT e.EmployeeID, e.EmployeeName, e.DOB, e.Age
FROM EmpAge e
JOIN AgeGroups ag ON e.Age = ag.Age
ORDER BY e.Age DESC, e.EmployeeName;

Notes and cautions: YEAR(DOB) is fine for the OP's calendar-year grouping but is non‑sargable on large tables — prefer range predicates when filtering a specific year (e.g. WHERE DOB >= '19850101' AND DOB < '19860101') to use indexes. Replace GETDATE() with a fixed @AsOfDate if a reproducible snapshot is needed. Handle NULL DOBs and adapt column/table names to the real schema.

Recommended Answers

All 3 Replies

Member Avatar for ChrisHunter

Where does the age of the employee come from is it calculated using their DOB? use the GROUPBY clause to group the employees by age

Member Avatar for deceptikon

Equal ages to what? Do you want groups of employees where each group has the same age? Do you want all employees matching a specified age? You can't write a query with vague terms.

Member Avatar for bnitishpai

ChrisHunter: Ya, the age is derived from DOB. Suggest me a sql statement using Group By clause then.
Deceptikon: I want groups of employees where each group has the same age. Is it possible to sort employees according to their age? ; and not by their Date of Birth?
Suppose Employees born between 1-Jan-1985 to 31-Dec-1985 should come under one group.
And the very next set would be employees born between 1-Jan-1986 to 31-Dec-1986.
That way.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.