There are other methods, with less use of date functions. If uahmed90 provides CREATE TABLE statement and some reasonable test records, we could try another approach.

Good idea would be to leave "the lookup" to the SQL server, then pass the results to C# code. Something like:

SELECT ID, NAme FROM DirectoryTable 
WHERE ID...

Ravi77, you may bi in bigger trouble than it seems.

In any case, you need to figure out what makes each table unique, does not have to be just one column....

Function coalesce() should also do the job. In this example, I assumed that order of using dates for sorting is LastReplied, IncidentDate, CreatedDate. I also reordered columns to my liking...

• This reply was modified 3 years, 2 months ago by  Zidar.

Not directly about coalesce, but since we are also talking about ways to concatenate rows, worth checking is a relatively recent addition to T-SQL functions is string_agg (expression, separator) . ...

ROW_NUMBER in DESCending order, cool trick. Thank you SoCal_DBD. In spite being a newbie, I knew about this one, but hesitated to apply it  due to possible slow execution. Live...

Be careful with dates. Expression

 >= @StartDate AND < @EndDate  --- this will miss all rows within @end date

 >= @StartDate AND <= @EndDate --- this...

I haven't been on SQL.Central for a long time, so i just noticed the post.

Not sure if this is the simplest solution, but for sure is the shortest for typing:

This is  INNER JOIN. In distant past, SQL did not have JOIN keword, so people used WHERE block to specify both JOIN condition and filters. Your statement would look like...