declare @partnerId int = ; declare @partnerType varchar(3) = ; declare @excludeInteractionId int = ; declare @days int = ; with rawData as ( select partnerId, partnerType, interactionId, salesRepFirstName, salesRepLastName, contactFirstName, contactLastName, interactionDate, dispositionDate, salesRepFirstName + ' ' + salesRepLastName as salesRepFullName, contactFirstName + ' ' + contactLastName as contactFullName, notes, mfr_name, sku, product, shortDesc, skuDesc, detailDisposition, cast( round( qty, 0 ) as int) qty, qtyDuration, qtyUOM from tbl_DW_Interactions#_tableSuffix# with (nolock) where partnerId = @partnerId and partnerType = @partnerType and interactionId != @excludeInteractionId and disposition != '?' and detailDisposition in ( 'STD', 'FU', '1T', 'NS' ) and datediff( day, interactionDate, getDate() ) <= @days ), data as ( select d1.partnerId, d1.partnerType, d1.interactionId, d1.interactionDate, d1.dispositionDate, d1.salesRepFullName, d1.contactFullName, d1.notes, ( select d2.detailDisposition, d2.mfr_name, d2.sku, d2.skuDesc, d2.product, d2.shortDesc, d2.qty, d2.qtyDuration, d2.qtyUOM from rawData d2 with (nolock) where d2.interactionId = d1.interactionId and d2.partnerType = d1.partnerType and d2.partnerId = d1.partnerId order by d2.mfr_name for json auto ) as details from rawData as d1 group by d1.partnerId, d1.partnerType, d1.interactionId, d1.interactionDate, d1.dispositionDate, d1.salesRepFullName, d1.contactFullName, d1.notes ) #forJSON( 'data', attributes.datastore, "interactionDate desc, interactionId" )#