Right Outer Join with Where clause

Using SQL Server 2005

I am reporting on a system with 32 devices, each of these devices can have certain events that happen to it that are logged and timestamped.
I need a to show the count of each events that have happened to it within a certain time period.
This code snippet below works fine BUT if there are no events that happen to a certain device in the time period, then that device is 'missing' from the table.
What I need is basically a row for every device, regardless of if it has had any events happen to it (I will just show '0' for the event count)
Any thoughts? I'm a complete newbie at this by the way.

Thanks

Code Snippet

SELECT DeviceStatusWords.DeviceName, COUNT(DeviceEventDurationLog.StatusBit) AS BitCount, DeviceEventDurationLog.StatusBit AS Bit
FROM DeviceEventDurationLog RIGHT OUTER JOIN
DeviceStatusWords ON DeviceEventDurationLog.DeviceID = DeviceStatusWords.DeviceID
WHERE (DeviceEventDurationLog.TimeIn > @.StartDate) AND (DeviceEventDurationLog.TimeIn < @.EndDate)
GROUP BY DeviceStatusWords.DeviceName, DeviceEventDurationLog.StatusBit
ORDER BY DeviceStatusWords.DeviceName

Here it is,

Code Snippet

select

devicestatuswords.devicename,

count(deviceeventdurationlog.statusbit) as bitcount,

deviceeventdurationlog.statusbit as bit

from

deviceeventdurationlog

right outer join devicestatuswords

on deviceeventdurationlog.deviceid = devicestatuswords.deviceid

and (deviceeventdurationlog.timein > @.startdate)

and (deviceeventdurationlog.timein < @.enddate)

group by

devicestatuswords.devicename,

deviceeventdurationlog.statusbit

order by

devicestatuswords.devicename

|||

It looks to me like you need to join this stuff to a "DEVICE" table or something similar that lists all "DeviceID" entries. If you will take the "DEVICE" table (or similar) and left join to the "DEVICE" table the results of this query you should have what you are after.

|||

I believe the reason you're missing rows is because the engine is fulfilling the FROM clause first, and then filtering out rows based upon the WHERE clause. In the following example, the engine will do all the filtering at the same time and so the RIGHT join maintains the records in the DeviceStatusWords table.

Code Snippet

....

FROM DeviceEventDurationLog RIGHT OUTER JOIN
DeviceStatusWords ON DeviceEventDurationLog.DeviceID = DeviceStatusWords.DeviceID
AND (DeviceEventDurationLog.TimeIn > @.StartDate) AND (DeviceEventDurationLog.TimeIn < @.EndDate)

.....

HTH!

|||The DeviceStatusWords table is the table that contains the list of devices. That's why I needed the RIGHT OUTER JOIN to select all devices from the DeviceStatusWords table. I just couldn't fiugure out the exact syntax - the previous reply was just what I was looking for though
- thanks guys for once again educating me!sql