count having question.

9 Sep 2005 1:20 AM

Marcelo

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

9 Sep 2005 1:42 AM

Q

Try 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

Show quote

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

9 Sep 2005 2:01 AM

Stu

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

9 Sep 2005 8:23 PM

Christian Allard

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:
Show quote

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

10 Sep 2005 12:13 AM

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