Note: This content is automatically generated by the ETL tool. Last build time is 2024-03-19 13:44:37. Please contact MWS Insights Data Infrastructure for the query.

SurfaceCSPPartner

Data source: gpsmart.database.windows.net

select * from (
SELECT PM.MPNVOrgID, CAST(PM.MPNVOrgID AS VARCHAR(20)) AS MPNIDALT   ,PM.PartnerOneID
, PM.AreaName, PM.SubsidiaryName
, PM.PartnerOneName
, SOnly.SurfaceRevenue
, SOnly.IsSurfaceOnly
, CSPOnly.CSPRevenue
, CSPOnly.IsCSPOnly
,CONVERT(VARCHAR(11), GETDATE(), 100) AS RefreshDatePartners
, ('https://ocpinsights.microsoft.com/SinglePartnerOverview?partneroneid='+ 	CAST(PM.[PartnerOneID] AS varchar(MAX))+'&tab=SPOCPPSummary') AS OCP_URL_Summary
FROM (
SELECT PM.MPNVOrgID, PM.PartnerOneID,
PM.AreaName, PM.SubsidiaryName, PM.PartnerOneName
FROM FY23Planning.vw_PartnerMaster PM
WHERE pm.PartnerOrgType = 'Reseller'
AND pm.SourceIDColumnName = 'SalesTPID'
AND pm.SourceName = 'MSSales'
GROUP BY PM.MPNVOrgID, PM.PartnerOneID
, PM.AreaName, PM.SubsidiaryName, PM.PartnerOneName
) PM
LEFT JOIN (
SELECT
PartnerOneID
,SUM(SoldSeatsRevenue) AS SurfaceRevenue
,'Yes' as IsSurfaceOnly
FROM [sales].[vw_Fact_Sales] FS
JOIN [sales].[vw_Bridge_PartnerAssociation] BPA ON FS.AssociationKey = BPA.AssociationKey
JOIN [sales].[vw_Dim_Product] DP ON FS.ProductKey = DP.ProductKey
WHERE
SuperRevSumDivisionNAme IN ( 'Surface Devices'
)
AND FiscalMonthID > 336
GROUP BY  PartnerOneID
HAVING SUM(SoldSeatsRevenue) > 0
) SOnly ON PM.PartnerOneID = SOnly.PartnerOneID
LEFT JOIN (
SELECT
PartnerOneID
,SUM(SoldSeatsRevenue) AS CSPRevenue
,'Yes' as IsCSPOnly
FROM [sales].[vw_Fact_Sales] FS
JOIN [sales].[vw_Bridge_PartnerAssociation] BPA ON FS.AssociationKey = BPA.AssociationKey
JOIN [sales].[vw_Dim_DetailPricingLevel] DPL ON FS.DetailPricingLevelKey = DPL.DetailPricingLevelKey
WHERE
DPL.ReportingSummaryPricingLEvelName = 'Cloud Solution Provider'
AND FiscalMonthID > 336
GROUP BY PartnerOneID
HAVING SUM(SoldSeatsRevenue) > 0
) CSPOnly ON PM.PartnerOneID = CSPOnly.PartnerOneID
WHERE (SOnly.SurfaceRevenue > 0 OR CSPOnly.CSPRevenue > 0)
) tab