Concatenate column with aggregate of another column

Question

This is my table.

And the result I want is below. I cannot use where stage = ? because that column values can be anything.

I tried with this query but it returns the null values.

select case when [date] =(select  max(date) from #temp) then stage end as stages from #temp

Below is my full query where I would like to use the min and max value

SELECT opportunity.OpportunityId,
opportunity.ClientBusinessGuid as ClientID
, clientbusiness.BusinessName as ClientName
, otypes.[Name] as [Opportunity Type]
, choices.[Text] as [Source]
,opportunity.DateCreated
,DateName(month,opportunity.DateCreated) as CohortMonth
,opportunity.ExpectedDate as [ExpectedCloseDate(start of period)]
,history.[ExpectedCloseDate(end of period)]
,case when history2.[date] = (select min(history2.[date]) from #temp2 where ---HERE
history2.OpportunityId = opportunity.OpportunityId) THEN history2.Stage END
FROM MYP_Opportunity opportunity
JOIN MYP_ClientBusinesses_X clientbusiness ON opportunity.ClientBusinessGuid = 
clientbusiness.ClientBusinessGuid
JOIN MYP_OpportunityTypes otypes ON otypes.OpportunityTypeId = opportunity.TypeId
JOIN MYP_Choices choices ON opportunity.SourceId = choices.ChoiceId
JOIN #temp history ON history.OpportunityId = opportunity.OpportunityId
JOIN #temp2 history2 ON history2.OpportunityId = opportunity.OpportunityId
AND clientbusiness.AdviserbusinessId = 66
--AND opportunity.OpportunityId = 180643
where history.HistoryId = (Select MAX(HistoryId) from #temp where history.OpportunityId = 
opportunity.OpportunityId)
group by opportunity.ClientBusinessGuid, clientbusiness.BusinessName, otypes.[Name] ,choices. 
[Text] ,opportunity.DateCreated, opportunity.OpportunityId
,opportunity.ExpectedDate,history.[ExpectedCloseDate(end of 
period)],history2.OpportunityId,history2.Stage,history2.Date

Answer 1

you can use a cross apply on the history table to get the whole row according to the opportunity id (with stage) according to min or max date (ascending for min date and descending for max date = 2 cross applies) ?

something like:

select 
    t1.opportunity 
    ,MinDate.minimumDate
    ,MaxDate.maximumDate
from #temp2 t1
cross apply --outer?
(
    select top 1
        concat(date, stage) as minimumDate
    from #temp2 t2
    where t2.opportunity = t1.opportunity
    ...
    order by date asc
)MinDate
cross apply
(
    select top 1
        concat(date,stage) as maximumDate
    from #temp2 t2
    where t2.opportunity = t1.opportunity
    ...
    order by date desc
)MaxDate
group by
    t1.opportunity
    ,MinDate.minimumDate
    ,MaxDate.maximumDate
    ,....