need to help query in sql server

See more:

SQL-Server

I have a question about SQL Server.

Table patient:

create table patient (pn int,code int,date date,doctorcode int)

insert into patient (pn,code,date,doctorcode)
values
(1,10,'2015-02-19','100),
(1,10,'2015-02-19','101),
(1,10,'2015-02-19','102),

(2,10,'2015-02-12','101),
(2,10,'2015-02-13','102),
(2,10,'2015-02-14','103),

(3,10,'2015-02-15','103),
(3,10,'2015-02-18','104),
(3,10,'2015-02-26','105),

(4,10,'2015-01-26','105),

5,10,'2013-05-24',100),
(5,10,'2013-05-14',101),
(5,10,'2013-05-14',102),
(5,10,'2013-03-22',103),
(5,10,'2013-05-14',105)

Table Patientref:

create table patientref
(pn int,code int, sdate date,edate date,status int)

insert into patientref(pn,code,sdate,edate,status)
values
(1,10,'2015-02-13','2015-02-19',1),
(1,10,'2015-02-19','2015-03-24',2),
(1,10,'2015-04-28','2015-05-08',4),
(2,10,'2015-02-08','2015-02-19',4),
(2,10,'2015-02-09','2015-02-19',2),
(2,10,'2015-02-10','2015-02-19',2),
(2,10,'2015-02-11','2015-02-18',1),
(3,10,'2015-02-10','2015-02-17',4),
(3,10,'2015-02-10','2015-02-17',3),
(3,10,'2015-02-11','2015-02-18',3),
(2,10,'2015-04-10','2015-05-19',2),
(3,10,'2015-02-11','2015-02-18',1),
(3,10,'2015-02-26','2015-03-18',1),
(4,10,'2015-04-30','2015-06-23',4),

5,10,'2013-02-13','2013-02-13',4),
(5,10,'2013-02-13','2013-04-30',2),
(5,10,'2013-05-14','2013-05-31',2)

Here we need consider patient dates that fall between sdate and edate of the patientrefs table, and then we need to consider the highest status values in order (for example, the highest values in order - 2 is first highest, 4 is second highest, 3 is third highest, and 1 is fourth highest value)

If the date falls between multiple different sdate and edate with the same status values, then we need to consider the latest sdate value and from that entire record we need to extract that value.

Examples: patient

pn |  code  |  date      |   doctorcode
2  |  10    |2015-02-12  |   101
2  |  10    |2015-02-13  |   102
2  |  10    |2015-02-14  |   103

Table : Patientref:

pn |  code  |  sdate      |   edate      | Status
2  |  10    |2015-02-08   |   2015-02-19 |  4
2  |  10    |2015-02-09   |   2015-02-19 |  2
2  |  10    |2015-02-10   |   2015-02-19 |  2
2  |  10    |2015-02-11   |   2015-02-18 |  1

Here, pn=2 values have dates which fall between sdate and edate of patientref table. Then we give highest values status is 2, and status 2 values have two records, then we go for max sdate(latest sdate). Then this pn=2 latest sdates is 2015-02-10 and we need to retrieve the corresponding edate and status values.
pn = 4donot have sdate and edate and status values dut not fall conditon

Based on this, the desired output is below:

pn |  code  |  date      |   doctorcode | sdate     |edate      |status
1  |  10    |2015-02-19  |   100        |2015-02-19 |2015-03-24 | 2
1  |  10    |2015-02-19  |   101        |2015-02-19 |2015-03-24 | 2
1  |  10    |2015-02-19  |   102        |2015-02-19 |2015-03-24 | 2
2  |  10    |2015-02-12  |   101        |2015-02-10 |2015-02-19 | 2
2  |  10    |2015-02-13  |   102        |2015-02-10 |2015-02-19 | 2
2  |  10    |2015-02-14  |   103        |2015-02-10 |2015-02-19 | 2
3  |  10    |2015-02-15  |   103        |2015-02-10 |2015-02-17 | 4
3  |  10    |2015-02-18  |   104        |2015-02-11 |2015-02-18 | 3
3  |  10    |2015-02-26  |   105        |2015-02-26 |2015-03-18 | 1
4  |  10    |2015-01-26  |   105        |           |           | 
5 | 10      |2013-05-24  | 100           |2013-05-14 |2013-05-31 | 2
5 | 10      |2013-05-14  | 101           |2013-05-14 |2013-05-31 | 2
5 | 10      |2013-05-14  | 102           |2013-05-14 |2013-05-31 | 2
5 | 10      |2013-03-22  | 103           |2013-02-13 |2013-04-30 | 2
5 | 10      |2013-05-14  | 105           |2013-05-14 |2013-05-31 | 2

I tried it like this:

select p.pn,p.code,p.[date],p.doctorcode,pr.sdate,pr.edate,pr.[status] from patient p
 outer apply (select top 1 pr.pn,pr.code,pr.sdate,pr.edate,pr.[status] from patientref pr 
where pr.pn=p.pn and pr.code=p.code and p.date between pr.sdate and pr.edate
 order by case when pr.status=2 
then 1 when pr.status=4 then 2
 when pr.status=3 then 3 
when pr.status=1 then 4 end ,pr.sdate 
 )pr

but this query not given expected result.here when dos not fall between sdate and edate that records not given in the above query.
I required that records also.if not fall b/w condition then we need retrive that records empty values for that records

please tell me how to write query to achive this task in sql server