aggragate question

Question

Post reply

aggragate question

Daryl AZ

Hall of Fame

Points: 3087
More actions
March 14, 2007 at 11:05 am

#172458

I have a part that goes through a sequence of status's. I am looking to simplifiy my stored procedure.
the SQL below works, Is there another method for retrieving startWeight instead of the case statement? How efficient is a case statement in the sql below?
Select job.name job, sequence.name sequence
, count(*) TotalCount , sum(part.weight) TotalWeight
, count(start.id) StartCount
, sum(case when start.ID is not null then part.weight else 0 end) startWeight
, count(inspect.id) InspectCount
, count(ship.id) ShipCount
From job
INNER JOIN part ON..
INNER JOIN sequence ON..
LEFT JOIN (select distinct partID from status where status = start) start ON..
LEFT JOIN (select distinct partID from status where status = inspect) inspect ON..
LEFT JOIN (select distinct partID from status where status = ship) ship ON..
WHERE job = 'j1'
Group By ...
thanks

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

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

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 1

Hard to tell with the criteria for the "ON" clauses missing...

--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)

Daryl AZ Hall of Fame Points: 3087 More actions · Answer 2

The actual sproc was quite a bit more involved but I used the same type of logic below, specifically the case logic. I did not notice a performance hit.

I added the ... missing details below.

Select job.name job, sequence.name sequence

, count(*) TotalCount , sum(part.weight) TotalWeight

, count(start.id) StartCount

, sum(case when start.ID is not null then part.weight else 0 end) startWeight

, count(inspect.id) InspectCount

, count(ship.id) ShipCount

From job

INNER JOIN part ON part.jobid = job.id

INNER JOIN sequence ON part.sequenceID = sequence.id

LEFT JOIN (select distinct partID from status where status = start) start ON part.id = start.partid

LEFT JOIN (select distinct partID from status where status = inspect) inspect ON part.id = inspect.partid

LEFT JOIN (select distinct partID from status where status = ship) ship ON part.id = ship.partid

WHERE job = 'j1'

Group By job.name job, sequence.name sequence

thanks

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

I'd likely make it use a few more CASE statements to keep from having to dip the Status table 3 times in the FROM clause... crosstab code is VERY fast if you let it be...

--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)