October 21, 2025 at 8:21 pm
hello everybody, new here and this is my first question
I have a table contains: Name, Id, joinDate
My question is that:
I need a query to list all years between current date and joinDate, as the following as instance:
Id------------------------Name----------------------empDate
1--------------------------John-----------------------6-8-2020
result:
1--------------------------John-----------------------6-8-2021
1--------------------------John-----------------------6-8-2022
1--------------------------John-----------------------6-8-2023
1--------------------------John-----------------------6-8-2024
1--------------------------John-----------------------6-8-2025
Thanks in advance.
October 22, 2025 at 1:41 am
Since you're new here... here's an article you owe it to yourself to read (it'll take like 2 minutes!)
Forum Etiquette: How to post data/code on a forum to get the best help
That said, here's how I answered it... (someone will likely point out the errors in my ways, I'm sure).
Setup:
use tempdb;
go
CREATE TABLE Employee (
EmployeeID INT IDENTITY PRIMARY KEY,
EmployeeName VARCHAR(10) NOT NULL,
HireDate DATE NOT NULL,
TermDate DATE DEFAULT NULL
);
GO
INSERT INTO Employee (EmployeeName, HireDate, TermDate)
VALUES ('John', '6-8-2020', '5-1-2024'),
('Fred', '4-5-2021', null);
CREATE TABLE YearList(Yr INT PRIMARY KEY);
GO
INSERT INTO YearList VALUES (2020),(2021),(2022),(2023),(2024),(2025);
Then you can basically create a join on the years...
WITH cteEmployee
AS (
SELECT
EmployeeName,
HireDate,
YEAR(HireDate) AS HireYear,
TermDate,
COALESCE(YEAR(TermDate),YEAR(GETDATE())) AS LeaveYear
FROM Employee
)
SELECT e.EmployeeName, e.HireYear, e.LeaveYear, yl.Yr
FROM cteEmployee e
INNER JOIN YearList yl
ON yl.Yr >= e.HireYear AND yl.Yr <= e.LeaveYear
Sample result:
EmployeeName HireYear LeaveYear Yr
John 2020 2024 2020
John 2020 2024 2021
John 2020 2024 2022
John 2020 2024 2023
John 2020 2024 2024
Fred 2021 2025 2021
Fred 2021 2025 2022
Fred 2021 2025 2023
Fred 2021 2025 2024
Fred 2021 2025 2025
Thanks for the data! Makes it a LOT easier to build a tested solution.
Welcome aboard!
October 22, 2025 at 1:45 am
This is a solution using a numbers or tally table. It uses an inline tally table created with a few nested common table expressions. I did not make up this technique and this instance I have copied it from the legendary Jeff Moden. My recollection is that common table expressions came out with SQL 2005, so assuming you title is correct it should still work. I think cross apply also came with sql 2005, but it could be a subquery if needed.
I used a variable @Today instead of GETDATE() to illustrate a mistake I almost fell for. We have an employee starting on 2024-12-31, all employees have a zero years, but with @Today set t0 '2025-01-01' which is 1 day later than the hire date, it still returns a datediff of 1 as it is the next year. I had to add "AND DATEADD(YEAR,c.N, [EmpDate]) <= @Today" to stop the new employee appearing with two rows and a 1 year date of 2025-12-31.
There's probably a cleaner way that excludes partial years up front rather than as an afterthought.
DROP TABLE IF EXISTS dbo.Employee
GO
CREATE TABLE dbo.Employee (ID INT NOT NULL IDENTITY(1,1), [Name] VARCHAR(100), EmpDate DATE NOT NULL)
INSERT dbo.Employee ([Name], EmpDate)
VALUES ('Smelly Pete', '2019-05-21'),
('Grumpy George', '2025-01-01'),
('Handsome Jeff', '2000-05-04'),
('Better-than-you Betty', '2020-09-11'),
('Presuming Ed', '01/30/2012'),
('Newbie Noname', '2024-12-31');
GO
DECLARE @Today DATE = '2025-01-01';
-- Not invented by me, copied from Handsome Jeff
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 100
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
cteTally(N) AS (SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E2
)
SELECT a.id, a.[Name], a.[EmpDate], c.N AS Anniversary, DATEADD(YEAR,c.N, [EmpDate]) AS [EmpDate]
FROM dbo.Employee AS a
CROSS APPLY (VALUES(DATEDIFF(YEAR,a.[EmpDate], @Today))) AS b (YearBoundaries)
CROSS JOIN cteTally AS c
WHERE c.N <= b.YearBoundaries
AND DATEADD(YEAR,c.N, [EmpDate]) <= @Today
ORDER BY a.[name], c.N
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply