with submissions as ( select f.formId,s.linkId,s.submissionId, s.submitUserId, s.ownerId,s.ownerType, f.formName, case when datepart( month, s.submissionTime ) = 1 then 'JAN' when datepart( month, s.submissionTime ) = 2 then 'FEB' when datepart( month, s.submissionTime )= 3 then 'MAR' when datepart( month, s.submissionTime ) = 4 then 'APR' when datepart( month, s.submissionTime ) = 5 then 'MAY' when datepart( month, s.submissionTime ) = 6 then 'JUN' when datepart( month, s.submissionTime ) = 7 then 'JUL' when datepart( month, s.submissionTime ) = 8 then 'AUG' when datepart( month, s.submissionTime ) = 9 then 'SEP' when datepart( month, s.submissionTime ) = 10 then 'OCT' when datepart( month, s.submissionTime ) = 11 then 'NOV'when datepart( month, s.submissionTime ) = 12 then 'DEC' end + convert( varchar, datepart( year, s.submissionTime )) as reportMonth, count(*) as submissionCount from tbl_WRK_Forms f inner join tbl_WRK_FormSubmissions s on s.formId = f.formId group by f.formId, f.formName, datepart( month, s.submissionTime ), datepart( year, s.submissionTime ),s.linkId, s.ownerId,s.ownerType,s.submitUserId,s.submissionId ), locations as ( select cdr_recordId, cdr_dstCode, cdr_dstName from tbl_cdr_distributors where cdr_dstCode like 'RD%' and ownerId = 14 and fsl_TableCode = 'BRO' ) select cdr.cdr_dstname as RD, i.partnerId, isd.saleSubjectId, isd.saleSubjectType, fs.reportMonth, fs.formId, fs.submissionId,i.interactionDate, mem.firstName as SalesRepFname, mem.lastName as SalesRepLName, datepart( month, i.interactionDate ) as interactionMonth, datepart( year, i.interactionDate ) as interactionYear, 'x' as outputVal from tbl_CRM_InteractionSaleDetails isd WITH (nolock) inner join submissions fs on fs.ownerId = isd.ownerId and fs.ownerType = isd.ownerType and fs.linkId = isd.interactionDetailId inner join tbl_CRM_Interactions i WITH (nolock) on i.ownerId = isd.ownerId and i.ownerType = isd.ownerType and i.interactionId = isd.interactionId inner join locations cdr on cdr.cdr_recordId = i.partnerId inner join tbl_FSPRO_Members mem WITH (nolock) on mem.fspro_UserId = fs.submitUserId where isd.ownerId = 14 and isd.ownerType = 'BRO' and i.partnerType = 'CDR' order by fs.submissionId desc