Post reply

Query Help - Adding a column that sums other columns

  • January 27, 2005 at 12:49 pm

    #164448

    Greetings to the group!

    I've got a query that is doing almost everything I want it to do... Here is the query...

    SELECT

     E.PAYTYPE

    , SUM(CASE datepart(dw,E.TimeDate) WHEN 2 THEN E.Hours ELSE 0 END) as Monday

    , SUM(CASE datepart(dw,E.TimeDate) WHEN 3 THEN E.Hours ELSE 0 END) as Tuesday

    , SUM(CASE datepart(dw,E.TimeDate) WHEN 4 THEN E.Hours ELSE 0 END) as Wednesday

    , SUM(CASE datepart(dw,E.TimeDate) WHEN 5 THEN E.Hours ELSE 0 END) as Thursday

    , SUM(CASE datepart(dw,E.TimeDate) WHEN 6 THEN E.Hours ELSE 0 END) as Friday

    , SUM(CASE datepart(dw,E.TimeDate) WHEN 7 THEN E.Hours ELSE 0 END) as Saturday

    , SUM(CASE datepart(dw,E.TimeDate) WHEN 1 THEN E.Hours ELSE 0 END) as Sunday

    FROM TimeCard.dbo.TC_HOURS   E

    WHERE E.DEPTID in ('83810DAY','83810NGT','90510DAY','90510NGT','79410DAY','79410NGT')

    AND (E.TimeDate between '01/17/2005' and '01/23/2005')

    GROUP BY E.PAYTYPE

     

    The result is a grid-like return that looks something like...

    Paytype          Mon       Tue     Wed     Thur    Fri    Sat    Sun

    Overtime           9            9         2         9        9      9       4

    Sick                  9           2         9         9        9       4       1

    etc...

    I'm trying to figure out how to add one more colum to the right that sums up the hours for that week's Paytype.

    Any pointers or assistance would be greatly appreciated!

    Bob

  • January 27, 2005 at 1:20 pm

    #539423

    Bob -

    It would be easier if we knew the table structure. It's difficult to see from the query where you return the OVERTIME and SICK, etc columns. The query you have selected only has the part where you pivot the paytype.

    Quand on parle du loup, on en voit la queue

  • January 27, 2005 at 1:38 pm

    #539428

    Try something like this.

    Select dtDates.*, dtDates.Monday + dtDates.Tuesday + ... + dtDates.Sunday) as Total from

    (SELECT

    E.PAYTYPE

    , SUM(CASE datepart(dw,E.TimeDate) WHEN 2 THEN E.Hours ELSE 0 END) as Monday

    , SUM(CASE datepart(dw,E.TimeDate) WHEN 3 THEN E.Hours ELSE 0 END) as Tuesday

    , SUM(CASE datepart(dw,E.TimeDate) WHEN 4 THEN E.Hours ELSE 0 END) as Wednesday

    , SUM(CASE datepart(dw,E.TimeDate) WHEN 5 THEN E.Hours ELSE 0 END) as Thursday

    , SUM(CASE datepart(dw,E.TimeDate) WHEN 6 THEN E.Hours ELSE 0 END) as Friday

    , SUM(CASE datepart(dw,E.TimeDate) WHEN 7 THEN E.Hours ELSE 0 END) as Saturday

    , SUM(CASE datepart(dw,E.TimeDate) WHEN 1 THEN E.Hours ELSE 0 END) as Sunday

    FROM TimeCard.dbo.TC_HOURS E

    WHERE E.DEPTID in ('83810DAY','83810NGT','90510DAY','90510NGT','79410DAY','79410NGT')

    AND (E.TimeDate between '01/17/2005' and '01/23/2005')

    GROUP BY E.PAYTYPE

    ) dtDates

  • January 27, 2005 at 7:59 pm

    #539469

    Dunno, maybe I missed something....

    SELECT

     E.PAYTYPE

    , SUM(CASE datepart(dw,E.TimeDate) WHEN 2 THEN E.Hours ELSE 0 END) as Monday

    , SUM(CASE datepart(dw,E.TimeDate) WHEN 3 THEN E.Hours ELSE 0 END) as Tuesday

    , SUM(CASE datepart(dw,E.TimeDate) WHEN 4 THEN E.Hours ELSE 0 END) as Wednesday

    , SUM(CASE datepart(dw,E.TimeDate) WHEN 5 THEN E.Hours ELSE 0 END) as Thursday

    , SUM(CASE datepart(dw,E.TimeDate) WHEN 6 THEN E.Hours ELSE 0 END) as Friday

    , SUM(CASE datepart(dw,E.TimeDate) WHEN 7 THEN E.Hours ELSE 0 END) as Saturday

    , SUM(CASE datepart(dw,E.TimeDate) WHEN 1 THEN E.Hours ELSE 0 END) as Sunday

    ,SUM(E.Hours) AS Total

    FROM TimeCard.dbo.TC_HOURS   E

    WHERE E.DEPTID in ('83810DAY','83810NGT','90510DAY','90510NGT','79410DAY','79410NGT')

    AND (E.TimeDate between '01/17/2005' and '01/23/2005')

    GROUP BY E.PAYTYPE

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • January 27, 2005 at 8:55 pm

    #539470

    No I missed that one Jeff. Thanx for the correction.

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

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