Calculated Fields

juddaman posted on Monday, November 05, 2007

Hi all

One of my BOs has a calculated field - TotalHours which returns the value of (Finish - Start) - Breaks. Start, Finish and Breaks are all stored in the database. However it is now requied for this "total" value to be accesbile without using a BO (for reporting using a special tool). So I have a few options:

1) Add a column to the table "Total" and store the total, this wouldn't actually be read into the object. It seams a little nasty storing a computed value but it is the easist options and quite tempting.

2) Create a SP that can return the Total - my TSQL isn't great but I'm sure this is somehow possible. Finish - Start is no problem that returns a datetime (not a timespan like in .NET). However I'm not sure how I go about taking the breaks value away from the resulting datetime. My other issue with this method is business logic in the DB.

3) Create a script that uses BLL to get all the data from the DB (start,finish,breaks) caluclates the total and uploads this to  a "special" temp table for reporting. This script could be ran before doing any reporting. This just seams to make things more complicated for the user (my boss) trying to make reports.

Any thoughts on the above?

Thanks,
George

webjedi replied on Monday, November 05, 2007

Since you are taking the BO off the table in this special reporting tool it sounds like you are going to have to do some logic in the DB.  If it's simple logic it's not a big deal really calculating the total minutes worked could be handled in a simple stored procedure (depending on how breaks are stored in the db)

Are start time, end time, break 1 start, break 1 end, break 2 start, break 2 end all in the same row or is there a start time and end time each in it's own record?

 

juddaman replied on Monday, November 05, 2007

Hey! Thanks for the reply.

Each is in its own record. There can be multiple shifts per day per employee. What I really need is a value that indicates the total working time for each 'record' (finish - start - breaks). In my BO this value is a TimeSpan, if I were to store this value in the db it would be a bigint. So if I can calculate the "bigint value" in the SP that would be okay. If you can tell me how I can do that that would be very helpful :D

Start and Finish are SQL Server type: datetime
Breaks is SQL Server type int

A resulting datetime is no good as SQL Server can't do SUM() on datetimes. So I can get the total for the day that way. So I guess a inegeral type like bigint would be the most appropriate type for total hours per slot (record).

Thanks
George

webjedi replied on Monday, November 05, 2007

A datediff should help...I'd calculate minutes and then convert to hours

so saying your table (test) looks something like this:

employeeid int
startdate datetime
enddate datetime
breaks int

for employeeid=123

suppose you have this data in your tables:

EmployeeID   StartDate                           EndDate                              Breaks

123                2007-01-01 08:00:00.000 2007-01-01 10:00:00.000 10
123                2007-01-02 08:00:00.000 2007-01-02 10:00:00.000 10

select sum((datediff(mi,startdate,enddate)-breaks)) as totalminutesworked from test where employeeid=123

You'd get totalminutesworked=220 (you can do the math in sql or your BO to get your hour value)

2 shifts of 120 minutes each minus 10 minutes of breaks.

Obviously you'll want to expand the query to use a given date range though not just all of the records for an employee.

webjedi replied on Monday, November 05, 2007

I just re-read and saw you wanted to calculate each shifts totalminutes worked...(and I gomered the from statement)

Assume the above table has a shiftid that's an int:

use this instead

select sum((datediff(mi,startdate,enddate)-breaks)) as totalminutesworked from test where employeeid=123 group by shiftid

That will give you 2 rows (one for each shift) with the totalminutes worked.

juddaman replied on Wednesday, November 07, 2007

Hey! Thank you very much. datediff was the answer! Using that gave me the different between the values aka the result I get by just using subtract (on two DateTime objects) within C#. So I was able to achieve what I wanted. It took me 7 Views too get the report data that I wanted though. Do you think it is every correct to store values that can be calculated?

webjedi replied on Wednesday, November 07, 2007

I would tend to say don't store a calculated field (especially one of this nature)...because it's not a value you want to trust...you can never be certain that one of the start times hasn't been changed after the field was calculated and the total was stuffed into the DB.  That's just my 2 cents...plus it saves on space in the DB and the I/O.

juddaman replied on Thursday, November 08, 2007

Hi! I do tend to agree. Though I can think a few examples where maybe you would store a calculated field. One being AccountBalance... would you really suggest calculating a field like this? I.e. inspecting all the transaction amount on an account to calculate current balance?

JoeFallon1 replied on Friday, November 09, 2007

Another example of a calculated field that should probably be stored is the inventory on hand quantity.

Joe