subqueries and/or multiple where clauses? 1

[marduk813]

This may be somewhat of a newbie question, but I'm having a hard time wrapping my head around it. I have a table that contains several columns including: Name, Type, Qty, Response_Time, and Complete_Time. Name is the name of the person, Type is a type of work "unit", Qty is the number of work units of a particular type, and the two time columns are total times (in number of seconds) for each quantity of units.

Name Type Qty Response_Time Complete_Time
Bob A 20 600 800
Bob B 30 700 1000
Bob C 40 800 1200

(There will be more than 3 types of work, but I only need A, B, and C.)

I need to do a calculation to find the average amount of time spent per unit per person, but there's a catch: the numerator of the equation is the sum of the total times of 3 types, but the denominator is the sum of the quantities of only 2 types. So basically:

Sum(Response_Time) Where Type IN (A,B,C)
divided by
Sum(Qty) Where Type IN (A,B)

I know it's an incorrect average, but unfortunately that's the way the boss wants it.

I'm pretty sure I can do this with a cursor, but I wanted to avoid that if possible. Just not sure how else to go about it.

Any suggestions?

TIA,

Jas

 

[monksnake]

I know it's an incorrect average, but unfortunately that's the way the boss wants it.

I don't think he has a clue, but that's just me.


<.

 

[monksnake]

There will be more than 3 types of work, but I only need A, B, and C
......
but the denominator is the sum of the quantities of only 2 types

So if a person has 5 types of work, you still divide by 2 types of work??

(Referring to your example above)
Which 2 types? A, B
B, C
A, C
Did you pick A and B randomly?

<.

 

[mrdenny]

Since we can't do much in here about a dumb boss, here's the SQL to your problem.

select sum(case when type in ('A', 'B', 'C') then Response_Time else 0 end) / sum(case when type in ('A', 'B') then Complete_Time else 0 end)
from ...

This will throw errors when the sum of types A and B is 0. You will probably want to wrap the entire thing in another CASE statement checking for the sum of A and B values = 0 and drop in a 0 when it does.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[marduk813]

No, I will always need A and B specifically (for quantities), and I will always need A, B, and C for times.

I just mentioned the possibility of more work types in case that would affect the query. There will more than likely be as many as 6 or 7 types, but I'm only concerned with A, B, and C.

 

[marduk813]

Thanks mrdenny! I will give this a shot and let you know how it works out.


Jas

 

[r937]

Sum(Response_Time) Where Type IN (A,B,C)
divided by
Sum(Qty) Where Type IN (A,B)

this isn't necessarily as silly as it seems


suppose A and B are profit-generating tasks, while C is an internal or overhead task

then this ratio would indicate how productive towards the bottom line each employee is

managers love stuff like that

r937.com | rudy.ca

 

[monksnake]

No, I will always need A and B specifically (for quantities), and I will always need A, B, and C for times.

I just mentioned the possibility of more work types in case that would affect the query. There will more than likely be as many as 6 or 7 types, but I'm only concerned with A, B, and C.

Understood, I guess I was the minority since everyone else already understood and gave you an answer.

<.

 

[marduk813]

Awesome! mrdenny's code works like a charm. Thank you very much! And thanks monksnake and r937 for your input too!

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com