Outlet Count by Hierarchy

Description

Retrieves the number of outlets associated with each sales hierarchy.


Query :

SELECT 
    B.SalesHierarchyId,
    B.Name,
    B.Code,
    B.LevelName,
    B.SalesHierarchyType,
    B.ParentSalesHierarchyId,
    B.ParentSalesHierarchy,
    B.ParentCode,
    COUNT(C.LocationId) AS LocationCount
FROM vwSalesHierarchyList B WITH (NOLOCK)
INNER JOIN vwLocationList C WITH (NOLOCK) 
    ON B.SalesHierarchyId = C.SalesHierarchyId
WHERE B.ClientId = 408
  AND B.Code IN ('V01001','V01002','V01003','V01004','V01005','V01006','V01007','V01008','V01009')
GROUP BY 
    B.SalesHierarchyId,
    B.Name,
    B.Code,
    B.LevelName,
    B.SalesHierarchyType,
    B.ParentSalesHierarchyId,
    B.ParentSalesHierarchy,
    B.ParentCode
ORDER BY B.SalesHierarchyId ASC;