asked
Count active Customers based on item master
Hi There, I need you to help me on writing two queries in SQL 2008 that shows the following information based on item master 1- Brand wise count on customer master plus customer who purchased the brand 2- Item Wise count of customer master plus customer who purchased the item Here the link that shows the table information and the query which I tried. The problem I'm facing is the count for Division and Route are showing as null. Base on brands available in Item master I need to show the count of available customers for each brand and also the count of customer who purchased the brand. [CLick here to view the sample data on SQL Fiddle][1] SELECT brandname, division, route, DivisionTotalCustomersCount = MAX(DivisionTotalCustomersCount), RouteTotalCustomersCount = MAX(RouteTotalCustomersCount), PurchasedCustomersCount = SUM(PurchasedCustomersCount) FROM (SELECT i.brandname, c.division, c.route, DivisionTotalCustomersCount = (SELECT COUNT(distinct x.CustomerID) FROM CustomerMaster x WHERE x.division = c.division), RouteTotalCustomersCount = (SELECT COUNT(distinct x.CustomerID) FROM CustomerMaster x WHERE x.Route = c.route), PurchasedCustomersCount = count(distinct C.CustomerID) FROM CustomerMaster c LEFT OUTER JOIN SalesData s on c.CustomerID = s.CustomerID right outer join ItemMaster i on s.item = i.itemcode GROUP BY i.brandname, c.division, c.route) A GROUP BY brandname, division, route ORDER BY 1 [1]:
http://sqlfiddle.com/#!3/df2bb/6
Does this query get anywhere near what you are expecting: select A.BrandName, B.Division, B.Route, B.DivisionTotalCustomers, B.RouteTotalCustomers, isnull(C.PurchasedCustomersCount,0) as PurchasedCustomersCount from ( select distinct BrandName, Route, Division from dbo.ItemMaster cross join dbo.CustomerMaster ) A join ( select distinct Division, Route, DENSE_RANK() over (partition by Division order by c.CustomerID asc) + DENSE_RANK() over (partition by Division order by c.CustomerID desc) - 1 as DivisionTotalCustomers , DENSE_RANK() over (partition by ROUTE order by c.CustomerID asc) + DENSE_RANK() over (partition by ROUTE order by c.CustomerID desc) - 1 as RouteTotalCustomers from CustomerMaster c left join SalesData s on c.CustomerID = s.CustomerID ) B on B.Division = A.Division and B.Route = A.Route left join ( select s.brand, c.division, c.route, PurchasedCustomersCount = count(distinct C.CustomerID) FROM CustomerMaster c JOIN SalesData s on c.CustomerID = s.CustomerID --join ItemMaster i on s.item = i.itemcode GROUP by s.brand, c.division, c.route ) C on A.Brandname = C.Brand and C.Division = A.Division and C.Route = A.Route
your query does not show all the brands in the result. Please check the sample result [HERE](
https://ethercalc.org/mmnzizii2u)
I've updated my answer with amended code - the fiddle link isn't working so I can't get to your original data.
14 People are following this question.
