• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 172
  • Last Modified:

Coding for summary based on a calculated field

I am retrieving data out of a labor billing table.  I have fields available for hours, billing extension, and name. The only missing item is billing rate, which I can get by creating a field using billing extension / hours. I am trying to find the best way to do a single line for each employee for each rate, which is that calculated field. The data would look something like this:

Bill       2 (hours)    100.00(billing extension)   50.00 (Rate)
Bill       1 (hours)       75.00(billing extension)   75.00 (Rate)
Steve   .5(hours)      30.00 (billing extension)  60.00 (Rate)

There could be many detail lines in the data that make up the single summary lines. Not sure what the best way to group these lines together - would be very easy if the billing rate was an actual field, but it is not. Simplified query attached.
select name, (reghrs) as hours, billext, (billext/reghrs) as billrate from LD

group by Name, RegHrs, BillExt

Open in new window

1 Solution
Kevin CrossChief Technology OfficerCommented:

From a performance perspective, you may consider going with a blend between normalization and storing the calculation. You can go for a computed column in SQL. It will be built off of the billext and reghrs columns, but can be indexed that you can then GROUP BY Name, Billrate.

Short of that, you can do this dynamically:
;with LDcte(name, hours, billext, billrate) as (
   select name, reghrs, billext, (billext/reghrs)
   from LD
   -- just to ensure we do not have any divide by zero errors
   where reghrs > 0
select name, sum(hours) [hours], sum(billext) [billext], billrate
from LDcte
group by name, billrate

Open in new window

Hope that helps!
charkerr6111Author Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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