Query Help - Adding a column that sums other columns

Question

Query Help - Adding a column that sums other columns

Bob Bridges-173967

SSCrazy

Points: 2094
More actions
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

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

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

PBirch SSCrazy Eights Points: 8493 More actions · Answer 1

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

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)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

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