Aggregates and avoiding iteration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Aggregates and avoiding iteration

Author

Topic

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-07-12 : 11:58:44

Hi,

Hi this article was close to what I needed so I tried to apply it to what I was doing.

http://www.sqlteam.com/item.asp?ItemID=6692

Hours Worked table

IDx int Identity
[Period Ending] smalldatetime
[Job #] char
[Sequence #] int
[Std Hrs] float
[Ot Hrs] float
[DT Hrs] float
[Total Hrs] char
[Shift Prem] char
Old char
SubCode int

The [Shift Prem] is a flag.

If the field is null then
day shift
else
afternoon or night shift

[Period Ending] will eventually be a variable passed in (ie. ADO command Parameter collecton)

Returns all non shift premium hours

SELECT a.[Employee #], Sum([Std Hrs]) as notshiftpremtotal
FROM [Hours Worked] a
WHERE a.[Shift Prem] is Null AND
a.[Period Ending] >= '10/05/2001' AND
a.[Period Ending] <= '10/11/2001'
Group by a.[Employee #]

Employee # notshiftpremtotal
----------- -------------------------------
3510045 51.5
3510366 42.75
3520180 8.0
3510010 54.0
3510133 40.0
3510260 57.5
3510052 44.75
3510074 16.25
3520288 52.5
3520245 8.0......................

______________________________________________________________________

Returns all non shift premium hours

SELECT b.[Employee #], Sum([Std Hrs]) as shiftpremtotal
FROM [Hours Worked] b
WHERE b.[Shift Prem] is NOT Null AND
b.[Period Ending] >= '10/05/2001' AND
b.[Period Ending] <= '10/11/2001'
Group by b.[Employee #]

Employee # shiftpremtotal
----------- -------------------------
35100240 44.5
35100260 40.0
35100430 38.0
35100470 39.0
35100580 40.0
35100610 42.5
35100690 39.0
35100740 34.0
35100810 32.25
35100860 46.5
35101050 18.0
35101120 26.0
35101240 37.5
35101520 28.25
35101740 39.75
35102070 26.0........................

____________________________________________________________________________

Put the two together

Select distinct [Hours Worked].[Employee #], c.notshiftpremtotal, d.shiftpremtotal
FROM [Hours Worked]
JOIN (SELECT a.[Employee #], Sum([Std Hrs]) as notshiftpremtotal
FROM [Hours Worked] a
WHERE a.[Shift Prem] is Null AND
a.[Period Ending] >= '10/05/2001' AND
a.[Period Ending] <= '10/11/2001'
Group by a.[Employee #]) as c
ON [Hours Worked].[Employee #] = c.[Employee #]
JOIN (SELECT b.[Employee #], Sum([Std Hrs]) as shiftpremtotal
FROM [Hours Worked] b
WHERE b.[Shift Prem] is NOT Null AND
b.[Period Ending] >= '10/05/2001' AND
b.[Period Ending] <= '10/11/2001'
Group by b.[Employee #]) as d
ON [Hours Worked].[Employee #] = d.[Employee #]

Employee # notshiftpremtotal shiftpremtotal
----------- ------------------ -------------------
3510024 8.0 44.5
3510026 8.0 40.0
3510043 8.0 38.0
3510047 8.0 39.0
3510058 8.0 40.0
3510061 8.0 42.5
3510069 8.0 39.0
3510074 16.25 34.0
3510081 24.0 32.25
3510086 8.0 46.5
3510105 30.5 18.0
3510112 22.0 26.0
3510124 8.0 37.5

_________________________________________________________________________________________________

<HOMER VOICE> !doh! </HOMER VOICE>

That isn't what I wanted! This returns just the employees have worked both shifts within the week
I need the Employee list to be complete at each stage like
(Select distinct [Hours Worked].[Employee #] from [Hours Worked]) and the
aggregate SUMs, notshiftpremtotal and shiftpremiumtotal to be zero if there is no value.
But the conditional on the [shift prem] flag has to be put somewhere and I got mixed up.

How to fix?

edit for group by b. vs a. in second select, it was wrong

Edited by - Sitka on 07/12/2002 13:33:00

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-12 : 12:14:23

The CASE expression, once again, to the rescue:

SELECT b.[Employee #],
Sum(CASE WHEN b.[Shift Prem] IS NOT NULL THEN [Std Hrs] ELSE 0 END) as shiftpremtotal,
Sum(CASE WHEN b.[Shift Prem] IS NULL THEN [Std Hrs] ELSE 0 END) as notshiftpremtotal
FROM [Hours Worked] b
WHERE b.[Period Ending] >= '10/05/2001' AND
b.[Period Ending] <= '10/11/2001'
Group by a.[Employee #]

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-07-12 : 13:13:50

Like a 69 Polara Ragtop [url]http://www.moparpicturebook.com/newpage202.htm[/url]

**Clean!!!...**

Thanks so much.
More than this specific solution,
was a committment to search out the non cursor way.
Got a little off track there, (wow...look out...big clumsy drunk guy..comin thru..

)
The failure came in not having experience/skills
with uses of CASE. Gonna try and grind it home now, with some extra
study. Hope it sticks and other things will just have to wait.

What was the time on that reply? ~6 minutes 45 seconds. Unreal.
Thanks again.

Edited by - Sitka on 07/12/2002 13:29:03

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources