How to calculate business hours from log table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to calculate business hours from log table

Author

Topic

hicham4
Starting Member

5 Posts

Posted - 2012-01-31 : 07:50:04

Hi everybody,

I have a table "Log" with attr.

LogId int (PK)
LogDescription nvarchar(500)
TaskId int (FK)
Datecreated datetime

Scenario. Data entry into Log, make a new log, describe what hapend (here is a text that describe the changing of the status of a task) and when.
The description text is somthing like "Username has changed the staus of Task 2 from "New" to "Active""
But it can be also "Username has changed the staus of Task 2 from "Active" to "On Hold"" that means that nothing is done when the status is on hold.
Now i want to calculate the time (business hours) when a task was between active and resolved and only when the status active was.

Thanks a lot for any help.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-01-31 : 12:27:03

can you provide sample data with schema and all. also expected result?

If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-31 : 13:19:50

apert from desription field, is there some other field in same table or in other tables which gives the old and new status?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hicham4
Starting Member

5 Posts

Posted - 2012-02-01 : 03:21:12

quote:
Originally posted by visakh16

apert from desription field, is there some other field in same table or in other tables which gives the old and new status?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

No, but from description u can extract what the old status was: "Username has changed the staus of Task 2 from "New" to "Active"", in this case is not necessary to have some other 'History' fields, if i'm wrong please correct me!

hicham4
Starting Member

5 Posts

Posted - 2012-02-01 : 04:25:07

quote:
Originally posted by yosiasz

can you provide sample data with schema and all. also expected result?

If you don't have the passion to help people, you have no passion

Here a sample data from the Log table:


LogID 	TaskID	LogDescription	                                                    DateCreated	       UserID
4	2	User1 has changed the staus of Task 2 from "Active" to "Resolved""	2012-01-30 12:31:38.063	1
3	2	User6 has changed the staus of Task 2 from "On Hold" to "Active""	2012-01-25 12:28:56.080	6
2	2	User6 has changed the staus of Task 2 from "Active" to "On Hold"”	2012-01-21 12:20:11.130	6
1	2	User1 has changed the staus of Task 2 from "New" to "Active""	2012-01-20 11:59:53.420	1

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-01 : 09:31:34

quote:
Originally posted by hicham4

quote:
Originally posted by visakh16

apert from desription field, is there some other field in same table or in other tables which gives the old and new status?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

No, but from description u can extract what the old status was: "Username has changed the staus of Task 2 from "New" to "Active"", in this case is not necessary to have some other 'History' fields, if i'm wrong please correct me!

you can extract but the code will be a bit ugly. Also if format is not consistent, that will complicate matters!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-02-01 : 14:44:53

another option is to create a trigger that will dump data into an audit field and use that instead.

If you don't have the passion to help people, you have no passion

hicham4
Starting Member

5 Posts

Posted - 2012-02-07 : 04:42:31

quote:
Originally posted by yosiasz

another option is to create a trigger that will dump data into an audit field and use that instead.

If you don't have the passion to help people, you have no passion

Yes, maybe i have to create 3 triggers

First, to insert for the first time into audit_table
Second, to update field "ToActiveOrResolved DateTime"
Third, to update field "FromActive DateTime"

First the Audit_Table looks like this:
CREATE TABLE [dbo].[Audit](
[Id] [int] NOT NULL,
[TaskId] [int] NULL,
[Description] [varchar](500) NULL,
[FromActive] [datetime] NULL,
[ToActiveOrResolved] [datetime] NULL,
CONSTRAINT [PK_Audit] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Sample data from Log_Table
LogID TaskID LogDescription DateCreated UserID
4 2 User1 has changed the staus of Task 2 from "Active" to "Resolved"" 2012-01-30 12:31:38.063 1
3 2 User6 has changed the staus of Task 2 from "On Hold" to "Active"" 2012-01-25 12:28:56.080 6
2 2 User6 has changed the staus of Task 2 from "Active" to "On Hold"” 2012-01-21 12:20:11.130 6
1 2 User1 has changed the staus of Task 2 from "New" to "Active"" 2012-01-20 11:59:53.420 1

Any idea?
Thanks.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-02-07 : 19:27:17

I would not do

User1 has changed the staus of Task 2 from "Active" to "Resolved""

Just record the actual data values in distinct columns so that you can do smart adhoc queries and not parse text columns. otherwise you are back to square one, you took the problem from one table to another


CREATE TABLE [dbo].[LogAudit](
[Id] [int] NOT NULL,
[TaskId] [int] NULL,
[FromAction] varchar(50) ,
[ToAction] varchar(50) ,
ActionFromID or you can add a table called ActionList and use ActionID here as Foreign Key
ActionToID or you can add a table called ActionList and use ActionID here as Foreign Key
[Description] [varchar](500) NULL,
[FromActive] [datetime] NULL,
[ToActiveOrResolved] [datetime] NULL,
CONSTRAINT [PK_Audit] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

then if someone asks you "how many items changed from Active to Resolved you do not need to do parsing of a description field. your data is now smart, BI enabled. If in the future someone wants to change an ActionList table you disable it and add a new one, that way you keep history. this might be all overkill but the other approach is non sustainable/manageable

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources