select COUNT(*) as profilesAdded from dbo.tbl_OPR_ProductStatus ps with (nolock) inner join tbl_OPR_ClientOperators opr with (nolock) on opr.OperatorID = ps.operatorId and opr.ownerID = ps.ownerId and opr.FSLTableCode = ps.ownerType where ps.ownerId = and ps.ownerType = and ps.createTimeStamp >= and ps.createTimeStamp <= and ps.createUserId = and opr.oprtype in ('*','A','B') select COUNT(*) as profilesAddedYTD from dbo.tbl_OPR_ProductStatus ps with (nolock) inner join tbl_OPR_ClientOperators opr with (nolock) on opr.OperatorID = ps.operatorId and opr.ownerID = ps.ownerId and opr.FSLTableCode = ps.ownerType where ps.ownerId = and ps.ownerType = and ps.createTimeStamp >= and ps.createTimeStamp <= and ps.createUserId = and opr.oprtype in ('*','A','B') select sum( case when PS.volumeTime = 'w' then volumeQty * 52 when PS.volumeTime = 'm' then volumeQty * 12 when PS.volumeTime = 'y' then volumeQty end ) as annualNotBuyingCases, SUM( case when PS.volumeTime = 'w' then ((volumeQty * 52) * coalesce(prd.averageUnitPrice,0)) * (coalesce(prd.commissionRate,0)) when PS.volumeTime = 'm' then ((volumeQty * 12) * coalesce(prd.averageUnitPrice,0)) * (coalesce(prd.commissionRate,0)) when PS.volumeTime = 'y' then (volumeQty * coalesce(prd.averageUnitPrice,0)) * (coalesce(prd.commissionRate,0)) end ) as annualNotBuyingComm from dbo.tbl_OPR_ProductStatus PS with (nolock) inner join tbl_OPR_ClientOperators opr with (nolock) on opr.ownerId = and opr.FSLTableCode = and opr.OperatorID = PS.operatorId left outer join tbl_PRD_Products prd with (nolock) on prd.ProdID = PS.prodId and prd.ownerID = ps.ownerID and prd.FSL_TableCode = ps.ownerType where PS.createTimeStamp >= and PS.createTimeStamp <= and PS.purchasingstatus = 'n' and PS.createUserId = and opr.OPRType in ('*','A','B')