| SQL |
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 |