SQL code required for updating Total columns

I have a file which I have imported in a SQL 2005 table called StaffTime.
The data in this table lists the working times for each staff along with a column for the total work time and total break time.

In the table, there is a row dedicated to each employee. On the employee row there could be
up to 50 columns of time data going from Time1 to Time50.
Each Time column holds the working time for the employee. There are are also certain letters used within a Time column to identify the type of activity involved as follows;
S means Start Time
E means End Time
B means Break Time

I have attached an Image file as an example where only 5 Time columns are used.

In the attached example Total Work Time of 6 Hours is calculated by adding the times together from each Time column
In the attached example Total Break Time of 30 Minutes is calculated by identifying where a Break Time occurs and subtracting this time from the Start Time in the following Time column and adding this Break Time to all other Break Times.

(In the attached example, I also added sections called Total Work Time Detail and Total Break Time Detail just to attempt to explain how the Total values are obtained)

Currently in this table, I have each Time column populated (please note there could be up to 50 Time columns on each Row).
However, I do not have the total time values for both the Total Work Time column and the Total Break Time column.


Can you provide me with the SQL code for updating both the Total Work Time and Total Break Time columns for each employee row?

Example.JPG
dm7733Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cyberkiwiCommented:

create function parsetime(@t varchar(4)) returns int as
begin
return case when isnumeric(@t) = 1 and not @t like '%E%' then (@t / 100) * 60 + (@t % 100) else null end
end
GO
create function split_times
(
@values varchar(max)
) returns @res table ([type] char(1), [value] int)
as
begin
declare @value varchar(4096)
declare @Bpos int, @Epos int, @Spos int, @firstpos int, @lastpos int, @type char(1)
set @firstpos = 0
--
select @lastpos = @firstpos, @Bpos = charindex('B', @values),
	@Epos = charindex('E', @values),
	@Spos = charindex('S', @values)
set @firstpos = case when @BPos >0 and @EPos >0 and @BPos< @EPos then @BPos
	when @EPos >0 then @EPos else @BPos end
set @firstpos = case when @SPos >0 and @firstpos >0 and @SPos< @firstpos then @SPos
	when @firstPos > 0 then @firstPos else @firstpos end
select @type = case @firstpos when @bpos then 'B' when @epos then 'E' else 'S' end
--
while @firstpos > @lastpos
begin
	select @value = substring(@values, @firstpos+1, 4)
	insert @res select @type, dbo.parsetime(@value)
	--
	select @lastpos = @firstpos, @Bpos = charindex('B', @values, @lastpos+1),
		@Epos = charindex('E', @values, @lastpos+1),
		@Spos = charindex('S', @values, @lastpos+1)
	set @firstpos = case when @BPos >0 and @EPos >0 and @BPos< @EPos then @BPos
		when @EPos >0 then @EPos else @BPos end
	set @firstpos = case when @SPos >0 and @firstpos >0 and @SPos< @firstpos then @SPos
		when @firstPos > 0 then @firstPos else @firstpos end
	select @type = case @firstpos when @bpos then 'B' when @epos then 'E' else 'S' end
	--
end
delete @res where [value] is null
return
end
GO

-- create the two functions above, then run this query
declare @employee table (employee varchar(100), staffid int,
	time1 varchar(20),time2 varchar(20),time3 varchar(20),time4 varchar(20),
	time5 varchar(20),time6 varchar(20),time7 varchar(20),time8 varchar(20)
)
insert @employee select 'john', 12345,
	'S0905 - B1045', 'S1105 - E1145', 'S1205 - B1245', 'S1405 - B1535',
	'S1545 - E1715', null, null, null
;

-- this is actual query, the temp table is created just for illustration
select e.employee, D.Work, D.[Break]
from @employee e
cross apply
(
	select
	  [Work]=sum(case when A.[Type]='S' and C.[Type] in ('B','E') then C.Value - A.Value else 0 end),
	  [Break]=sum(case when A.[Type]='B' and C.[Type]='S' then C.Value - A.Value else 0 end)
	from dbo.split_times(
		isnull(time1,'')+isnull(time2,'')+isnull(time3,'')+isnull(time4,'')+
		isnull(time5,'')+isnull(time6,'')+isnull(time7,'')+isnull(time8,'')) A
	cross apply (
		select top 1 * from dbo.split_times(
			isnull(time1,'')+isnull(time2,'')+isnull(time3,'')+isnull(time4,'')+
			isnull(time5,'')+isnull(time6,'')+isnull(time7,'')+isnull(time8,'')) B
		where B.value > A.value
		order by B.value
	) C
) D

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cyberkiwiCommented:
Go add isnull as many times as you need, or use dynamic sql to generate it up to 50 times.
dm7733Author Commented:
Excellent!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.