Select Percentage Completed MS SQL
Posted on 2012-09-04
I have a table that has the following columns -
[ID], [SupplierSurveyID], [BrandID], [PartnerID], [CYearStockist],[Completed]
Partners (PartnerID) select the Brands (BrandID) that they stock and then Complete (Completed) a survey for each brand stocked.
What I want to be able to do in a single select statement is
Count all brands stocked (A)
so where SupplierSurveyID = 1 AND PartnerID = 440 AND CYearStockist = 'Y' ((PLUS 2))
Count all brands stocked where Completed = 'Y' (B)
so where SupplierSurveyID = 1 AND PartnerID = 440 AND CYearStockist = 'Y' AND Completed = 'Y' ((PLUS 1))
And then divide (B) by (A) to return the Percentage completed.
Please advise the best way to do this.