My bad with the DATEFROMPARTS method, here is a correct version with the integer math
😎
,DATEDIFF(MONTH,@Birth,DATEFROMPARTS(YEAR(@Service),1,1)) / 12 AS DFPAgeAsOfJanFirst


Again and as always, you...

Just a word of warning, the XML methods posted on this thread so far are naive and ridiculously expensive, at least use the text() function to avoid the XML reconstruction...

Quick thought, if it is spilling to tempdb at times then why not simply remove the join hints and add "with recompile"?
😎

Further, have you looked into those...

Quick thought, change the WHERE clause and convert the COMMENT_DATE to a DATE, just as you do in the output.
😎

WHERE (CONVERT(DATE, q.COMMENT_DATE) >= DATEADD(DD, -1,...

You are very welcome Thomas, always happy to help.
😎

Here is another method which is equally simple
DATEDIFF(YEAR,@Birth,DATEFROMPARTS(YEAR(@Service),1,1))

Recommend that you set up a simple...

Isn't this simply a question of integer math?
😎

DECLARE @Birth DATETIME  = CONVERT(DATETIME,'19921130',112); 
DECLARE @service DATETIME = CONVERT(DATETIME,'20160826',112);

SELECT (DATEDIFF(MONTH,0,@Service) - DATEDIFF(MONTH,0,@Birth)) / 12 AS...

Quick suggestion towards a solution based on DelimitedSplit8K
😎

USE TEEST;
GO
SET NOCOUNT ON;

DECLARE @DETAILS VARCHAR(1000) = 'Item1: A100 ; Item2: B200; Item3:C300;...