select avg( DATEDIFF( day, firstActiveDate, dateClosed ) + 1 ) opportunityTimeToClose, count(*) closedOpportunityCount, SUM( case when statusCode = 'C' then 1 else 0 end * weightAmt ) opportunitySoldLbs, SUM( case when statusCode = 'C' then 1 else 0 end * caseAmt ) opportunitySoldCases, SUM( case when statusCode = 'C' then 1 else 0 end * coalesce( o.incomeAmt, 0 ) ) opportunitySoldDollars, sum( case when statusCode = 'C' then 1 else 0 end ) opportunitySaleCount, sum( case when statusCode = 'R' then 1 else 0 end ) opportunityNoSaleCount from tbl_SPL_Opportunities o with (nolock) inner join tbl_SPL_Stages s with (nolock) on s.StageID = o.StageID and s.StatusCode in ('R', 'C' ) and s.ownerID = o.ownerID and s.ownerType = o.ownerType inner join tbl_PRD_Products p with (nolock) on p.prodId = o.subjectId and p.ownerId = o.ownerId and p.fsl_TableCode = o.ownerType inner join tblManufacturers mfr with (nolock) on mfr.mfr_Id = o.mfrId where o.subjectType = 'PRD' and o.ownerId = and o.ownerType = and o.salesPersonUserId = and DateClosed >= and DateClosed <= select count(*) totalOpportunityUpdates, count( distinct o.opportunityId ) updatedOpportunities from tbl_SPL_Opportunity_StageChanges sc with (nolock) inner join tbl_SPL_Opportunities o with (nolock) on o.OpportunityID = sc.opportunityId where o.subjectType = 'PRD' and o.ownerId = and o.ownerType = and o.salesPersonUserId = and sc.changeDateTime >= and sc.changeDateTime <= select 'C' statusCode, o.mfrId, o.opportunityId from tbl_SPL_Opportunities o with (nolock) inner join tbl_SPL_Stages s with (nolock) on s.StageID = o.stageId and s.ownerId = o.ownerId and s.ownerType = o.ownerType where o.OwnerID = and o.OwnerType = 'BRO' and o.salesPersonUserId = and ( o.dateClosed >= and o.dateClosed <= ) union select 'A' statusCode, o.mfrId, o.opportunityId from tbl_SPL_Opportunities o with (nolock) inner join tbl_SPL_Stages s with (nolock) on s.StageID = o.stageId and s.ownerId = o.ownerId and s.ownerType = o.ownerType where o.OwnerID = and o.OwnerType = 'BRO' and o.salesPersonUserId = and ( o.FirstActiveDate <= and ( o.DateClosed is null or o.DateClosed > ) ) union select 'I' statusCode, o.mfrId, o.opportunityId from tbl_SPL_Opportunities o with (nolock) inner join tbl_SPL_Stages s with (nolock) on s.StageID = o.stageId and s.ownerId = o.ownerId and s.ownerType = o.ownerType where o.OwnerID = and o.OwnerType = 'BRO' and o.salesPersonUserId = and ( o.FirstActiveDate is null or o.firstActiveDate >= ) and o.CreateDate <= select COUNT( distinct o.OpportunityId ) currentOpportunityCount from tbl_SPL_Opportunities o with (nolock) inner join tbl_SPL_Stages s with (nolock) on s.StageID = o.stageId and s.ownerId = o.ownerId and s.ownerType = o.ownerType inner join tbl_SPL_Opportunity_StageChanges sc with (nolock) on sc.OpportunityId = o.opportunityId where o.OwnerID = and o.OwnerType = and o.salesPersonUserId = and ( o.FirstActiveDate <= and ( o.DateClosed is null or o.DateClosed > ) ) and datediff( day, sc.changeDateTime, ) between 0 and 90 select count(*) newOpportunities from tbl_SPL_Opportunities o with (nolock) where o.OwnerID = and o.OwnerType = and o.salesPersonUserId = and o.CreateDate >= and o.CreateDate <= select avg( case when osc.newStageId=1945 then case when subJoin2.test2 = 1 then case when subJoin3.test3 = 1 then case when DATEDIFF( day, firstActiveDate, dateClosed ) < 91 then DATEDIFF( day, firstActiveDate, dateClosed ) else DATEDIFF( day, firstActiveDate, dateClosed ) - 90 end else case when s.StateCode in ('C','R') then DATEDIFF( day, firstActiveDate, dateClosed ) - ( case when DATEDIFF( day, offSetStart.ChangeDateTime, offSetEnd.ChangeDateTime ) > 90 then 90 else DATEDIFF( day, offSetStart.ChangeDateTime, offSetEnd.ChangeDateTime ) end ) else DATEDIFF( day, firstActiveDate, ) - ( case when DATEDIFF( day, offSetStart.ChangeDateTime, offSetEnd.ChangeDateTime ) > 90 then 90 else DATEDIFF( day, offSetStart.ChangeDateTime, offSetEnd.ChangeDateTime ) end ) end end else case when s.StateCode in ('C','R') then DATEDIFF( day, firstActiveDate, dateClosed ) - 90 else DATEDIFF( day, firstActiveDate, ) - 90 end end else case when s.StateCode in ('C','R') then DATEDIFF( day, firstActiveDate, dateClosed ) else DATEDIFF( day, firstActiveDate, ) end end ) averageDaysInPipelineHTD, avg( cast(stageCount.sellingstages as float) / ( case when osc.newStageId=1945 then case when subJoin2.test2 = 1 then case when subJoin3.test3 = 1 then case when DATEDIFF( day, firstActiveDate, dateClosed ) < 91 then case when DATEDIFF( day, firstActiveDate, dateClosed ) < 1 then 1 else DATEDIFF( day, firstActiveDate, dateClosed ) end else case when DATEDIFF( day, firstActiveDate, dateClosed ) - 90 < 1 then 1 else DATEDIFF( day, firstActiveDate, dateClosed ) - 90 end end else case when s.StateCode in ('C','R') then case when ( DATEDIFF( day, firstActiveDate, dateClosed ) - ( case when DATEDIFF( day, offSetStart.ChangeDateTime, offSetEnd.ChangeDateTime ) > 90 then 90 else case when DATEDIFF( day, offSetStart.ChangeDateTime, offSetEnd.ChangeDateTime ) < 1 then 1 else DATEDIFF( day, offSetStart.ChangeDateTime, offSetEnd.ChangeDateTime ) end end ) ) < 1 then 1 else DATEDIFF( day, firstActiveDate, dateClosed ) - ( case when DATEDIFF( day, offSetStart.ChangeDateTime, offSetEnd.ChangeDateTime ) > 90 then 90 else case when DATEDIFF( day, offSetStart.ChangeDateTime, offSetEnd.ChangeDateTime ) < 1 then 1 else DATEDIFF( day, offSetStart.ChangeDateTime, offSetEnd.ChangeDateTime ) end end ) end else case when ( DATEDIFF( day, firstActiveDate, ) - ( case when DATEDIFF( day, offSetStart.ChangeDateTime, offSetEnd.ChangeDateTime ) > 90 then 90 else DATEDIFF( day, offSetStart.ChangeDateTime, offSetEnd.ChangeDateTime ) end ) ) < 1 then 1 else DATEDIFF( day, firstActiveDate, ) - ( case when DATEDIFF( day, offSetStart.ChangeDateTime, offSetEnd.ChangeDateTime ) > 90 then 90 else DATEDIFF( day, offSetStart.ChangeDateTime, offSetEnd.ChangeDateTime ) end ) end end end else case when s.StateCode in ('C','R') then case when DATEDIFF( day, firstActiveDate, dateClosed ) - 90 < 1 then 1 else DATEDIFF( day, firstActiveDate, dateClosed ) - 90 end else case when ( DATEDIFF( day, firstActiveDate, ) - 90 ) < 1 then 1 else DATEDIFF( day, firstActiveDate, ) - 90 end end end else case when s.StateCode in ('C','R') then case when ( DATEDIFF( day, firstActiveDate, dateClosed ) + 1 ) < 1 then 1 else DATEDIFF( day, firstActiveDate, dateClosed ) + 1 end else case when (DATEDIFF( day, firstActiveDate, ) ) < 1 then 1 else DATEDIFF( day, firstActiveDate, ) end end end ) ) averagePipelineRateHTD from tbl_SPL_Opportunities o with (nolock) inner join tbl_SPL_Stages s with (nolock) on s.StageID = o.StageID and s.StateCode in ('C','R','O') and s.statusCode <> 'I' and s.ownerId = o.ownerId and s.ownerType = o.ownerType inner join ( select sqo.opportunityid, ( select count(distinct newstageid) from tbl_spl_opportunity_stagechanges with (nolock) where opportunityid = sqo.opportunityid ) sellingStages from tbl_spl_opportunities sqo with (nolock) where sqo.ownerId = and sqo.ownerType = and sqo.salesPersonUserId = ) stageCount on stageCount.opportunityid = o.opportunityid left join tbl_SPL_Opportunity_StageChanges osc with (nolock) on osc.opportunityId = o.OpportunityID and osc.event='created' left outer join ( select distinct scTwo.opportunityId, 1 as test2 from tbl_SPL_Opportunity_StageChanges scTwo with (nolock) where scTwo.event <> 'created' and scTwo.newStageId <> 1945 ) as subJoin2 on subJoin2.opportunityId = o.OpportunityID left outer join ( select scSD.opportunityId, ChangeDateTime from tbl_SPL_Opportunity_StageChanges scSD with (nolock) where scSD.event = 'created' and scSD.previousStageId = 1945 ) as offSetStart on offSetStart.opportunityId = o.OpportunityID left outer join ( select scED.opportunityId, ChangeDateTime from tbl_SPL_Opportunity_StageChanges scED with (nolock) where scED.event <> 'created' and scED.previousStageId = 1945 and scED.newStageId <> 1945 ) as offSetEnd on offSetEnd.opportunityId = o.OpportunityID left outer join ( select distinct scThree.opportunityId, 1 as test3 from tbl_SPL_Opportunity_StageChanges scThree with (nolock) where (scThree.event='closed' or scThree.event='killed') and (scThree.newStageId=1546 or scThree.newStageId=1547) and scThree.previousstageid=1945 ) as subJoin3 on subJoin3.opportunityId = o.OpportunityID where o.subjectType = 'PRD' and o.ownerId = and o.ownerType = and o.salesPersonUserId = and ( ( s.statecode in ('C','R') and DateClosed >= and DateClosed <= ) or ( s.statecode = 'O' and createDate <= ) ) and exists (select * from tbl_SPL_Opportunity_StageChanges sc with (nolock) inner join tbl_SPL_Stages st with (nolock) on st.StageID = sc.NewStageId and st.StateCode = 'O' and s.statusCode <> 'I' where sc.OpportunityId = o.OpportunityID) and not exists ( select * from tbl_SPL_Opportunity_StageChanges scA with (nolock) where scA.opportunityId = o.OpportunityID and scA.event = 'created' and scA.previousStageId = 1945 and not exists ( select * from tbl_SPL_Opportunity_StageChanges scB with (nolock) where scB.opportunityId = scA.opportunityId and scB.NewStageId <> 1945 and scB.event <> 'created' ) and scA.ChangeDateTime > DateAdd(day,-90,getdate()) ) case when (DATEDIFF( day, firstActiveDate, ) + 1) < 1 then 1 else DATEDIFF( day, firstActiveDate, ) + 1 end end ) ) averagePipelineRateHTD from tbl_SPL_Opportunities o inner join tbl_SPL_Stages s on s.StageID = o.StageID and s.StateCode in ('C','R','O') and s.statusCode <> 'I' inner join ( select sqo.opportunityid, ( select count(distinct newstageid) from tbl_spl_opportunity_stagechanges where opportunityid = sqo.opportunityid ) sellingStages from tbl_spl_opportunities sqo where sqo.ownerId = and sqo.ownerType = and sqo.salesPersonUserId = ) stageCount on stageCount.opportunityid = o.opportunityid where o.subjectType = 'PRD' and o.ownerId = and o.ownerType = and o.salesPersonUserId = and ( ( s.statecode in ('C','R') and DateClosed >= and DateClosed <= ) or ( s.statecode = 'O' and createDate <= ) ) and exists (select * from tbl_SPL_Opportunity_StageChanges sc inner join tbl_SPL_Stages st on st.StageID = sc.NewStageId and st.StateCode = 'O' and s.statusCode <> 'I' where sc.OpportunityId = o.OpportunityID) ---> select count(*) as pipelineClosedCountHTD from tbl_SPL_Opportunities o with (nolock) inner join tbl_SPL_Stages s with (nolock) on s.StageID = o.stageId and s.ownerId = o.ownerId and s.ownerType = o.ownerType where o.OwnerID = and o.OwnerType = and o.salesPersonUserId = and o.subjectType = 'PRD' and ( o.dateClosed >= and o.dateClosed <= ) and s.statecode in ('C','R') and exists (select * from tbl_SPL_Opportunity_StageChanges sc with (nolock) inner join tbl_SPL_Stages st with (nolock) on st.StageID = sc.NewStageId and st.StateCode = 'O' and s.statusCode <> 'I' where sc.OpportunityId = o.OpportunityID) select count(*) as pipelineClosedSaleCountHTD from tbl_SPL_Opportunities o with (nolock) inner join tbl_SPL_Stages s with (nolock) on s.StageID = o.stageId and s.ownerId = o.ownerId and s.ownerType = o.ownerType where o.OwnerID = and o.OwnerType = and o.salesPersonUserId = and o.subjectType = 'PRD' and ( o.dateClosed >= and o.dateClosed <= ) and s.statecode = 'c' and exists (select * from tbl_SPL_Opportunity_StageChanges sc with (nolock) inner join tbl_SPL_Stages st with (nolock) on st.StageID = sc.NewStageId and st.StateCode = 'O' and s.statusCode <> 'I' where sc.OpportunityId = o.OpportunityID)