Return to Question

We collect customer surveys and one of them is the dreaded "How likely are you to recommend our service?" question from which we calculate a Net Promoter Score. (I hate this calculation, but I'll leave that discussion for a different time.)

We collect customer surveys and one of the questions is the dreaded "How likely are you to recommend our service?" question from which we calculate a Net Promoter Score. (I hate this calculation, but I'll leave that discussion for a different time.)

• Is there a better way of doing this than UNIONing all the individual sub-queries?
• Since it's now going to be in a stored procedure where I can easily write some tSQL around it, would I be better off selecting Promoters, Detractors and Responses each in their own query then doing the math at the end?
• Would I be best served by having indices on CollectionDate, RecommendNPS, DoNotReport, and ClinicID?

• Is there a better way of doing this than UNIONing all the individual sub-queries?
• Since it's now going to be in a stored procedure where I can easily write some tSQL around it, would I be better off selecting Promoters, Detractors and Responses each in their own query then doing the math at the end?
• Would I be best served by having indices on CollectionDate, RecommendNPS, DoNotReport, and ClinicID?
• I recognize that having the output parameter listed first may be a bit unconventional, however, I've chosen to do that as I have one standard procedure that I use to call stored procedures and I decided that it would be easiest (perhaps lazy) to always have the output parameter (if there is one) be listed first. This is, likely, another refactoring that needs to be done, but is out of scope for this question.

Note: The HashValue, LoadDate, and DoNotReport columns were added after the first survey change, so they're selected as NULL for the earliest data.

I know that the first thing I can do is pull the commented code (as well as all the 0 Neutrals, in the other SELECT statements) because it's not actually necessary to know the number of Neutral responses to calculate the NPS. I was using that previously as I was checking other code to ensure I was getting correct results and for trouble shooting.

• Is there a better way of doing this than UNIONing all the individual sub-queries?
• Since it's now going to be in a stored procedure where I can easily write some tSQL around it, would I be better off selecting Promoters, Detractors and Responses each in their own query then doing the math at the end?
• Would I be best served by having indicies on CollectionDate, RecommendNPS, DoNotReport, and ClinicID?

Note: The HashValue, LoadDate, and DoNotReport columns were added after the first survey change, so they're selected as NULL for the earliest data.

I know that the first thing I can do is pull the commented code (as well as all the 0 Neutrals, in the other SELECT statements) because it's not actually necessary to know the number of Neutral responses to calculate the NPS. I was using that previously as I was checking other code to ensure I was getting correct results and for troubleshooting.

• Is there a better way of doing this than UNIONing all the individual sub-queries?
• Since it's now going to be in a stored procedure where I can easily write some tSQL around it, would I be better off selecting Promoters, Detractors and Responses each in their own query then doing the math at the end?
• Would I be best served by having indices on CollectionDate, RecommendNPS, DoNotReport, and ClinicID?