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
