Case in where clause

Transact-SQL
1

Is there a way to add the last line in the code?

declare @requestorid int = 44997,
@recordid int = 152810,
@minMax int = 0



SELECT a.dateentered, 
	   c.namekey,
	   b.initials
FROM   dbo.tbltrkrecordlog AS a 
	   INNER JOIN (SELECT Max(t.recordid)    AS recordid, 
						  t.customfieldid, 
						  case when @minMax =1 then max(t.dateentered) else min(t.dateentered) end changeDate,
						  max(SUBSTRING(e.firstname, 1, 1) + SUBSTRING(e.middleinitial, 1, 1) + SUBSTRING(e.lastname, 1, 1)) initials 
				   FROM   dbo.tbltrkrecordlog t
				   left join tblemployee e on e.id = t.employeeid
				   WHERE  ( t.recordid = @recordid ) 
				   GROUP  BY t.customfieldid) AS b 
			   ON a.customfieldid = b.customfieldid 
				  AND a.dateentered = b.changeDate 
	   INNER JOIN dbo.tbltrkcustomfield AS c 
			   ON a.customfieldid = c.id
where c.namekey = 'hprmprcorrectiveactionsneededtakennotes'
--and case when @minMax =1 then max(t.dateentered) else min(t.dateentered) end
2

No. But you don't need to add that, you can instead use column changeDate, which already contains that value from the inner query.

3

Sorry. Not sure what ya mean

4

It's not clear what you're trying to do with that "last line in the code".

If you're trying to add something to the WHERE clause, then (1) MIN/MAX can only be compared in a HAVING clause, not a WHERE clause and (2) there's no comparison in your CASE statement, only a single column. It'd be like saying "WHERE A" on a query.

However, since you have the same CASE statement in the "b" table definition, and the name "changeDate" was assigned there, if you wanted to you add:
"and b.changeDate ..." to the outer WHERE clause.

But it would be more efficient to add it as a HAVING clause in the inner query, since SQL would screen out rows more quickly.

5

No. I want to take it out of the select. It works for the date but not the name