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
October 22, 2025 at 7:17 am
To repeat query results without using loops in SQL Server 2005 Express, you can use a number-generating technique via CROSS JOIN or a derived table to simulate repetition.Since SQL Server 2005 doesn't have modern functions like SEQUENCE or ROW_NUMBER() (well, ROW_NUMBER() exists, but it's limited), here's a way to repeat rows N times without using loops or cursors.
October 22, 2025 at 9:46 am
Well guys, thanks for the quick reply,, I've tested your codes and all works fine
I have a little more questions, I'll post it here, if you wouldn't mind.
October 23, 2025 at 11:52 pm
Generally speaking, if you can provide a clear question, some sample data (create table and insert scripts), and an expected output, you can ask all kinds of questions and get tested answers. I have even gotten most of the way through posting a question and then realized my mistake in the middle of posting (see my question on course requirement hierarchies/levels).
If you follow Jeff's article, it's entirely possible that you can answer your own question - at least some of the time.
(SQL 2005?? really?? can you even run that on 64 bit?)
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply