declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @cutoffDate date = '07/03/2024'; with emailsByOrderId as ( select ownerId, ownerType, cast( replace( emailTrackingCode, 'SMPLORDFUL', '' ) as int ) as orderId, 'REQUESTED' as step, emailDate, emailBody from tbl_WRK_Emails with (nolock) where ownerId = @ownerId and ownertype = @ownerType and emailTrackingCode like 'SMPLORDFUL%' union all select e.ownerId, e.ownerType, o.orderId, case when e.emailTrackingCode = 'SMPLfulfill' then 'FULFILLED' else 'SHIPPED' end as emailStep, e.emailDate, e.emailBody from tbl_wrk_emails e with (nolock) inner join tbl_pos_orders o with (nolock) on e.ownerId = o.ownerId and e.ownerType = o.fsl_tablecode and e.emailSubject like '%## ' + o.orderNum + '%' where e.emailtrackingCode in ( 'SMPLshp', 'SMPLfulfill' ) and e.emailDate >= @cutoffDate and e.ownerId = @ownerId and e.ownerType = @ownerType ) , emailsByOrderIdRanked as ( select *, row_number() over ( partition by orderId order by emailDate desc ) as ranked from emailsByOrderId ) , rawdata as ( select pos.orderId, pos.orderNum, pos.email, pos.orderDate, pos.status, eml.emailDate, eml.step emailType, case when eml.emailBody is not null then 'Y' else 'N' end as hasEmail, eml.emailBody from tbl_POS_Orders pos with (nolock) left outer join emailsByOrderIdRanked eml on eml.ranked = 1 and eml.ownerId = pos.ownerId and eml.ownerType = pos.fsl_tablecode and eml.orderId = pos.orderId where pos.ownerId = @ownerId and pos.fsl_tablecode = @ownerType and not exists ( select * from tbl_POS_OrderLineItems items with (nolock) where items.orderId = pos.orderId ) and pos.orderdate >= @cutoffDate ) , data as ( select * from rawdata where hasEmail = 'Y' ) #forJSON( 'data', attributes.datastore, 'orderDate' )#