What I would like to do though is tier the results, so that any suppliers with less that 10% stockists are moved to the bottom, so the results would look like so

Stockists > 10% order by Total Desc
Stockists < 10% order by Total Desc

and have these returned in one recordset as they are currently.

To get the total number of people that participated in the survey.

I'm stuck as to how to marry these two together?

Thanks

0

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Thanks for the data, just need a little more explanation in simple term that
what Stockists is?, and
to calculate Stockists percentage from the data provided in excel how will I do it using simple maths?

Thanks RehanYousaf, ok to explain, we are a buying group for a number of retailers (stockists) each year we ask them to complete a survey on the brands that they stock, for the data returned the 'Completed' column is the total number of retailers that completed the survey, 'Stockists' is the number of retailers that stocked the brand, as they don't stock all brands.

As we don't want the figures to be offset by a brand that has a very a single very happy stockist and no other stockists. we have decided to move all brands where less (or equal) than 10% of the the total 'completed' are stockists to the bottom and then apply the normal order by.

So in Excel, the query would be = Sum('Stockists'/'Completed')*100, when i tried this in SQL, I got strange results....

Hope this helps.

62 was hard coded, as we know that to be the total brands this year, although if this could be queried as COUNT DISTINCT brandid that would be good also....

;WITH cteSupplierSurveyInput AS ( SELECT SupplierSurveyID, BrandID, SUM(Saleability) /COUNT(*) AS avgSaleability, SUM(Quality) /COUNT(*) AS avgQuality, SUM(Availability) /COUNT(*) AS avgAvailability, SUM(Delivery) /COUNT(*) AS avgDelivery, SUM(RETURNS) /COUNT(*) AS avgReturns, SUM(RetailPrice) /COUNT(*) AS avgRetailPrice, SUM(ServiceSupport) /COUNT(*) AS avgServiceSupport, SUM(Rep) /COUNT(*) AS avgRep, SUM(Accounts) /COUNT(*) AS avgAccounts, SUM(CreditControl) /COUNT(*) AS avgCreditControl, (((SUM(Saleability) + SUM(Quality) + SUM(Availability) + SUM(Delivery) + SUM([RETURNS]) + SUM(RetailPrice) + SUM(ServiceSupport) + SUM(Rep) + SUM(Accounts) + SUM(CreditControl) ) /10) / COUNT(*)) Total, COUNT(*) Stockists, SUM(CASE WHEN nextyeartrade = 'Y' THEN 1 ELSE 0 END) AS StockistsNY FROM SupplierSurveyInput S GROUP BY S.BrandID, S.SupplierSurveyID),cteSupplierSurveyCountFiltered AS( SELECT SupplierSurveyID, COUNT(*) AS SupplierSurveyCountFiltered FROM SupplierSurveyInput WHERE CYearStockist != 'U' GROUP BY SupplierSurveyID),cteSupplierSurveyCount AS (SELECT SupplierSurveyID, COUNT(*) AS SupplierSurveyCountFROM SupplierSurveyInput GROUP BY SupplierSurveyID)SELECT ROW_NUMBER() OVER(ORDER BY s.Total) AS RowNumber, *, CONVERT(FLOAT,f.SupplierSurveyCountFiltered)/c.SupplierSurveyCount * 100 AS Percentage, case when CONVERT(FLOAT,f.SupplierSurveyCountFiltered)/c.SupplierSurveyCount * 100 >= 10 then 1 else 0 end AS PercentageOrderFROM cteSupplierSurveyInput s LEFT JOIN cteSupplierSurveyCountFiltered f ON s.SupplierSurveyID = f.SupplierSurveyID LEFT JOIN cteSupplierSurveyCount c ON s.SupplierSurveyID = c.SupplierSurveyID LEFT JOIN Brand b ON s.BrandID = b.IDORDER BY PercentageOrder, Total

Thank you,. but that gives me some strange results... One question, the query below works great now, with the exception of the 'Position' column, which is no longer correct,..

select ROW_NUMBER() OVER(order by (((sum(Saleability) + sum(Quality) + sum(Availability) + sum(Delivery) + sum([Returns]) + sum(RetailPrice) + sum(ServiceSupport) + sum(Rep) + sum(Accounts) + sum(CreditControl) ) /10) / count(*)) desc) as Position, brandid,B.Name,sum(Saleability) /count(*) as avgSaleability,sum(Quality) /count(*) as avgQuality,sum(Availability) /count(*) as avgAvailability,sum(Delivery) /count(*) as avgDelivery,sum(Returns) /count(*) as avgReturns,sum(RetailPrice) /count(*) as avgRetailPrice,sum(ServiceSupport) /count(*) as avgServiceSupport,sum(Rep) /count(*) as avgRep,sum(Accounts) /count(*) as avgAccounts,sum(CreditControl) /count(*) as avgCreditControl,(((sum(Saleability) + sum(Quality) + sum(Availability) + sum(Delivery) + sum([Returns]) + sum(RetailPrice) + sum(ServiceSupport) + sum(Rep) + sum(Accounts) + sum(CreditControl) ) /10) / count(*))*20 Total,count(*) Stockists,sum(case when nextyeartrade = 'Y' then 1 else 0 end) as StockistsNY, C.Completed Completed,CASE WHEN (count(*)*100)/C.Completed <= 10 then 1Else 2 END as Tierfrom [dbo].[SupplierSurveyInput] Sinner join dbo.Brand Bon S.BrandID = B.IDLeft join(SELECT [SupplierSurveyID], COUNT([ID])/COUNT(DISTINCT(BrandID)) Completed FROM [dbo].[SupplierSurveyInput] where CYearStockist <> 'u' group by [SupplierSurveyID]) C on C.SupplierSurveyID = S.SupplierSurveyIDwhere [CYearStockist] = 'Y' AND S.SupplierSurveyID = 1 AND CountryID in (3)group by b.name, brandid, S.SupplierSurveyID, C.Completedorder by Tier desc, Total desc

use
CONVERT(FLOAT,(COUNT(*))*100)/C.Completed
instead of
(COUNT(*)*100)/C.Completed

in your script

0

Featured Post

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works

Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.