Home Forums SQL Server 7,2000 T-SQL Query Help - Adding a column that sums other columns RE: Query Help - Adding a column that sums other columns

  • 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