[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 317
  • Last Modified:

Visual Studios 2008 and SQL, creating a specific sort of Cross Tab

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!
0
gwarcher
Asked:
gwarcher
  • 3
  • 2
1 Solution
 
rushShahCommented:
hi..

do u want something like this??

select * from yourtable where Region = 1 AND District = 'a'

if this is so,
then return the resultset to .net and bind it to gridview.
0
 
gwarcherAuthor Commented:
No, I don't need table data populated into the dataset.  

I would need to take the resuts from my stored procedures:

SELECT COUNT(*) where SEX = 'F" and Region = 1 and District = 'a'  (returns 3)
then have an all counts SELECT COUNT(*) where Region = 1 AND District = 'a' (returns 4)

I would then take results 1 (3) and result 2 (4) and find the percentage of Females in the District (and Region)  Then take that number and populate it in order to create a spreadsheet like report that would read something like this

                             Sex                 Age                      Type Purchased                amount purchased
District               M     F            18-25      25+        w1               w2                1-2               3+
1                      25%   75%        50%     50%       15%           65%                85%            15%

And so on for the regions as well.

Does that clarify things a bit?
0
 
rushShahCommented:
hi..
may be you can do something like this..in ur procedure..

SELECT (100 - F ) AS 'M', F
FROM (
SELECT ((SELECT COUNT(*) where SEX = 'F" and Region = 1 and District = 'a')
/(SELECT COUNT(*) where Region = 1 AND District = 'a') * 100)  AS 'F'
) t
0
 
gwarcherAuthor Commented:
I will certainly try that.  But at this point I am more concerned with how to properly dynamically display the results in Visual Studios, whether it be in a form, report or some sort of data grid.  I need to be able to make calculations on the district, and then the same calculations again for each respective region in the district.
0
 
rushShahCommented:
you can display data in Page may be either using data grid...as in format as you need

you can make all calculation in sql server just as format as you want to display and then retrieve it in visual studio
or in visual studio and loop through the data to make calculations..
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now