I have run your query on the dummy data supplied and it generates a few error messages:
Invalid column name 'rn'.
Invalid column name 'dos'.
Invalid column name 'dos'.
Ambiguous column name 'pn'.
Ambiguous column name 'code'.
On fixing these errors the query returned the following:
pn code mindos
11 5 2015-04-07
The reason the following record is not being returned
pn | code | mindos
13 | 7 | 2012-11-07
is due to the way these two records are being sorted in the
row_number() over
part of the query
dos |pn |code |servicecode
2012-11-07 |13 |7 |94
2012-11-07 |13 |7 |99
The record with the servicecode = 99 is coming in first and the join to PatientService does not have service record 99, so the expected record is not returned.
To get the result you desire, the query can be modified to
1. take into consideration of the servicecode in the order by for min dos
or
2. filter the patient table with the PatientService initially and then do the min dos order by
Hope that helps you on your way.
If you need anymore help, fix the initial errors stated in the error messages and then just make a comment.
But if you have having issues with the initial errors, I can help you out with those too.
---update---
select
pn, code, dos as mindos
from
(select
a.pn, a.code, servicecode, a.dos
from
(select
pn, code, servicecode, dos,
row_number() over (partition by pn, code order by dos, servicecode) as rn
from patient) a
inner join
patientrefs b on a.pn = b.pn and a.code = b.code and a.rn = 1
and a.dos between b.sdate and b.edate) as mindos
inner join
PatientService c on mindos.servicecode = c.servicecode
select
*
from(
select
row_number() over (partition by pn, code order by dos, servicecode) as rn,
*
from Patient) p
inner join Patientrefs pr
on p.pn = pr.pn and p.code = pr.code
inner join PatientService ps
on p.servicecode = ps.Servicecode
where p.rn = 1
and p.dos between pr.sdate and pr.edate
;
(
select
row_number() over (partition by pn, code order by dos, isnull(ps.servicecode, 999)) as rn,
p.*,
isnull(ps.servicecode, 999) servicecodeOrder
from Patient p
left join PatientService ps
on p.servicecode = ps.servicecode
) p
They should both work, but you need to test it out.