declare @completedStartDate date = ; declare @completedEndDate date = ; declare @plannedStartDate date = ; declare @plannedEndDate date = ; declare @planStartDate date = ; declare @planEndDate date = ; declare @today date = ; with interactions as ( select distinct interactionId, disposition, interactionDate, pastDue from tbl_DW_Interactions#_tableSuffix# with (nolock) where ( ( interactionDate between @completedStartDate and @completedEndDate ) -- completed period or ( interactionDate between @planStartDate and @planEndDate ) -- plan period or ( pastDue = 'Y' ) -- past due calls or ( interactionDate >= @today and disposition = '?' ) -- planned calls ) and territoryId in ( ) and salesRepId in ( ) and ( 1 = 2 or territoryPath like ) and salesRepId in ( ) and interactionMethod in ( ) and salesRepOwnerType = and sourceType != 'EML' ), completed as ( select count( * ) callCount from interactions where disposition = 'X' and interactionDate between @completedStartDate and @completedEndDate ), planCompleted as ( select count( * ) callCount from interactions where disposition = 'X' and interactionDate between @planStartDate and @planEndDate ), pastDue as ( select count( * ) callCount from interactions where pastDue = 'Y' ), planned as ( select count( * ) callCount from interactions where interactionDate between @plannedStartDate and @plannedEndDate and pastDue = 'N' and disposition = '?' ), planPlanned as ( select count( * ) callCount from interactions where interactionDate between @planStartDate and @planEndDate and pastDue = 'N' and disposition = '?' ), data as ( select ( select callCount from completed ) as completedCalls, ( select callCount from pastDue ) as pastDueCalls, ( select callCount from planned ) as plannedCalls, ( select callCount from planPlanned ) as planPlannedCalls, ( select callCount from planCompleted ) as planCompletedCalls ) select * from data for json auto, include_null_values attributes.datastore.writeToLog( _r );