Looking for devices that have NOT been used.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Looking for devices that have NOT been used.

Author

Topic

Bill Z
Starting Member

27 Posts

Posted - 2014-12-04 : 10:40:59

I found some examples of what I thought I wanted to do but I must of screwed it up.

What do I need to change the code below to get devices NOT used?

What I have is 2 tables. PunchOrigin has time and device used today. DCMDevice has all of the devices in the network.


select left(right(INDEVICENAME,7),6) as Device, count (INDEVICENAME) as DeviceCount
from  dbo.VP_PUNCHORIGIN 
where year (eventdate) = year(getdate())
and  month (eventdate) = month(getdate())
and    day (eventdate) = day(getdate()) 
and not INDEVICENAME is NULL
and not left(right(INDEVICENAME,7),6) in (Select DEVICEID from dbo.DCMDEVICE)

group by INDEVICENAME
order by Device

What I am getting is a list of Devices not in the DEVICEID table with counts. I want, what is in the DEVICEDID table where counts are equal zero.

Here is the output I'm getting but I do not want.

Device DeviceCount
004402 36
004502 11
004703 13
006402 54
006602 12
006802 35
090503 17

Hoping to get something like:
Device DeviceCount
127002 0
900001 0
900002 0
909001 0
999001 0
999993 0

_______________________________________________________________________________
How do you measure success? Money? Career? Health? Stuff? Relationships? Success and Purpose in Life go hand in hand. If you want to fulfill YOUR purpose in life you need to find out what it is.

I do this by accomplishing GOD’S purpose for my life.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-12-04 : 11:02:47

[code]
SELECT *
FROM dbo.DCMDevice D
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.PunchOrigin P
WHERE P.eventdate >= DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
AND P.eventdate < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 1)
AND LEFT(RIGHT(P.INDEVICENAME, 7), 6) = D.DeviceId
);
[/code]

Bill Z
Starting Member

27 Posts

Posted - 2014-12-04 : 11:13:45

Thanks.

That does work! Wow!

I have not tried any code using 'WHERE NOT EXISTS' yet.

I have a new tool now.

Thanks again.

_______________________________________________________________________________
How do you measure success? Money? Career? Health? Stuff? Relationships? Success and Purpose in Life go hand in hand. If you want to fulfill YOUR purpose in life you need to find out what it is.

I do this by accomplishing GOD’S purpose for my life.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources