so it checks field 'class' and if it finds a 1 it adds 6 years, if it finds a 2 adds 3 years etc... and if it finds a null then it looks at another date field. however I am getting some errors...and I am not sure its the best way of writing this...
Would anyone be able to advise if there is a better way?
That said, nothing really jumps out. It looks like you have all your opening and closing parens accounted for and in the correct locations.
What are the errors that you're getting?
As far as a better way... You could move this logic to the SQL code which would allow you to use a CASE expression which would give it a MUCH cleaner syntax...
SELECT
ColumnName = CASE
WHEN x.CLASS = 1 THEN DATEADD(yy, 6, x.NDT_Test_Date)
WHEN x.CLASS = 2 THEN DATEADD(yy, 3, x.NDT_Test_Date)
WHEN x.CLASS = 3 THEN DATEADD(yy, 1, x.NDT_Test_Date)
WHEN x.CLASS IN (4, 5) THEN x.NDT_Test_Date
ELSE x.NDT_Intervention_Date_1
END