Finding Sum of different column...

Question

Post reply

Finding Sum of different column...

CrazyMe

Ten Centuries

Points: 1005
More actions
October 27, 2005 at 12:31 am

#91859

hi all,
I have task cut out for me to find the sum of different columns...let me put the scenario.
An employee would fill in timesheet for a fortnight...the table would have column
employeeno,branchcd,timesheethrs1,timesheethrs2...timesheethrs15.
I wanted to find out how the consolidated total hours worked by an employee by summing the columns timesheethrs1 - timesheethrs15.
Regards
Arun
Kindest Regards,
Arunkumar
Reputation is what other people know about you. Honor is what you know about yourself."--

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Papillon Hall of Fame Points: 3303 More actions · Answer 1

Hi!!!!

SELECT ID,BRANCHCD,SUM(ISNULL(Hrs1,0) + ISNULL(Hrs2,0) + ISNULL(Hrs3,0) + ISNULL(Hrs4,0)......) 'WORKING Hours'

FROM EMPLOYEE

GROUP BY ID,BRANCHCD

Regards,

Papillon

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Can you fix the database design? At the moment it's violating first normal form (table contains repeating groups). What happens if it's decided that a month of data needs to be stored?

Create Table Employees (

EmployeeNo ...

Branchcd ...

...

)

CREATE TABLE TimeSheets (

EmployeeNo ...

TimeSheetDate DATETIME,

HoursWorked SMALLINT

)

GO

SELECT Employees.EmployeeNo, Employees.Branchcd, Sum(HoursWorked)

FROM Employees INNER JOIN TimeSheets ON Employees.EmployeeNo = TimeSheets.EmployeeNo

GROUP BY Employees.EmployeeNo, Employees.Branchcd

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

CrazyMe Ten Centuries Points: 1005 More actions · Answer 3

Gila,

We are capturing day by day hours and not the total hours hence is the columns repeating for everyday...do let me know if i'm right.

Arun

Kindest Regards,

Arunkumar

Reputation is what other people know about you. Honor is what you know about yourself."--

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Doesn't matter how you capture the data. You've got 15 fields all storing the same thing in one table. That's bad db design. It may make the capturing easier, but reporting is harder (as you've probably noticed) and leads to problems when requirements change.

The design I suggested also stores day-by-day hours.

eg.

Employee	TimeSheetDate	Hours
1	2005-10-01	5
1	2005-10-02	8
1	2005-10-03	4
2	2005-10-01	6
2	2005-10-02	1
2	2005-10-03	10

Make sense?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass