Sql query with join and group by and

Question

I have two table in sql. First one the patient list, second one is their report. all patient's reports are in the report, just with id we can join them. Each patient has some reports (Maybe all the fields of a record is not filled). Now I want to make a report that get the last report of each patient but if some field are empty in the last record of that patient I should fill it with last filled record of that patients records. I have date in the table of reports.

I want to do it for all patients. Here I will add a pic for one patient as an example

In the example above, I want just highlighted ones for this patient in the report.

I have write this query but it give even when a filed in the last record is null while it 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
FROM [Bartar_MSCRM].[dbo].[Filteredbartar_newpaitient] as patient 
JOIN (SELECT r.*, row_number() over (partition by r.bartar_patientname 
                                     order by r.bartar_date desc) as seqnum
      FROM [Bartar_MSCRM].[dbo].[Filteredbartar_callcenterreport] as r
      where r.bartar_delay is not null
     ) r
ON r.bartar_patientname = patient.bartar_newpaitientid and seqnum = 1 
ORDER BY patient.bartar_id DESC ;

patient Table

Report Table

Join

Final Report What I want

Answer 1

this is a sample, in your case you have to get the value of each column in a subquery (either in the join statement, or in the main select statement example:

inner join (
        select distinct bartar_patientname
                ,(select top 1 bartar_pastePerMonth from [Bartar_MSCRM].[dbo].[Filteredbartar_callcenterreport] c2 where c2.bartar_patientname = cte.bartar_patientname and c2.bartar_pastePerMonth is not null order by c2.bartar_date desc) as bartar_date
                ,(select top 1 bartar_acc           from [Bartar_MSCRM].[dbo].[Filteredbartar_callcenterreport] c2 where c2.bartar_patientname = cte.bartar_patientname and c2.bartar_acc is not null order by c2.bartar_date desc) as bartar_acc
                ,(select top 1 bartar_insuranceinfo from [Bartar_MSCRM].[dbo].[Filteredbartar_callcenterreport] c2 where c2.bartar_patientname = cte.bartar_patientname and c2.bartar_insuranceinfo is not null order by c2.bartar_date desc) as bartar_insuranceinfo
                ,(select top 1 bartar_brand         from [Bartar_MSCRM].[dbo].[Filteredbartar_callcenterreport] c2 where c2.bartar_patientname = cte.bartar_patientname and c2.bartar_brand is not null order by c2.bartar_date desc) as bartar_brand
        from [Bartar_MSCRM].[dbo].[Filteredbartar_callcenterreport] cte
        ) r

Again, this is a sample of the solution.

Answer 2

Your script is fine as it looks, so I'll just place that on a temporary table for now, and do a per sequence query and filter it by "OR" afterwards.

Please try the script below.

    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
, ROW_NUMBER() OVER (PARTITION BY r.bartar_newpaitientid, r.bartar_pastepermonth ORDER BY r.bartar_date DESC) AS bartarpaste_sequence
, ROW_NUMBER() OVER (PARTITION BY r.bartar_newpaitientid, r.bartar_acc ORDER BY r.bartar_date DESC) AS bartaracc_sequence
, ROW_NUMBER() OVER (PARTITION BY r.bartar_newpaitientid, r.bartar_insuranceinfo ORDER BY r.bartar_date DESC) AS bartarins_sequence
, ROW_NUMBER() OVER (PARTITION BY r.bartar_newpaitientid, r.bartar_brandname ORDER BY r.bartar_date DESC) AS bartarbrd_sequence
    INTO #tmpPatientReport
    FROM [Bartar_MSCRM].[dbo].[Filteredbartar_newpaitient] as patient 
    JOIN (SELECT r.*, row_number() over (partition by r.bartar_patientname 
                                         order by r.bartar_date desc) as seqnum
          FROM [Bartar_MSCRM].[dbo].[Filteredbartar_callcenterreport] as r
          where r.bartar_delay is not null
         ) r
    ON r.bartar_patientname = patient.bartar_newpaitientid and seqnum = 1 
    ORDER BY patient.bartar_id DESC;

    SELECT
       *
    FROM #tmpPatientReport
    WHERE 
       bartarpaste_sequence = 1
       OR bartaracc_sequence = 1
       OR bartarins_sequence = 1
       OR bartarbrd_sequence = 1

Answer 3

just do join for each column

JOIN (SELECT r.colx, row_number() over (partition by r.bartar_patientname 
                                     order by r.bartar_date desc) as seqnum
      FROM [Bartar_MSCRM].[dbo].[Filteredbartar_callcenterreport] as r
      where r.bartar_delay is not null and r.colx in not null
     ) rx
ON rx.bartar_patientname = patient.bartar_newpaitientid and seqnum = 1