help with T-SQL on SQL Server 2005

Question

Hi there,

I have a T-SQL that works below:

SELECT WP_VTDID AS UTIL_VTDID, 
(SELECT COUNT(WP_ENGINE) FROM WAYPOINTS WHERE (WP_ENGINE = 1) AND (WP_SPEED > 0) AND WP_VTDID='L083') AS UTIL_RUN,
(SELECT COUNT(WP_ENGINE) FROM WAYPOINTS WHERE (WP_ENGINE = 1) AND (WP_SPEED = 0) AND WP_VTDID='L083') AS UTIL_IDLE,
(SELECT COUNT(WP_ENGINE) FROM WAYPOINTS WHERE (WP_ENGINE = 0) AND WP_VTDID='L083') AS UTIL_OFF
FROM WAYPOINTS
WHERE WP_VTDID = 'L083' AND WP_DATETIME BETWEEN '2009-03-13 00:00:00' AND '2009-03-13 23:59:59'
GROUP BY WP_VTDID

However i have multiple **WP_VTDID** values and i want to fetch all of data, can someone create a T-SQL command that works for multiple value? (value already on database)

PS: Just ignore the **WP_DATETIME** for now

So the result could be something like this:

---------------------------------
| UTIL_VTDID | RUN | IDLE | OFF |
---------------------------------
| L083       | 100 | 20   | 0   |
| L084       | 200 | 50   | 10  |
| L085       | 60  | 30   | 50  |
| L086       | 0   | 0    | 100 |
---------------------------------

---

found the solution, thanks to Jakob Christensen

SELECT WP_VTDID AS UTIL_VTDID, 
(SELECT COUNT(WP_ENGINE) FROM WAYPOINTS WHERE (WP_ENGINE = 1) AND (WP_SPEED > 0) AND WP_VTDID=t.WP_VTDID) AS UTIL_RUN,
(SELECT COUNT(WP_ENGINE) FROM WAYPOINTS WHERE (WP_ENGINE = 1) AND (WP_SPEED = 0) AND WP_VTDID=t.WP_VTDID) AS UTIL_IDLE,
(SELECT COUNT(WP_ENGINE) FROM WAYPOINTS WHERE (WP_ENGINE = 0) AND WP_VTDID=t.WP_VTDID) AS UTIL_OFF
FROM WAYPOINTS t
WHERE WP_DATETIME BETWEEN '2009-03-13 00:00:00' AND '2009-03-13 23:59:59'
GROUP BY WP_VTDID

Thanks,
Dels

Answer 1

You want to JOIN your nested SQL statements on the waypoints table.

This is untested but see what I've done here:

SELECT 
     WAYPOINTS.WP_VTDID AS UTIL_VTDID, 
     COUNT(UTIL_RUN.WP_ENGINE) AS UTIL_RUN
FROM WAYPOINTS
JOIN WAYPOINTS  UTIL_RUN ON
    WAYPOINTS.PKEY=UTIL_RUN.PKEY
AND (UTIL_RUN.WP_ENGINE = 1) AND (UTIL_RUN.WP_SPEED > 0)
WHERE WAYPOINTS.WP_DATETIME BETWEEN '2009-03-13 00:00:00' AND '2009-03-13 23:59:59'
GROUP BY WAYPOINTS.WP_VTDID

Just join for other values.

and substitute pkey for your primarykey field.

Answer 2

This will do the trick:

SELECT DISTINCT
WP_VTDID AS UTIL_VTDID, 
(
    SELECT COUNT(WP_ENGINE) 
    FROM WAYPOINTS 
    WHERE (WP_ENGINE = 1) 
    AND (WP_SPEED > 0) 
    AND WP_VTDID = t.WP_VTDID
) AS UTIL_RUN,
(
    SELECT COUNT(WP_ENGINE) 
    FROM WAYPOINTS 
    WHERE (WP_ENGINE = 1) 
    AND (WP_SPEED = 0) 
    AND WP_VTDID = t.WP_VTDID
) AS UTIL_IDLE,
(
    SELECT COUNT(WP_ENGINE) 
    FROM WAYPOINTS 
    WHERE (WP_ENGINE = 0) 
    AND WP_VTDID = t.WP_VTDID
) AS UTIL_OFF
FROM WAYPOINTS t