count having question.

Hello,
I have a table which has 3 columns,
[dispatcher], [dueDate],[Delivered]
I am trying to write a query which tells me which dispatchers delivered
their package on time
So I have,
select dispatcher, count(datedif(d,dueDate, Delivered)) as [onTime],
count(datedif(d,dueDate, Delivered)) as [Late]
from table
group by dispatcher
having some magical code goes here
And I need to create a table like
[dispatcher] [ontTime] [Late]
Mario - 10 - 20
Luis - 32 - 10
Is there a way to do this without havving to use multiple queries and
temporary tables?
ThanksTry the following, untested:
select dispatcher,
(select count(*)
from table t2
where t2.dispatcher = t.dispatcher
and datediff(d,dueDate, Delivered) = 0) as [onTime],
(select count(*)
from table t2
where t2.dispatcher = t.dispatcher
and datediff(d,dueDate, Delivered) > 0) as [Late]
from table t
group by dispatcher
Q
"Marcelo" wrote:

> Hello,
> I have a table which has 3 columns,
> [dispatcher], [dueDate],[Delivered]
> I am trying to write a query which tells me which dispatchers delivered
> their package on time
> So I have,
> select dispatcher, count(datedif(d,dueDate, Delivered)) as [onTime],
> count(datedif(d,dueDate, Delivered)) as [Late]
> from table
> group by dispatcher
> having some magical code goes here
> And I need to create a table like
> [dispatcher] [ontTime] [Late]
> Mario - 10 - 20
> Luis - 32 - 10
> Is there a way to do this without havving to use multiple queries and
> temporary tables?
>
> Thanks
>
>|||An alternate method might be:
/*Assumes that early delivery (delivered before duedate) is considered
to be OnTime
*/
SELECT dispatcher,
onTime = SUM(CASE WHEN DATEDIFF(d, dueDate, Delivered) <= 0
THEN 1 ELSE 0 END),
Late = SUM(CASE WHEN DATEDIFF(d, dueDate, Delivered) > 0
THEN 1 ELSE 0 END)
FROM table t
GROUP BY dispatcher
Untested.
Stu|||which would be faster?
I have the same pattern as yours that is "select sum(case...)" but it's
becoming quite slow as the number of row grows.
Will the solution from Q run faster and if so, some explanations would
certainly help me understand better for next time.
Thx.
Stu wrote:
> An alternate method might be:
> /*Assumes that early delivery (delivered before duedate) is considered
> to be OnTime
> */
> SELECT dispatcher,
> onTime = SUM(CASE WHEN DATEDIFF(d, dueDate, Delivered) <= 0
> THEN 1 ELSE 0 END),
> Late = SUM(CASE WHEN DATEDIFF(d, dueDate, Delivered) > 0
> THEN 1 ELSE 0 END)
> FROM table t
> GROUP BY dispatcher
> Untested.
> Stu
>|||Christian, are you the original poster? If so, you changed your logon,
so I am uncertain. I typically use the syntax that I provided because
its cleaner, and it performs well for me. Since I don't have access to
the data (and the indexes), I can't really test it to see; however, if
you're the original poster, you can (and then you can let me know which
performs better).
Do you know how to use Execution plans in Query Analyzer? That'll go a
long way to helping you determin which syntax is best.
Stu