CODEWITHSUNDEEP
sqlsql-server-2008sql-server-2008-r2
Mhmt
Mhmt

Reputation: 769

How can I find Offline Dealers in my table?

I have 170 Dealers in Dealer table.These Dealers sends some datas to my Data table every 10 seconds.

I want to find Online and Offline Dealers.

If Dealer readTime> DATEADD(MINUTE,-30,GETDATE()) Dealer is Online

If Dealer readTime< DATEADD(MINUTE,-30,GETDATE()) Dealer is Offline

I can find Online Dealers by this query

         Select  D1.DeviceID, D1.readTime,DL.FirmName  

         from Data D1 with (nolock) 
         inner join Dealer as DL on DL.DeviceID=D1.DeviceID 
         where D1.OID   in 

             ( 

             select MAX (D2.OID) 
             from Data D2 with (nolock)
             where D2.readTime > DATEADD(MINUTE,-30,GETDATE()) and  
             D2.readTime<getdate()
             group by D2.DeviceID


             )   ORDER BY D1.readTime DESC

I tried find Offline Dealers in second query but I get this problem : Online Dealers have datas in result of second query.Because Dealers sends data every 10 seconds.

How can I find only Offline Dealers ?

The query(change '<' to '>' ) 

        Select  D1.DeviceID, D1.readTime,DL.FirmName  

         from Data D1 with (nolock) 
         inner join Dealer as DL on DL.DeviceID=D1.DeviceID 
         where D1.OID   in 

             ( 

             select MAX (D2.OID) 
             from Data D2 with (nolock)
             where D2.readTime > DATEADD(MINUTE,-30,GETDATE()) and  
             D2.readTime<getdate()
             group by D2.DeviceID


             )   ORDER BY D1.readTime DESC

Upvotes: 0

Views: 44

Answers (2)

Mhmt
Mhmt

Reputation: 769

Solved. 

   Select  D1.DeviceID, D1.readTime,DL.FirmName  

     from Data D1 with (nolock) 
     inner join Dealer as DL on DL.DeviceID=D1.DeviceID 
     where D1.OID   in 

         ( 

         select MAX (D2.OID) 
         from Data D2 with (nolock)
         where D2.readTime > DATEADD(MINUTE,-30,GETDATE()) and  
         D2.readTime<getdate()
         group by D2.DeviceID


         )  and   DATEDIFF(MINUTE,DATEADD(MINUTE,-30,GETDATE()),S.readTime)<0

             ORDER BY D1.readTime DESC

Upvotes: 0

Rowland Shaw
Rowland Shaw

Reputation: 38129

As you've a subquery to define who is online, and you use an IN clause to find those, simply negate that clause to be a NOT IN, so something like:

     Select  D1.DeviceID, D1.readTime,DL.FirmName  

     from Data D1 with (nolock) 
     inner join Dealer as DL on DL.DeviceID=D1.DeviceID 
     where D.OID   NOT in 

         ( 

         select MAX (D2.OID) 
         from Data D2 with (nolock)
         where D2.readTime > DATEADD(MINUTE,-30,GETDATE()) and  
         D2.readTime<getdate()
         group by D2.DeviceID


         )   ORDER BY D.readTime DESC

Upvotes: 1

Related Questions