select operatorId, mfrCustNum, companyName from tbl_OPR_ClientOperators opr where ownerId = and fsltablecode = and exists ( select * from tbl_TPM_Contracts where partnerType = 'OPR' and partnerId = operatorId ) and exists ( select * from tbl_LST_PartnerListItems where listId = 31702 and partnerId = operatorId ) and nullif( mfrCustNum, '' ) is not null order by opr.companyName
select r.requestId from tbl_TPM_Contracts c inner join tbl_TPM_Requests r on r.contractId = c.contractId where c.partnerId = and c.partnerType = 'OPR' and c.ownerId = and c.ownerType = with rawData as ( select case when cc.lastDate is null then c.claimPeriodStart else cc.firstDate end firstDate, case when cc.lastDate is null then c.claimPeriodENd else cc.lastDate end lastDate, cc.status, cc.subClaimAmount, cc.approvedAmount, ct.contractId from tbl_TPM_Contracts ct with (nolock) inner join tbl_TPM_Requests r with (nolock) on r.contractId = ct.contractId and r.requestId in ( ) and r.ownerId = ct.ownerId and r.ownerType = ct.ownerType inner join tbl_TPM_ClaimContracts cc with (nolock) on cc.requestId = r.requestId and cc.reconciled = 'Y' and cc.ownerId = r.ownerId and cc.ownerType = r.ownerType inner join tbl_TPM_Claims c with (nolock) on c.claimId = cc.claimId and c.ownerId = cc.ownerId and c.ownerType = cc.ownerType inner join tbl_OPR_CLientOperators opr with (nolock) on opr.operatorId = ct.partnerId and opr.ownerId = ct.ownerId and opr.fsltablecode = ct.ownerType inner join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = ct.partnerId and cdr.ownerId = ct.ownerId and cdr.fsl_tablecode = ct.ownerType and nullif( cdr.cdr_dstCode, '' ) is not null where ct.ownerId = and ct.ownerType = and ct.partnerType = and not ( ct.assignedContractNbr like 'CC%' ) ), dateNormalizedRawData as ( select convert( date, convert( varchar, datepart( month, firstDate )) + '/1/' + convert( varchar, datepart( year, firstDate ))) normalizedFirstDate, dateadd( day, -1, dateadd( month, 1, convert( date, convert( varchar, datepart( month, lastDate )) + '/1/' + convert( varchar, datepart( year, lastDate ))) )) normalizedLastDate, case when status = 'APPROVED_VERIFIEDAMT' then approvedAmount when status = 'APPROVED_CLAIMAMT' then subClaimAmount else 0 end normalizedAmount, * from rawData ) select datediff( month, normalizedFirstDate, normalizedLastDate ) + 1 months, datepart( year, normalizedLastDate ) calendarYear, datepart( month, normalizedLastDate ) calendarMonth, normalizedAmount / convert( float, datediff( month, normalizedFirstDate, normalizedLastDate ) + 1 ) monthlyAmount, * from dateNormalizedRawData select calendarYear, calendarMonth, period, sum( amount ) as amount from qmd_data where ( calendarYear = and calendarMonth >= ) or ( calendarYear = and calendarMonth <= ) group by calendarYear, calendarMonth, period order by calendarYear desc, calendarMonth desc select sum( amount ) as totalAmount from qmd_yearData

#htmleditformat( _runningText )#

#htmleditformat( _summaryText )#

 with allocation as ( select opr.mfrCustNum, opr.operatorId, opr.companyName, ( select count(*) from tbl_TPM_Contracts where partnerId = opr.operatorId and partnertype = 'OPR' ) as contracts, sum( forecast_amt ) total from tbl_MFR_TPF_Detail tpf inner join tbl_OPR_ClientOperators opr on opr.operatorId = tpf.allocationId and opr.ownerId = tpf.mfr_id and opr.fsltablecode = 'MFR' where tpf.allocationType = 'OPR' and tpf.forecast_status = 'R' and tpf.tpartnerType = 'OPR' and tpf.mfr_id = 54 and ( ( convert( int, tpf.forecast_year ) = 2017 and convert( int, tpf.forecast_month ) >= 11 ) or ( convert( int, tpf.forecast_year ) = 2018 and convert( int, tpf.forecast_month ) <= 10 ) or ( convert( int, tpf.forecast_year ) = 2018 and convert( int, tpf.forecast_month ) >= 11 ) or ( convert( int, tpf.forecast_year ) = 2019 and convert( int, tpf.forecast_month ) <= 7 ) ) group by opr.mfrCustNum, opr.operatorId, opr.companyName ) select opr.mfrCustNum, opr.operatorId, opr.companyName, ( select count(*) from tbl_TPM_Contracts where partnerId = opr.operatorId and partnertype = 'OPR' ) as contracts, a.total from tbl_LST_PartnerListItems pli inner join tbl_OPR_CLientOperators opr on opr.ownerId = 54 and opr.fsltablecode = 'MFR' and opr.operatorId = pli.partnerId left outer join allocation a on a.operatorId = opr.operatorId where pli.listId = 31702 order by a.total desc select sum( total ) as total from qmd_allocationSummary
#_right# #_left#