sql - Choosing MAX value by id in a view?

Question

I have created a simple view based on a few columns in our database

ALTER VIEW [BI].[v_RCVLI_Test] AS
Select distinct
    Borger.CPRnrKort as CPR,
    (...)
    IndsatsDetaljer.VisitationId as VisitationsId,
    Indsats.KatalogNavn as IndsatsNavn,
    (case
        when 
        (   
            Indsats.Model = 'SMDB2 Tilbudsmodel' or
            Indsats.Model = 'SMDB2 Samtalemodel' or
            Indsats.Model = 'Tilbudsmodel' or
            Indsats.Model = 'NAB Tilbudsmodel'
        )
        then IndsatsDetaljer.ServicePeriodeStart
        else IndsatsDetaljer.Ikrafttraedelsesdato
        end
    ) as StartDato,
    (case
        when 
        (   
            Indsats.Model = 'SMDB2 Tilbudsmodel' or
            Indsats.Model = 'SMDB2 Samtalemodel' or
            Indsats.Model = 'Tilbudsmodel'
        )
        then (case when IndsatsDetaljer.VisitationSlut = '9999-12-31' then convert(varchar(10), getdate(), 23) else IndsatsDetaljer.VisitationSlut end)
        when
        Indsats.Model = 'NAB Tilbudsmodel'
        then (case when IndsatsDetaljer.NABehandlingSlutDato = '9999-12-31' then convert(varchar(10), getdate(), 23) else IndsatsDetaljer.NABehandlingSlutDato end)
        else (case when IndsatsDetaljer.VisitationSlut = '9999-12-31' then convert(varchar(10), getdate(), 23) else IndsatsDetaljer.VisitationSlut end)
        end
    ) as StopDato,
    Refusion.Handlekommune as Handlekommune,
    replace(Refusion.Betalingskommune, 'Ukendt', 'Kendt') Betalingskommune

from nexus2.Fact_VisiteretTid as Fact

join nexus2.Dim_Paragraf Paragraf
on Fact.DW_SK_Paragraf = Paragraf.DW_SK_Paragraf
join nexus2.Dim_Indsats Indsats
on Fact.DW_SK_Indsats = Indsats.DW_SK_Indsats (...)

The cases for StartDato and StopDato are there because those dates come from different columns. I've converted the date '9999-12-31' to the the current date because we'll be doing some time calculations later on, and it's just more convenient.

CPR is the id of a person, VisitationsId is the id for the service the person received. In theory, There should only be one StartDato and one StopDato per VisitationsId, but because of a glitch in the documentation system, we sometimes get TWO StopDato: one is the correct, and one is '9999-12-31' (now converted to current date).

So I need to group by VisitationsId and then just take the MIN value of StopDato, but I'm kind of unsure how to go about doing that?

CPR	VisitationsId	StartDato	StopDato	Something Else
123	56	2019-01-01	2019-12-12	Something
123	56	2019-01-01	9999-12-31	Something
123	58	2019-01-01	2019-12-14	Something
345	59	2018-11-01	9999-12-31	Something
345	55	2017-01-02	2017-11-12	Something
345	55	2017-01-02	9999-12-31	Something

question from:https://stackoverflow.com/questions/65841772/choosing-max-value-by-id-in-a-view

Answer 1

Add a filter which tests for this condition?

with cte (
    {your current query}
)
select *
from cte T
where not (
    StopDato = '9999-12-31'
    and exists (
       select 1
       from cte T1
       where T1.VisitationsId = T.VisitationsId
       and StopDato != '9999-12-31'
    )
);

And you look like you are converting StopDato to a varchar which is bad - you should treat dates as dates until you need to display them.