operatorDetails log
{ts '2024-09-27 09:46:34'} SQL
declare @ownerId int = 3658 ;
    declare @ownertype varchar(3) = 'MFR' ;
    declare @operatorId int = 12691191 ;

    declare @thisYear int = Year( getDate() );
    declare @lastYear int = @thisYear - 1;
    declare @nextYear int = @thisYear + 1;


    with 


    distributionDetailsRaw as (

        select dd.ownerId, dd.ownerType, dd.operatorId, dd.distributionDetailsId, dd.cdr_recordId, cdr.cdr_dstName, dd.accountNumber, dd.avgWeeklyPurchases, dd.oprPriority, 
               nullif( dd.salesRepId, 0 ) as salesRepId,

	    
            row_number() over ( partition by dd.cdr_recordId order by dd.distributionDetailsId ) as cdr_recordId_rank
  
        from tbl_OPR_DistributionDetails dd with (nolock)
        inner join tbl_CDR_Distributors cdr with (nolock)
            on cdr.ownerId = dd.ownerId
            and cdr.fsl_tablecode = dd.ownerType
            and cdr.cdr_recordId = dd.cdr_recordId
        where dd.ownerId = @ownerId
            and dd.ownerType = @ownerType
            and dd.operatorId = @operatorId

    ),


    /*
    distributionDetails as (

        select dd.distributionDetailsId, dd.cdr_recordId, cdr.cdr_dstName, dd.accountNumber, dd.avgWeeklyPurchases, dd.oprPriority, 
	        sr.fspro_userId as salesRepId, sr.firstName salesRepFirstName, sr.lastName salesRepLastName, sr.email salesRepEmail,
	        cast( case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 else 0 end as bit ) as isPrimary, 
			cast( case when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 1 else 0 end as bit ) as isSecondary, 
			case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1
				 when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 2
			else 3 end as sortRank
  
        from tbl_OPR_DistributionDetails dd with (nolock)
        inner join tbl_CDR_Distributors cdr with (nolock)
            on cdr.ownerId = dd.ownerId
            and cdr.fsl_tablecode = dd.ownerType
            and cdr.cdr_recordId = dd.cdr_recordId
        inner join tbl_OPR_ClientOperators opr ( nolock)
            on opr.ownerId = dd.ownerId
            and opr.fsltablecode = dd.ownerType
            and opr.operatorId = dd.operatorId
            left outer join tbl_FSPro_members sr with (nolock)
            on sr.ownerId = dd.ownerId
            and sr.fsl_tablecode = dd.ownerType
            and sr.fspro_userId = dd.salesRepId
        where dd.ownerId = @ownerId
            and dd.ownerType = @ownerType
            and dd.operatorId = @operatorId

    ),
    */

    distributionDetails as (
        select 
            dd.*,
            sr.firstName salesRepFirstName, sr.lastName salesRepLastName, sr.email salesRepEmail
            /*,
            cast( case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 else 0 end as bit ) as isPrimary, 
            cast( case when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 1 else 0 end as bit ) as isSecondary
            */

        from distributionDetailsRaw dd with (nolock)
        /*
        inner join tbl_OPR_ClientOperators opr ( nolock)
            on opr.ownerId = dd.ownerId
            and opr.fsltablecode = dd.ownerType
            and opr.operatorId = dd.operatorId
            */
        left outer join tbl_FSPro_members sr with (nolock)
            on sr.ownerId = dd.ownerId
            and sr.fsl_tablecode = dd.ownerType
            and sr.fspro_userId = dd.salesRepId

        where cdr_recordId_rank = 1

    ),

    partnerURLs as (


        select p.partnerId, p.partnerType, p.instagramURL, p.linkedinURL, p.tiktokURL, p.facebookURL, p.youtubeURL, p.twitterURL
        from ( 
        select urlType + 'URL' as urlType, urlValue, partnerId, partnerType
            from tbl_CRM_PartnerURLs with (nolock)
            where ownerId = @ownerId
            and ownerType = @ownerType
            and partnerType = 'OPR'
            and partnerId = @operatorId
            and nullif( urlValue, '' ) is not null
            ) urls


            pivot ( 
                    min( urlValue )
                    for urlType in ( instagramURL, linkedinURL, tiktokURL, facebookURL, youtubeURL, twitterURL )
                ) as p

    ),




    rawUnitForecast as (
        select ownerId, ownerType, partnerId as operatorId, coalesce( unit_qty, 1 ) as num_units, case when cast( forecast_year as int ) = @thisYear then 'num_units_thisYr'
                                                        when cast( forecast_year as int ) = @lastYear then 'num_units_lastYr'
                                                        else 'num_units_nextYr' end as fieldName
                from tbl_MFR_TPF_PartnerUnitForecast with (nolock)
            where forecast_year in ( @lastYear, @thisYear, @nextYear )
                and ownerId = @ownerId
                and ownerType = @ownerType
                and partnerType = 'OPR'
                and partnerId = @operatorId
    ),

    unitForecastPivot as (

        select ownerId, ownerType, operatorId, num_units_lastYr, num_units_thisYr, num_units_nextYr
        from (
            select * from rawUnitForecast with (nolock)
        ) as pivotSrc
        pivot (
            max( num_units ) for fieldName in ( num_units_lastYr, num_units_thisYr, num_units_nextYr )
        ) as pivotResult

    ),

    unitForecast as (

        select opr.ownerId, opr.fsltablecode as ownerType, opr.operatorId, 
                coalesce( uf.num_units_lastYr, coalesce( opr.num_units_lastYr, 1 ) ) num_units_lastYr,
                coalesce( uf.num_units_thisYr, coalesce( opr.num_units_thisYr, 1 ) ) num_units_thisYr,
                coalesce( uf.num_units_nextYr, coalesce( opr.num_units_nextYr, 1 ) ) num_units_nextYr
          from tbl_OPR_ClientOperators opr with (nolock)
        left outer join unitForecastPivot uf with (nolock)
            on uf.ownerId = opr.ownerId
            and uf.ownerType = opr.fsltablecode
            and uf.operatorId = opr.operatorId
        where opr.ownerId = @ownerId
        and opr.fsltablecode = @ownerType
        and opr.operatorId = @operatorId

    ),


    memberGroups as (


		select opr.ownerId, opr.fsltablecode, cho.cho_type as oprCompanyType, opr.companyName, opr.fsl_choId, opr.operatorId, row_number() over ( partition by opr.fsl_choId order by operatorId ) as mappingPriority
			from tbl_OPR_ClientOperators opr with (nolock)
			inner join tbl_CHO_Operators cho with (nolock)
			on cho.cho_id = opr.fsl_choId
		where opr.ownerId = @ownerId
			and opr.fsltablecode = @ownerType
			and cho.cho_type in ( 'C', 'G' )

	)

    

    , gpo as (
        select a.partnerId operatorId, org.companyName, org.fsl_choId, a.affiliationDate, a.partnerAffiliateNbr, row_number() over ( partition by a.partnerId order by a.affiliationDate  ) as ranking
            from [tmp_affiliations_MFR3658_1727444790362] a with (nolock )
        inner join memberGroups org with (nolock)
            on org.operatorId = a.orgId
            and org.ownerId = a.ownerId
            and org.fsltablecode = a.ownerType
        where a.ownerId = @ownerId
            and a.ownerType= @ownerType
            and a.affiliationStatus = 'A'
            and org.oprCompanyType = 'G'
            and a.partnerId = @operatorId
            and a.partnerType = 'OPR'
    )

    , allGPO as (
        select p.operatorId, gpo1OPR.operatorId as gpoId_1, gpo2OPR.operatorId as gpoId_2, gpo3OPR.operatorId as gpoId_3, gpo4OPR.operatorId as gpoId_4, gpo1OPR.companyName as gpoName_1, gpo2OPR.companyName as gpoName_2, gpo3OPR.companyName as gpoName_3, gpo4OPR.companyName as gpoName_4
        from ( 
            select operatorId, fsl_choId, 'gpo' + cast( ranking as varchar) as gpoN
            from gpo
            where ranking <= 4  
            
        )
        as gpoData
        pivot ( 
            min( fsl_choId )
            for gpoN in ( gpo1, gpo2, gpo3, gpo4 )
        ) as p
            left outer join memberGroups gpo1OPR with (nolock)
        on gpo1OPR.fsl_choId = p.gpo1
        and gpo1OPR.mappingPriority = 1

        left outer join memberGroups gpo2OPR with (nolock)
        on gpo2OPR.fsl_choId = p.gpo2
        and gpo2OPR.mappingPriority = 1

        left outer join memberGroups gpo3OPR with (nolock)
        on gpo3OPR.fsl_choId = p.gpo3
        and gpo3OPR.mappingPriority = 1

        left outer join memberGroups gpo4OPR with (nolock)
        on gpo4OPR.fsl_choId = p.gpo4
        and gpo4OPR.mappingPriority = 1

    ),

    cmc as (
        select a.partnerId operatorId, org.companyName, org.fsl_choId, a.affiliationDate, a.partnerAffiliateNbr, row_number() over ( partition by a.partnerId order by a.affiliationDate  ) as ranking
        from [tmp_affiliations_MFR3658_1727444790362] a with (nolock)
        inner join memberGroups org with (nolock)
            on org.operatorId = a.orgId
            and org.ownerId = a.ownerId
            and org.fsltablecode = a.ownerType
        where a.ownerId = @ownerId
            and a.ownerType= @ownerType
            and a.affiliationStatus = 'A'
            and org.oprCompanyType = 'C'
            and a.partnerId = @operatorId
            and a.partnerType = 'OPR'
    ),

    allCMC as (

        select p.operatorId, cmc1OPR.operatorId as cmcId_1, cmc2OPR.operatorId  as cmcId_2, cmc3OPR.operatorId  as cmcId_3, cmc1OPR.companyName as cmcName_1, cmc2OPR.companyname as cmcName_2, cmc3OPR.companyName as cmcName_3
        from ( 
            select operatorId, fsl_choId, 'cmc' + cast( ranking as varchar) as cmcN
            from cmc
            where ranking <= 3 
            

            )  
        as cmcData
        pivot ( 
            min( fsl_choId )
            for cmcN in ( cmc1, cmc2, cmc3 )
        ) as p
        left outer join memberGroups cmc1OPR with (nolock)
        on cmc1OPR.fsl_choid = p.cmc1
        and cmc1OPR.mappingPriority = 1
        left outer join memberGroups cmc2OPR with (nolock)
        on cmc2OPR.fsl_choid = p.cmc2
        and cmc2OPR.mappingPriority = 1
        left outer join memberGroups cmc3OPR with (nolock)
        on cmc3OPR.fsl_choid = p.cmc3
        and cmc3OPR.mappingPriority = 1

    )

    , affiliationsRaw as (
        select coalesce( c.operatorId, g.operatorId ) operatorId, 
        c.cmcId_1, c.cmcName_1, c.cmcId_2, c.cmcName_2, c.cmcId_3, c.cmcName_3, 
        g.gpoId_1, g.gpoName_1, g.gpoId_2, g.gpoName_2, g.gpoId_3, g.gpoName_3, g.gpoId_4, g.gpoName_4
        from allCMC c
        full outer join allGPO g
        on g.operatorId = c.operatorId
    )

    , affiliations as (
        select r.*, 
            c1.AffiliationDate cmcAffiliationDate_1, c1.partnerAffiliateNbr cmcPartnerAffiliateNbr_1,
            g1.AffiliationDate gpoAffiliationDate_1, g1.partnerAffiliateNbr gpoPartnerAffiliateNbr_1,
            g2.AffiliationDate gpoAffiliationDate_2, g2.partnerAffiliateNbr gpoPartnerAffiliateNbr_2,
            g3.AffiliationDate gpoAffiliationDate_3, g3.partnerAffiliateNbr gpoPartnerAffiliateNbr_3,
            g4.AffiliationDate gpoAffiliationDate_4, g4.partnerAffiliateNbr gpoPartnerAffiliateNbr_4
          from affiliationsRaw r with (nolock)

          left outer join memberGroups cm1 with (nolock)
             on cm1.operatorId = r.cmcId_1
          left outer join cmc c1 with (nolock)
            on c1.fsl_choId = cm1.fsl_choId
            and c1.operatorId = r.operatorId

          left outer join memberGroups gm1 with (nolock)
             on gm1.operatorId = r.gpoId_1
          left outer join gpo g1 with (nolock)
            on g1.fsl_choId = gm1.fsl_choId
            and g1.operatorId = r.operatorId

          left outer join memberGroups gm2 with (nolock)
             on gm2.operatorId = r.gpoId_2
          left outer join gpo g2 with (nolock)
            on g2.fsl_choId = gm2.fsl_choId
            and g2.operatorId = r.operatorId

          left outer join memberGroups gm3 with (nolock)
             on gm3.operatorId = r.gpoId_3
          left outer join gpo g3 with (nolock)
            on g3.fsl_choId = gm3.fsl_choId
            and g3.operatorId = r.operatorId

          left outer join memberGroups gm4 with (nolock)
             on gm4.operatorId = r.gpoId_4
          left outer join gpo g4 with (nolock)
            on g4.fsl_choId = gm4.fsl_choId
            and g4.operatorId = r.operatorId

    ),



    data as (
		select opr.ownerId, opr.fsltablecode as ownerType, 
        
            opr.operatorId, 

            opr.oprType as priority,
            opr.classificationId as classificationId,
            opr.mfr_bsr_id,

            opr.profileComplete, case when opr.profileComplete = 'Y' then null else nullif( opr.profileErrors, '' ) end profileErrors,

            cast( floor( 10191817 * sqrt( opr.operatorId * log10( opr.operatorId ))) as bigint ) as operatorTk,
            
            opr.companyName, opr.address, opr.address2, opr.city, opr.state, opr.zipCode, opr.countryId, opr.county,

               opr.phone, opr.phoneExt, opr.faxNumber,

               nullif( opr.mfrCustNum, '' ) mfrCustNum,

               nullif( opr.url, '' ) url,
               cho.cho_id, cho.cho_name, cho.cho_type,
		       case when cho.cho_type = 'I' or ( opr.oprCompanyType = '0' and opr.independentYN = 1 ) then 'INDEPENDENT'
		            when cho.cho_type = 'U' or ( opr.oprCompanyType = '0' and opr.independentYN = 0 ) then 'UNIT'
		            when cho.cho_type = 'O' or opr.oprCompanyType = '1' then 'CHAIN HQ'
		            when cho.cho_type = 'G' or opr.oprCompanyType = 'G' then 'GPO'
		            when cho.cho_type = 'C' or opr.oprCompanyType = 'C' then 'CMC'
		            when cho.cho_type = 'S' or opr.oprCompanyType = 'S' then 'SERVICE'
		            when cho.cho_type = 'H' or opr.oprCompanyType = 'H' then 'HOLDING'
    	            else 'UNKNOWN' end as operatorType,

                cast( case when cho.cho_type = 'I' or ( opr.oprCompanyType = '0' and opr.independentYN = 1 )then 1 else 0 end as bit ) isIndependent,
                cast( case when cho.cho_type = 'U' or ( opr.oprCompanyType = '0' and opr.independentYN = 0 ) then 1 else 0 end as bit ) isUnit,
                cast( case when cho.cho_type = 'O' or opr.oprCompanyType = '1' then 1 else 0 end as bit ) isChainHQ,
                cast( case when cho.cho_type = 'G' or opr.oprCompanyType = 'G' then 1 else 0 end as bit ) isGPO,
                cast( case when cho.cho_type = 'C' or opr.oprCompanyType = 'C' then 1 else 0 end as bit ) isCMC,
                cast( case when cho.cho_type = 'S' or opr.oprCompanyType = 'S' then 1 else 0 end as bit ) isService,
                cast( case when cho.cho_type = 'H' or opr.oprCompanyType = 'H' then 1 else 0 end as bit ) isHolding,

                cast( case when cho.cho_id is not null then 1 else 0 end as bit ) is1FSMapped,



                nullif( opr.primaryContactId, 0 ) as primaryContactId,
                nullif( opr.cuisineId, 0 ) as cuisineId, cuisine.attrDescription as cuisine,
                cast( case when opr.tradeEnabled = 'Y' then 1 else 0 end as bit ) as tradeEnabled,
                nullif( opr.tradeNbr, '' ) tradeNbr,
                coalesce( opr.alcoholStatus, '?' ) as alcoholStatus,
                opr.seasonOpenDate, opr.seasonCloseDate, opr.seasonType,

                opr.directCustomer, opr.directCustomerNbr,

                nullif( opr.comments, '' ) comments, nullif( opr.comments2, '' ) comments2,



                nullif( opr.distrib1, 0 ) as distrib1,


                dd1.salesRepId as distrib1_salesRepId,
                dd1.accountNumber as distrib1_accountNumber,
                dd1.avgWeeklyPurchases as distrib1_avgWeeklyPurchases,

                nullif( opr.distrib2, 0 ) as distrib2,

                dd2.salesRepId as distrib2_salesRepId,
                dd2.accountNumber as distrib2_accountNumber,
                dd2.avgWeeklyPurchases as distrib2_avgWeeklyPurchases,

                -- json_query(( select * from distributionDetails as dd with (nolock) order by dd.cdr_dstName for json auto, include_null_values )) as distributionDetails,
                nullif( opr.chain_distr_notes, '' ) as chain_distr_notes,

                opr.recordSource, opr.dateCreated, opr.lastUpdated,

                opr.lastInteractionDate, 

                nullif( opr.email, '' ) email, 
                pu.instagramURL, pu.linkedinURL, pu.tiktokURL, pu.facebookURL, pu.youtubeURL, pu.twitterURL,

                ter.territoryId, ter.name as territoryName, ter.territoryPath, nullif( tm.email, '' ) as territoryManagerEmail,

                opr.oprSegment, seg.clientSegId, seg.segmentName, seg.parentSegmentName, seg.segmentPath,



                opr.oprCompanyType,

                opr.unitsFollowSegment, 
			    opr.unitsFollowCuisine, 
				opr.unitsFollowSeasonality, 
				opr.unitsFollowGPO,
				opr.unitsFollowCMC,

                coalesce( popr.unitsFollowSegment, 'N' ) as segmentFollowsHQ,
                coalesce( popr.unitsFollowCuisine, 'N' ) as cuisineFollowsHQ,
                coalesce( popr.unitsFollowSeasonality, 'N' ) as seasonalityFollowsHQ,

                opr.oprParentCo, popr.operatorId parentOperatorId, popr.companyname parentCompanyName,
				opr.chainHQUnitNotes,
                opr.unitNbr,

                popr.chainHQUnitNotes as chainHQUnitNotesFromParent,
                nullif( opr.mdmType, '' ) mdmType, nullif( opr.mdmId, '' ) mdmId, opr.mdmDate,
                nullif( opr.relationshipRank, 0 ) relationshipRank,

                uf.num_units_lastYr as num_units_lastYr,
                uf.num_units_thisYr as num_units_thisYr,
                coalesce( uf.num_units_nextYr, uf.num_units_thisYr ) as num_units_nextYr,

                cast( case when exists( select * from tbl_OPR_ClientOperators u with (nolock) where u.ownerId = opr.ownerId and u.fsltablecode = opr.fsltablecode and u.oprParentCo = opr.operatorId ) then 1 else 0 end as bit ) hasUnits

                , a.cmcID_1, a.cmcPartnerAffiliateNbr_1, a.cmcAffiliationDate_1
                , a.gpoID_1, a.gpoPartnerAffiliateNbr_1, a.gpoAffiliationDate_1
                , a.gpoID_2, a.gpoPartnerAffiliateNbr_2, a.gpoAffiliationDate_2
                , a.gpoID_3, a.gpoPartnerAffiliateNbr_3, a.gpoAffiliationDate_3
                , a.gpoID_4, a.gpoPartnerAffiliateNbr_4, a.gpoAffiliationDate_4

                , bsr.email accountOwnerEmail, bsr.firstName accountOwnerFirstName, bsr.lastName accountOwnerLastName, bsr.title accountOwnerTitle
                , bsr.firstName + ' ' + bsr.lastName as accountOwnerFullName
                , opr.crmActive
                , cho.tastewiseId


		  from tbl_OPR_CLientOperators opr  with (nolock)
          left outer join tbl_OPR_ClientOperators popr with (nolock)
             on popr.operatorId = opr.oprParentCo
             and popr.ownerId = opr.ownerId
             and popr.fsltablecode = opr.fsltablecode
          inner join tbl_TER_Territories ter with (nolock)
             on ter.ownerId = opr.ownerId
             and ter.fsl_tablecode= opr.fsltablecode
             and ter.territoryId = opr.territoryId
           left outer join tbl_OPR_ClientSegments seg with (nolock)
              on seg.ownerId = opr.ownerId
              and seg.fsl_tablecode = opr.fsltablecode
              and seg.clientSegId = opr.oprSegment
              left outer join tbl_ORG_Attributes cuisine with (nolock)
			   on cuisine.ownerId = opr.ownerId
			   and cuisine.fsl_tablecode = opr.fsltablecode
			   and cuisine.attrId = opr.cuisineId

        left outer join tbl_Fspro_members bsr with (nolock)
           on bsr.fspro_userId = opr.mfr_bsr_id
           and bsr.ownerId > 0

        left outer join tbl_Fspro_members tm with (nolock)
           on tm.fspro_userId = ter.primaryManager
           and tm.ownerId > 0

		 left outer join tbl_CHO_Operators cho with (nolock)
		    on cho.cho_id = opr.fsl_choId

            left outer join partnerURLs pu with (nolock)
              on 1 = 1
              left outer join unitForecast uf with (nolock)
                on uf.ownerId = opr.ownerId
                and uf.ownertype = opr.fsltablecode
                and uf.operatorId = opr.operatorId

                left outer join affiliations a with (nolock)
                on a.operatorId = opr.operatorId

        left outer join distributionDetails dd1 with (nolock)
            on dd1.cdr_recordId = opr.distrib1

        left outer join distributionDetails dd2 with (nolock)
            on dd2.cdr_recordId = opr.distrib2

		 where opr.ownerId = @ownerId
		   and opr.fsltablecode = @ownerType
           and opr.operatorId = @operatorId
	)

    
, data_preData as (

	
	select *
	  from data 
	where ( 1 = 1 ) 


	)

	, data_data as (

		select * 
		       , count(*) over() as fseRowCount 
		  from data_preData
		  

	)



	, data_return as (
		select * from data_data
		where 1 = 1


	order by operatorId
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, data_result as (
		select ( select count(*) from data_data ) as totalCount,
			   
		       ( select * from data_return for json auto, include_null_values ) as data
		)


	select * from data_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER