Getting Weighted Average within a category with multiple sub categories

Question

Joe owns a worldwide DVD rental store. He has stores throughout the globe in different regions. Each month he likes to see if his equipment in his stores is up-to-date by finding the percentage of his inventory with a certain status of "Current," "Needs Update," and "Must Go" for each region. This helps ensure his customers have the best experience in his stores.

Bill, his assistant, wants to build a SQL query that will show Joe the percentage of equipment in each region and their status. The guy before him used an excel to do this but Bill thinks SQL and then an SSRS report that would allow Joe to select which region he wants to view would impress him. He wants to have it dynamically transition from months to quarters as the year progresses in the report. This is what Bill has been able to come up with so far:

Comparing it to the excel from before, everything is correct and his SSRS is working perfectly all except for North America, which has percentages that are slightly off. He is thinking that a weighted average for the subregions in North America would fix this to get the percentage correct because some subregions are bigger than others.

How would Bill go about calculating the weighted averages each of the regions in North America?

How would you rework this so it didn't appear to be homework? What has Bill tried to do to solve the problem? Where is that attempted query? — Paul Maxwell, Jul 09 '17 at 01:06
@programr Can post the schema of your tables ? sample data set and results? — Rohit Padma, Jul 09 '17 at 04:18

score 0 · Answer 1 · answered Jul 09 '17 at 10:35

This query looks a little like dogs breakfast. The main problems:

SELECT DISTINCT is not needed in GROUP BY queries as values are already distinct. I can understand the usage of DISTINCT in COUNTS and TOT queries provided that data in TABLEIMPORT is not normalised.
Total luck of comments explaining the intention of the query.
Variable/alias naming is very poor.

I suspect the problem with the first query is here:

QTRMNTH as (
select distinct
q.Quarter  [Month]
,q.Percents [Percents]
,d.[Equipment Status]
,d.Quarter
,d.Year
,o.SelectRegion
from data d
     inner join OWN o on o.[Region] = d.[Region]
     inner join QUARTERS q on q.Quarter = d.Quarter and q.year = d.Year and q.[Equipment Status] = d.[Equipment Status]
where d.[Month] < DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) - 1, -1) and   d.[Month] > dateadd(MONTH,-12,GETDATE())

I assume the above code is supposed to return quarterly results per "greater region" [SelectRegion] based on monthly data (data). If this is the case then you need to remove the DISTINCT and add the GROUP BY.

I think it would also be helpful to remove DISTINCT from all other queries to be able to spot other hidden issues.

Getting Weighted Average within a category with multiple sub categories

1 Answers1