dm7733
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
cyberkiwi
Go add isnull as many times as you need, or use dynamic sql to generate it up to 50 times.
ASKER
Excellent!