with buyingOperatorsTy as ( select cho_id, sum(forecast_qty) as forecast_qty from tbl_DW_OperatorAnalyzer_#client.ownertype##client.ownerid# where ownertype = and ownerid = and purchasingMonth between and and skuid = and prodid = group by cho_id ), buyingOperatorsLy as ( select cho_id, sum(forecast_qty) as forecast_qty from tbl_DW_OperatorAnalyzer_#client.ownertype##client.ownerid# where ownertype = and ownerid = and purchasingMonth between and and skuid = and prodid = group by cho_id ), buyingNotBuyingItemsTy as ( select cho_id, sum(forecast_qty) as forecast_qty from tbl_DW_OperatorAnalyzer_#client.ownertype##client.ownerid# where ownertype = and ownerid = and purchasingMonth between and and skuid = and prodid = group by cho_id ), buyingNotBuyingItemsLy as ( select cho_id, sum(forecast_qty) as forecast_qty from tbl_DW_OperatorAnalyzer_#client.ownertype##client.ownerid# where ownertype = and ownerid = and purchasingMonth between and and skuid = and prodid = group by cho_id ), baseOperators as ( select o.cho_id, o.cho_name, o.cho_address, o.cho_city, o.cho_state, o.cho_zipcode, seg.segment from tbl_cho_operators o with (nolock) left outer join tbl_opr_segments seg on seg.segmentid = o.cho_segmentid inner join tbl_UT_ZipCodes_3D zips3D2 with (nolock) on zips3D2.zipCode_3D = left( o.cho_zipCode, '3' ) inner join tbl_ORG_AttributeLinks links with (nolock) on links.child_attrId = zips3D2.zip3D_id and links.mstr_attrTypeID = 18 and links.child_attrTypeID = 19 inner join tbl_ORG_AttributeLinks links2 with (nolock) on links2.child_attrID = links.mstr_attrID and links2.child_attrTypeID = 18 and links2.mstr_attrTypeID = 17 inner join tbl_UT_fseLocales locs with (nolock) on locs.localeID = links2.mstr_attrID and locs.localeType = 17 and locs.localeID = where o.cho_type in ( ) and ( ( o.cho_name like or o.cho_city like ) and 1 = 1 ) and o.cho_segmentid = and ( o.cho_city = and o.cho_state = ) and o.cho_zipcode like and o.cho_state = AND ( o.cho_id in ( select cho.cho_id from tbl_CRM_Affiliations aff with (nolock) inner join tbl_CHO_Operators cho with (nolock) on aff.partnerId = cho.cho_id and aff.ownerType = 'MSC' and aff.affiliationType = 'GPO' and aff.affiliationStatus = 'A' and aff.orgId = and aff.orgType = 'GPO' ) OR o.cho_id in ( select opr.fsl_choId as pkId from tbl_OPR_ClientOperators opr with (nolock) where opr.ownerId = and opr.fsltablecode = 'CHO' and opr.crmActive = 'Y' and nullif( opr.fsl_choId, 0 ) <> 0 ) ) AND ( o.cho_id in ( select cho.cho_id from tbl_CRM_Affiliations aff with (nolock) inner join tbl_CHO_Operators cho with (nolock) on aff.partnerId = cho.cho_id and aff.ownerType = 'MSC' and aff.affiliationType = 'CMC' and aff.affiliationStatus = 'A' and aff.orgId = and aff.orgType = 'CMC' ) OR o.cho_id in ( select opr.fsl_choId as pkId from tbl_OPR_ClientOperators opr with (nolock) where opr.ownerId = and opr.fsltablecode = 'CHO' and opr.crmActive = 'Y' and nullif( opr.fsl_choId, 0 ) <> 0 ) ) ) select o.*, case when bty.cho_id is null and bly.cho_id is null then 0 else 1 end as purchasingRecords, coalesce(bty.forecast_qty,0) as forecast_qty_ty, coalesce(bly.forecast_qty,0) as forecast_qty_ly from baseOperators o left outer join buyingOperatorsTy bty on bty.cho_id = o.cho_id left outer join buyingOperatorsLy bly on bly.cho_id = o.cho_id left outer join buyingNotBuyingItemsTy bnbty on bnbty.cho_id = o.cho_id left outer join buyingNotBuyingItemsLy bnbly on bnbly.cho_id = o.cho_id where 1 = 1 and ( bty.cho_id is not null or bly.cho_id is not null ) and ( bty.cho_id is null and bly.cho_id is null ) and ( bnbty.cho_id is null and bnbly.cho_id is null ) order by coalesce(bty.forecast_qty,0) desc, o.cho_name order by o.cho_name select segment, '' as cleanSegment, sum( coalesce(bty.forecast_qty,0) ) as forecast_qty_ty, sum( coalesce(bly.forecast_qty,0) ) as forecast_qty_ly from baseOperators o left outer join buyingOperatorsTy bty on bty.cho_id = o.cho_id left outer join buyingOperatorsLy bly on bly.cho_id = o.cho_id left outer join buyingNotBuyingItemsTy bnbty on bnbty.cho_id = o.cho_id left outer join buyingNotBuyingItemsLy bnbly on bnbly.cho_id = o.cho_id where 1 = 1 and ( coalesce(bty.forecast_qty,0) > 0 or coalesce(bly.forecast_qty,0) > 0 ) and ( coalesce(bty.forecast_qty,0) = 0 and coalesce(bly.forecast_qty,0) = 0 ) and ( coalesce(bnbty.forecast_qty,0) = 0 and coalesce(bnbly.forecast_qty,0) = 0 ) group by segment order by sum( coalesce(bty.forecast_qty,0) ) desc, o.segment select * from qryOperatorPurchasing order by forecast_qty_ty desc, cleanSegment