Averages for different data all displayed in one row

[scoobyroo]

I have been asked to provide a sql script of the data in the attached excel spreadsheet. Someone else will be creating a sharepoint page with the formatted data. I am able to get all the data in columns A & B in a relatively simple sql statement. I am having issues with the boxed in portion. The box is based on a person's current election hours (which is in coumn B). Right now we have about five different elections a person could have (1650, 1800, 1950, etc...). The formulas to calculate the norms are listed at the end of the box. I am just not sure how to go about populating this information for each person. Do I calculate every possible norm and put into a table and then link my sql query to it. THey want one row of data per person. I am open to any suggestions
or ideas.

 

[simian336]

If your asking how to do this in Sql, then I think you need to create a table with on field for each cell in your spreadsheet. Then update each field based on the formulas in your spreadsheet.

In this case you could use calculated fields or write some queries to update the fields based on your formulas.

Billable_Hours_Worked = BHW

BHW BHW_FN BHW etc...
1793 1701 =bhw-bhw_fn etc...

Simi

 

[Emtucifor]

Note: some of your formulas refer to empty cells (see the (91) and (107) in red).

I think you should use SSIS to create the reports. Then you can put all sorts of formulas in there to your heart's content. You would just need to pull the correct values for each section.

The 1650, 1800, 1950 could be the detail sections of a table grouped by person and election.

If you have to stick with the single-row-per-person thing, well, you really haven't given enough information for us to figure out what you need. There are numbers all over the place on your spreadsheet. How are we going to figure out where that data is stored to even begin to give you an idea of how to pull it out?