I need some direction on this.
I'm still fairly new to Visual Studios and SQL interaction. I have a large table that I will be making several different statistical calculations on and then would like to place it in either a spreadsheet like form or a report in Visual Studios. An example of my table would be as follows:
(Please note, there are more than districts than regions)
Region District Sex Age #Purchased Type Purchased
1 a M 18 2 w1
1 b F 21 3 w2
1 a F 35 3 w1
1 a F 25 1 w1
I have created several stored procedures in SQL to get the COUNT from specific fields, and in some cases with filters (COUNT(type purchased) FROM table WHERE #purchased = 2 AND SEX = M). The stored procedures are all dynamic SQL so that should take care of any variables I need to change. Everything here is working fine at the moment...
I will then use these results to bounce of the total number of records in the table to create percentages. Pretty simple stuff...
I have two problems that I am running into though.
1) I would like to run a SELECT DISTINCT DISTRICT WHERE Region = 1 type query and load it into an array. I would then like to use those values to re-run my statistics queries with an additional filter so that I can breakdown the statistics by both region and district. Is this possible?
2) I am not entirely sure what would be the best way to present the data in Visual Studios. Because of item #1, there could be 5 different districts in a region, or there could be 6 different districts, I need something scalable enough that it will add additional rows where needed.
I'm trying to be as clear as possible, even though I know it's quite vague. I'm not necessarily looking for coding here, just a direction on what would be the best way to do this.
Thanks for the help!