select
J.JobOrderID,J.JobType,J.JobNo,J.Status as JoborderStatus,AJ.Status as AirJobStatus,
isnull(INV.InvoiceID,0) as INVInvoiceid,isnull(INV.InvoiceNo,0) as INVInvoiceNo,isnull(INV.StatusID,0) as INVStatusID,isnull(INV.InvoiceModeID,0) as INVInvoiceModeID
from ffs_tbl_joborder J
inner join FFS_Tbl_AirJob AJ on AJ.AirJobID=J.JobOrderID and AJ.JobNo=J.JobNo
left join (
select JobOrderID,JobNo,JobType,InvoiceID,isnull(InvoiceNo,0) as InvoiceNo ,StatusID,InvoiceModeID,createddate,Minchk from (
select INV.JobOrderID,INV.JobNo,INV.JobType,INV.InvoiceID,INV.InvoiceNo,INV.StatusID,INV.InvoiceModeID,INV.CreatedDate, row_number() over(partition by JobOrderID,JobType order by CreatedDate desc) as Minchk
from FFS_TBl_Invoice INV where JobType in ('AirJob','AirHouseJob')
) as t1
where Minchk=1
)
INV on INV.JobOrderID=J.JobOrderID and INV.JobNo=J.JobNo and INV.JobType=J.JobType
where
INV.JobType in ('AirJob','AirHouseJob') and J.JobOrderID=2802
group by J.JobOrderID,J.JobType,J.JobNo,J.Status,AJ.Status,INV.InvoiceID,INV.InvoiceNo,INV.StatusID,INV.InvoiceModeID