CODEWITHSUNDEEP
sqlsql-server
Shaji
Shaji

Reputation: 761

SQL Query for datetime within 5 minutes of each other

I am trying to set up a report that queries for data within five minutes of each other. This way, I can recognize that one of my machines is down. This is what I have so far:

   SELECT A_M_DEVICE.M_Device_ID,
   A_M_DEVICE.Machine_Name,
   A_M_DEVICE.IP_Address,
   A_M_DEVICE.Device_Type,
   A_M_DEVICE.M_Device_Status_ID,
   A_M_DEVICE.Status_Date,
   S_M_DEVICE_STATUS.M_Device_Status_Desc,
   A_M_DEVICE.Domain_Name FROM    MConsole.dbo.A_M_DEVICE A_M_DEVICE
   INNER JOIN
      MConsole.dbo.S_M_DEVICE_STATUS S_M_DEVICE_STATUS
   ON (A_M_DEVICE.M_Device_Status_ID =
          S_M_DEVICE_STATUS.M_Device_Status_ID) WHERE (A_M_DEVICE.Machine_Name IN
       ('DeVA',
        'DevB',
        ))
   AND (A_M_DEVICE.Status_Date = DateDiff (Second) <= 300)

Image not allowed since I am a newbie. Else I would have posted one.

Alright - looks like I have enough reputations for a screenshot! enter image description here Any help, as always, will be highly appreciated.

Thank you in advance.

Upvotes: 5

Views: 6916

Answers (2)

Ken Gregory
Ken Gregory

Reputation: 7390

Select the rows that have a status date that doesn't match the MAX status date:

SELECT A_M_DEVICE.M_Device_ID,
   A_M_DEVICE.Machine_Name,
   A_M_DEVICE.IP_Address,
   A_M_DEVICE.Device_Type,
   A_M_DEVICE.M_Device_Status_ID,
   A_M_DEVICE.Status_Date,
   S_M_DEVICE_STATUS.M_Device_Status_Desc,
   A_M_DEVICE.Domain_Name FROM    MConsole.dbo.A_M_DEVICE A_M_DEVICE
   INNER JOIN
      MConsole.dbo.S_M_DEVICE_STATUS S_M_DEVICE_STATUS
   ON (A_M_DEVICE.M_Device_Status_ID =
          S_M_DEVICE_STATUS.M_Device_Status_ID) WHERE (A_M_DEVICE.Machine_Name IN
       ('DeVA',
        'DevB',
        ))
   AND (A_M_DEVICE.Status_Date NOT IN (SELECT MAX(A_M_DEVICE.Status_Date) FROM A_M_DEVICE)

The date will be identical for all rows that represent machines that are operational - so eliminating those that match the MAX date leave records representing machines that haven't had a status update.

Upvotes: 1

dkretz
dkretz

Reputation: 37655

Here's an approximation - check for "< 5" vs. "> 5" etc.

SELECT M_Device_ID,
    Machine_Name,
    IP_Address,
    Device_Type,
    M_Device_Status_ID,
    M_Device_Status_Desc,
    Domain_Name
FROM
(   SELECT M_Device_ID,
        Machine_Name,
        IP_Address,
        Device_Type,
        M_Device_Status_ID,
        M_Device_Status_Desc,
        Domain_Name,
        MAX(Status_Date)
    FROM A_M_DEVICE
    GROUP BY
        M_Device_ID,
        Machine_Name,
        IP_Address,
        Device_Type,
        M_Device_Status_ID,
        M_Device_Status_Desc,
        Domain_Name
) AS a
JOIN A_M_DEVICE AS b
ON
     a.Machine_Name = b.Machine_Name
     AND a.IP_Address = b.IP_Address
     AND a.Device_Type = b.Device_Type
     AND a.M_Device_Status_ID = b.M_Device_Status_ID
     AND a.M_Device_Status_Desc = b.M_Device_Status_Desc
     AND a.Domain_Name = b.Domain_Name
WHERE
     DATEDIFF(minute, a.Status_Date, b.Status_Date) < 5

Upvotes: 2

Related Questions