declare @ownerId int = ; declare @ownerType varchar(3) = ; with forms as ( select f.formId, f.formName, f.publishDate, f.unpublishDate, case when datediff( day, getDate(), f.unpublishDate ) < 0 then 'N' when datediff( day, f.publishDate, getDate() ) < 0 then 'N' else 'Y' end as active, f.synopsis, confirmationMessageTitle, confirmationMessageText, f.fieldDomain, f.fieldCategory, json_query( f.contactFieldMapping, '$' ) as contactFieldMapping, cast( case when 1 = 2 or json_value( f.contactFieldMapping, '$.#fieldName#' ) is not null then 1 else 0 end as bit ) as parseEntity, f.applicability, cast( case when f.applicability like '%OPR%' then 1 else 0 end as bit ) isOperatorApplicable, cast( case when f.applicability like '%CDR%' then 1 else 0 end as bit ) isDistributorApplicable, cast( case when f.applicability like '%FSPM%' then 1 else 0 end as bit ) isGeneralApplicable, cast( case when f.applicability like '%SPL%' then 1 else 0 end as bit ) isOpportunityApplicable, cast( case when f.applicability like '%CLL%' then 1 else 0 end as bit ) isInteractionApplicable, cast( case when f.applicability like '%EML%' then 1 else 0 end as bit ) isEmailCampaignApplicable, cast( case when f.applicability like '%BRO%' then 1 else 0 end as bit ) isBrokerApplicable, cast( case when f.applicability like '%WWW%' then 1 else 0 end as bit ) isWebsiteApplicable, coalesce( f.archive, 'N' ) as archive, case when exists ( select * from tbl_WRK_FormRecipients fr with (nolock) where fr.formId = f.formId and fr.ownerId = f.ownerId and fr.ownerType = f.ownerType and fr.recipientType in ( 'TO', 'APPROVER' )) then 'Y' else 'N' end as ready, case when exists ( select * from tbl_WRK_Fields wff with (nolock) where wff.ownerId = f.ownerId and wff.ownerType = f.ownerType and wff.active = 'Y' and wff.category = f.fieldCategory ) then 'Y' else 'N' end as hasFields, f.ccSubmission from tbl_WRK_Forms f with (nolock) where f.ownerId = @ownerId and f.ownerType = @ownerType ) , submissions as ( select formId, count(*) submissionCount, max( submissionTime ) lastSubmissionTime from tbl_WRK_FormSubmissions with (nolock) where ownerId = @ownerId and ownerType = @ownerType group by formId ), data as ( select f.*, s.submissionCount, s.lastSubmissionTime from forms f left outer join submissions s on s.formId = f.formId ) #forJSON( 'data', attributes.datastore, 'formName' )#