0

This is my table. enter image description here

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

enter image description here

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
3
  • If date is minimal in some row in a group then CONCAT(date, stage) is minimal in this row too.
    – Akina
    Aug 31, 2022 at 9:09
  • Welcome to the DBA.SE community. Please don't post pictures of data, as they cannot be used to reproduce your situation. Consider pasting the table in ASCII Tables and convert to something that can be used in your question (Hint: format as comment "REDDIT Style" in ASCII Tables). Do the same for the example result set. Thanks.
    – John K. N.
    Aug 31, 2022 at 12:42
  • will do!! Thanks John Sep 1, 2022 at 0:03

1 Answer 1

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
    ,....
2
  • 1
    Many thanks looks like this is exactly what I wanted. I'm new to SQL so struggling to use these operators. Anyway learned about cross apply. Next confusion is, what if the table has only one row per oppurtunity ID? How to keep the maximum field blank in such case ? Aug 31, 2022 at 23:58
  • Well, this all depends on how you want to represent your data. What you will achieve with the above, Is that you will have 1 row per OpportunityID which will have a Max and Min Date. If the dates are the same, you can add logic to handle it to not display the Max Date, or, you can add logic in the MaxDate "cross apply" (make it an "outer apply") and add something like "and concat(date,stage) <> MinDate.minimumDate". This will make the MaxDate null if you have just one row. But this will cause issues if you have 2 rows with the same date! Unless that will never be the case for you. Sep 5, 2022 at 8:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.