May 15, 2011 at 11:57 pm
I have a table that has salary data for many departments for several years (hundreds of payperiods)
(Typically each department for each pay period will have 3 Lines: Wages, Taxes, Fringe Benefits)
(today there are about 20 departments) but the number will grow.
My requirement is that I need to calculate a "Overhead amount for each department"
At the beginning of the year each department is assigned a Overhead rate (that ranges from 10% to 25%). This rate is fixed for the year.
Therefore, my query output will be the "Overhead amount" by each department for a specific time frame based on the Overhead rate. Basically the rate will multply the salaries (wages, taxes, fringebenefits) and output the overhead amount. For audit purposes I would like the output to be the actual wages, taxes, and fringe and then the Overhead amount
The overhead rate is not in any SQL table. Currently, in excel I have a list of departments and their overhead rate.
I am not sure how to approach this. Maybe I should create a table that will hold a dept and the rate value and then construct a query that will loop through each dept? I am not sure. Furthermore new departments will be added over the years.
Any assistance will be appreciated.
May 16, 2011 at 12:03 am
I would add another table, DepartmentID, Year, OverheadRate.
As for the query, no looping required. Just join the tables and filter on the year, simple as that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 16, 2011 at 12:37 am
Yes,You seem to be filling up a single table with lot of fields.Just split up the table and create new one as Gila suggested.
---
Thinking is the hardest work there is, which is the probable reason so few engage in it.
Sunitha
😎
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply