I really couldn't follow what your code was attempting to do so I did this from scratch. I'm not claiming it is the most elegant way of doing this but it works.
declare @USER_CURRENT_DATE DATETIME = getdate();
declare @CompareDate DATETIME ='2021/07/25 7:08:35 PM';
declare @res table ([id] int, [period] varchar(8), [amount] int);
INSERT INTO @res
SELECT 1, 'Year', datediff(YEAR, @compareDate, @USER_CURRENT_DATE)
UNION
SELECT 2, 'Month', datediff(MONTH, @compareDate, @USER_CURRENT_DATE)
UNION
SELECT 3, 'Week', cast(datediff(DAY, @compareDate, @USER_CURRENT_DATE) / 7 AS INT)
UNION
SELECT 4, 'Day', datediff(DAY, @compareDate, @USER_CURRENT_DATE)
UNION
SELECT 5, 'Hour', datediff(HOUR, @compareDate, @USER_CURRENT_DATE)
UNION
SELECT 6, 'Minute', datediff(MINUTE, @compareDate, @USER_CURRENT_DATE)
UNION
SELECT 7, 'Second', datediff(SECOND, @compareDate, @USER_CURRENT_DATE);
select top 1 cast([amount] as varchar(3)) + ' ' + case when [amount] > 1 then [period] + 's ago' else [period] + ' ago' end as ago
from @res
where [amount] > 0
order by id;
I brute-force calculated all the possible differences between the dates i.e. years, months, weeks, days, hours, minutes, seconds. Add to that as you wish.
NOTE: The calculation for weeks does not use datepart
WEEK
! This is because that date falls into "last week" rather my interpretation of a week ago being 7 to 13 days ago. If you want something different then change the calculation for [period] = 'Week'
Note 2: Do not use an identity column for
[id]
because you cannot control the order in which these items are inserted into the table variable.
The solution just finds the "first" non-zero entry in the order that I have explicitly set.
For testing I used a variety of
@CompareDate
values built using variations of this statement:
declare @CompareDate DATETIME =DATEADD(YEAR, -3, @USER_CURRENT_DATE)