Total duration

Cartillo
Cartillo used Ask the Experts™
on
Hi Experts,

I would like to request Experts help. How to make sure column at "TotalDuration" will automatically sum up time value from TimeIN and TimeOUT columns. Attached the print screen of the MSsql table for Experts perusal. Hope Experts will help.

   
total.png
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rajkumar GsSoftware Engineer

Commented:
Use a trigger on INSERT OR UPDATE, which will update TotalDuration column with the calculated value (sum)

Author

Commented:
Hi RajkumarGS,

How to do this in MSSQL? Please advice
Software Engineer
Commented:
Create a TRIGGER that does this job
create trigger tgrUpdateTotalDuration
on tblYourTableName
for insert, update
as
update tblYourTableNameset TotalDuration = TimeIN + [TimeOUT] 
where id = (select id from inserted)

Open in new window


Now you no need to insert the value for TotalDuration. Insert remaining values. TotalDuration will be calculated by trigger and it updates the value for you.

Trigger triggers when there is any action that is mentioned in the trigger (See here it is insert, update)

'INSERTED' represents the row that is affected by this operation

For more details, check this link
http://www.devarticles.com/c/a/SQL-Server/Using-Triggers-In-MS-SQL-Server/

Hope this helps
Raj
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Rajkumar GsSoftware Engineer
Commented:
For your table structure, it may look like
create trigger tgrUpdateTotalDuration
on tblYourTableName
for insert, update
as
update tblYourTableNameset TotalDuration = TimeIN + [TimeOUT] 
where Total_ID = (select Total_ID from inserted)

Open in new window

Top Expert 2010
Commented:
Cartillo,

With respect, this is a very bad idea: storing derived data is one of those items on the "database don't list".

I would recommend dropping the duration column altogether and instead calculating the duration on demand.  If you must have that column, then I would recommend making it a calculated column, and thus eliminate altogether the need for a trigger.

BTW, if there is ever any possibility that your in/out can span a day boundary, your design is not going to work.  Even if it seems inconceivable now, who knows what the future may hold.  For that reason, I would recommend capturing both date and time for the in/out.

Patrick
Top Expert 2012
Commented:
>>With respect, this is a very bad idea<<
I agree 100%.  And as Patrick has mentioned if you just have to have it, than use a Computed column as in something like this:
ALTER TABLE YourTable DROP COLUMN TotalDuration
ALTER Table YourTable ADD TotalDuration as DATEDIFF(minute, TimeIn, [TimeOut])
Rajkumar GsSoftware Engineer

Commented:
Yes. Patrick and acperkins are right.

I also suggest - Instead of using trigger, you can fetch calculated TotalDuration data on demand.

@Cartillo: You may please ignore all my comments in this thread.

Raj

Author

Commented:
Hi,

I've revised my sql table, attached the print screen, hope I've followed as per guide..
total1.png
Top Expert 2010
Commented:
Cartillo,

Yes, that looks in line with my recommendations.

If you are entering the date and time in TimeIN and TimeOUT, as I recommended and as you now can do with them being datetime, then I would think that the Date column becomes obsolete and can be removed.

Patrick

Author

Commented:
Hi,

Thanks for the guide.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial