sql server - Sql query with join and group by and -

i have 2 table in sql. first 1 patient list, second 1 report. patient's reports in report, id can join them. each patient has reports (maybe fields of record not filled). want make report last report of each patient if field empty in last record of patient should fill last filled record of patients records. have date in table of reports.

i want patients. here add pic 1 patient example

in example above, want highlighted ones patient in report.

i have write query give when filed in last record null while has data in previous records.

select patient.bartar_id,patient.bartar_enteringthesystem,patient.bartar_proviencename,        patient.bartar_cityname,patient.bartar_coloplastrepname,patient.bartar_consultorname,        patient.bartar_provienceofsurgeryname,patient.bartar_cityofsurgeryname,        patient.bartar_surgeryhospitalname,patient.bartar_doctor,patient.bartar_patientstatusname,        patient.bartar_ostomytypename, patient.bartar_ostomytimename,        r.bartar_date,r.bartar_delay,r.bartar_nextcall,r.new_newcaller,          r.bartar_brandname,r.bartar_pastename,r.bartar_bagname,r.bartar_accname,        r.bartar_pastepermonth,r.bartar_bagepermonth,r.bartar_insuranceinfo,        patient.bartar_deathhealeddate,patient.bartar_dateofseurgery [bartar_mscrm].[dbo].[filteredbartar_newpaitient] patient  join (select r.*, row_number() on (partition r.bartar_patientname                                       order r.bartar_date desc) seqnum       [bartar_mscrm].[dbo].[filteredbartar_callcenterreport] r       r.bartar_delay not null      ) r on r.bartar_patientname = patient.bartar_newpaitientid , seqnum = 1  order patient.bartar_id desc ; 

patient table

report table

join

final report want

this sample, in case have value of each column in subquery (either in join statement, or in main select statement example:

inner join (         select distinct bartar_patientname                 ,(select top 1 bartar_pastepermonth [bartar_mscrm].[dbo].[filteredbartar_callcenterreport] c2 c2.bartar_patientname = cte.bartar_patientname , c2.bartar_pastepermonth not null order c2.bartar_date desc) bartar_date                 ,(select top 1 bartar_acc           [bartar_mscrm].[dbo].[filteredbartar_callcenterreport] c2 c2.bartar_patientname = cte.bartar_patientname , c2.bartar_acc not null order c2.bartar_date desc) bartar_acc                 ,(select top 1 bartar_insuranceinfo [bartar_mscrm].[dbo].[filteredbartar_callcenterreport] c2 c2.bartar_patientname = cte.bartar_patientname , c2.bartar_insuranceinfo not null order c2.bartar_date desc) bartar_insuranceinfo                 ,(select top 1 bartar_brand         [bartar_mscrm].[dbo].[filteredbartar_callcenterreport] c2 c2.bartar_patientname = cte.bartar_patientname , c2.bartar_brand not null order c2.bartar_date desc) bartar_brand         [bartar_mscrm].[dbo].[filteredbartar_callcenterreport] cte         ) r 

again, sample of solution.