Post reply

Finding Sum of different column...

  • 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."--

  • October 27, 2005 at 1:53 am

    #600484

    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

  • October 27, 2005 at 2:13 am

    #600490

    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

  • October 27, 2005 at 2:21 am

    #600492

    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."--

  • October 27, 2005 at 2:43 am

    #600496

    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

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

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