x

Automatic calculation problem

I have two tables called TimeCardHours and Employee. Below are the structure of those.

create table TimeCardsHours
(
TimeCardDetailID int identity (1,1) constraint pkTimeCardDetailID primary key,
TimeCardID char (10),
DateWorked datetime not null ,
ProjectID char (10),
WorkDescription varchar (100),
BillableHours int constraint chkBillableHours check (BillableHours>0),
TotalCode int,
WorldCodeID char (10)
)

create table Employee
(
EmployeeID int identity (1,1) constraint pkemployeeid primary key,
FirstName char (10) not null,
LastName char(10),
Title char (10) constraint chktitle check (title in ('Trainee','Team Member','Team Leader','Project Manager','Senior Project Manager')),
Phone char (20) not null constraint chkemployeephone check (phone like ('([0-9],[0-9],[0-9])-([0-9],[0-9],[0-9])-([0-9],[0-9],[0-9],[0-9])')),
BillingRate int  constraint chkbillingrate check (billingrate>0)
)

Now I have a question like below:

The total cost in TimeCardHourse should be automatically calculated using the formula (Billable hours * Billing rate for the employee to whom the time card is issued.)

I gave a big try to do this, but I failed. please help me..Please..
more ▼

asked Jul 23, 2010 at 12:58 AM in Default

sepala gravatar image

sepala
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

OK. Computed Column doesn't appear to do what you need (a sub-query).

Other options:

  1. Trigger. When details are inserted / updated into the TimeCardHours, the trigger calculates the TotalCost value
  2. Control access using a [stored procedure][3], and have that calculate the TotalCost.

I would probably go for the Trigger in this situation, just to ensure that the calculations are performed as they should be during normal operations (assuming normal operations doesn't include bulk loading this table).

You should also consider putting a "billed" field on the TimeCardHours table, so that the trigger knows not to update the costs on that one...

[3]: http://msdn.microsoft.com/en-us/library/ms187926.aspx
more ▼

answered Jul 23, 2010 at 01:14 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.9k 18 20 44

@Grant - thanks for the Thumbs-Up! That pushed me over 1k rep...
Jul 23, 2010 at 03:49 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

I would suggest moving the BillingRate into a separate table with ValidFrom and ValidTo to cover the possibility of a changing BillingRate.

I prefer the view to a trigger/sproc, as a calculated value like this can change so often.

Another possibility is storing the billing rate in the TimCardsHours table directly with the BillableHours and then creating a computed column for that.

CREATE TABLE TimeCardsHours
    (TimeCardDetailID int IDENTITY(1, 1)
                          CONSTRAINT pkTimeCardDetailID PRIMARY KEY,
     TimeCardID char(10),
     DateWorked datetime NOT NULL,
     ProjectID char(10),
     WorkDescription varchar(100),
     BillableHours int CONSTRAINT chkBillableHours CHECK (BillableHours > 0),
     BillingRate int CONSTRAINT chkbillingrate CHECK (billingrate > 0),
     TotalCost AS (BillableHours * BillingRate),
     TotalCode int,
     WorldCodeID char(10))

CREATE TABLE Employee
    (EmployeeID int IDENTITY(1, 1)
                    CONSTRAINT pkemployeeid PRIMARY KEY,
     FirstName char(10) NOT NULL,
     LastName char(10),
     Title char(10) CONSTRAINT chktitle CHECK (title IN ('Trainee', 'Team Member', 'Team Leader', 'Project Manager', 'Senior Project Manager')),
     Phone char(20) NOT NULL
                    CONSTRAINT chkemployeephone CHECK (phone LIKE ('([0-9],[0-9],[0-9])-([0-9],[0-9],[0-9])-([0-9],[0-9],[0-9],[0-9])')))

SELECT  *
FROM    TimeCardsHours t
INNER JOIN Employee e ON t.EmployeeID = e.EmployeeID
more ▼

answered Jul 23, 2010 at 01:41 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

That's why I like these questions - you get several different options to think about, and it shows how different approaches can be used.

All in, this place is a great place to learn!
Jul 23, 2010 at 01:43 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

As an evil way to do this, you could put the calculation in a user defined function and call that as part of your computed column. However, this would be entirely evil when used in a large data scenario. Functions are bad enough for performance, let alone ones which do data lookups.

But, it is an option.

/*
 * Scalar Function: fn_ExampleScalarLookupFunction 
 * Created By: Matthew.Whitfield
 */
CREATE FUNCTION [dbo].[fn_ExampleScalarLookupFunction ](@value int)
  RETURNS int
AS
BEGIN
  RETURN (SELECT SUM([TestColumn2]) FROM test2 WHERE [TestColumn] = @value)
END
more ▼

answered Jul 23, 2010 at 06:50 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

Too true RE evil! Beelzebub would be proud ;-)
Jul 23, 2010 at 06:56 AM WilliamD
(Irrelevant mental meandering - I now have Queen's Bohemian Rhapsody in my head. And I've got a three hour drive to get home, with no CD / MP3 player. I'll just have to see what the BBC can offer up...)
Jul 23, 2010 at 07:31 AM ThomasRushton ♦
Thomas, so it'll be a pretty "Wayne's Worldy" drive for you...
Jul 23, 2010 at 07:39 AM WilliamD

:-) A certain amount of headbangery - the music sampled ranged from a Mozart string quartet through to some popular young musicians scouting for girls. Or something. I'm getting old, you know...

(BBC Radios 3 & 2. Got home before I needed to switch to BBC Radio 4 for "The Now Show")
Jul 23, 2010 at 12:00 PM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

you can create a view for this

select <specify the required column>, total_cost = t.BillableHours * e.BillingRate
from   TimeCardsHours t
inner join Employee e on t.EmployeeID = e.EmployeeID
more ▼

answered Jul 23, 2010 at 01:07 AM

Squirrel gravatar image

Squirrel
2.3k 1 2 4

What if the billing rate for an employee changes?
Jul 23, 2010 at 01:15 AM ThomasRushton ♦
that is for sepala to answer. It really depends on what is the business rule
Jul 23, 2010 at 04:20 AM Squirrel
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x25
x3

asked: Jul 23, 2010 at 12:58 AM

Seen: 1283 times

Last Updated: Jul 23, 2010 at 01:41 AM