SQL show based on date condition

I have following query to start of with:

Select *
from AAF21OKN$                  
where Service like '%AAF21%'
  OR Ydelse like '%UXM%'
  OR Ydelse like '%UXU%'
  OR Ydelse like '%UXR%'
  OR Ydelse like '%UXZ%'
Order by [Patient ID], [Date]

I then get following table (only a snapshot for better desciption):

Patient ID    Date                      Service
----------    -----------------------   ------            
Forløb08196   2020-03-04 00:00:00.000   UXMG25
Forløb08196   2020-03-06 00:00:00.000   AAF21 OKN
Forløb08196   2020-03-06 00:00:00.000   UXRG25BES

Forløb10030   2020-02-03 00:00:00.000   AAF21 OKN
Forløb10030   2020-02-04 00:00:00.000   UXMG25

I now have a problem:

• I only want to show the services where the service starting with ‘AAF21 OKN’ is first (by date).
• Patient ID: ‘Forløb10030’ is for example what i want.
• But in Patient ID: ‘Forløb08196’, I don’t want the Service ‘UXMG25’ row to be showed. As it has happened before the service ‘AAF21 OKN’

Answer

If I understand what you’re after, I think the following will meet your needs:

--Part 1
SELECT *
FROM AAF21OKN$ t1
WHERE
t1.Service Like '%AAF21%'
--Part 2
OR (EXISTS (SELECT 1 FROM AAF21OKN$ t2
            WHERE t2.Service like '%AAF21%'
            AND t2.[Patient ID] = t1.[Patient ID]
            AND t2.Date <= t1.Date)
    AND (
          t2.Ydelse like '%UXM%'
          OR t2.Ydelse like '%UXU%'
          OR t2.Ydelse like '%UXR%'
          OR t2.Ydelse like '%UXZ%'
        )
    )

The Part 1 gets you all the “AAF21 OKN” records. Part 2 gets you all the other records, but only where there EXISTS an “AAF21 OKN” record for the same patient which is on the same date or earlier (you may want to change the <= to < depending on your expectations).