Loading ...
Sorry, an error occurred while loading the content.

Closest Data Match

Expand Messages
  • Noman Aftab
    Hi, I have table which records reading of devices. The table structure is like: Device Table: DeviceId int DeviceName varchar Data: 1 LS30 2 LS31 3 LS32
    Message 1 of 3 , Mar 17 12:39 PM
    • 0 Attachment
      Hi,
      I have table which records reading of devices. The table structure is like:

      Device Table:
      DeviceId int
      DeviceName varchar

      Data:
      1 LS30
      2 LS31
      3 LS32


      DeviceReading Table:
      DeviceId int
      ReceivedOn datetime
      Reading decimal

      Data:
      1 '2012-03-17 11:00 AM' 22.5

      2 '2012-03-17 11:01 AM' 21.5

      3 '2012-03-17 11:00 AM' 18.0

      1 '2012-03-17 11:17 AM' 34.5


      2 '2012-03-17 11:16 AM' 10.4


      2 '2012-03-17 11:34 AM' 45.6


      3 '2012-03-17 11:32 AM' 25.5



      I am looking for a query which gives result, such that if client wants to query data of every 15 minutes interval.

      The other developer wrote this query, which I do not understand much :)
      The result of this query is used in a cross tab, crystal report.

       (select 
      DEVICE_READING.Reading,
      DEVICE_READING.Received_on,
      DEVICE_READING.Device_ID
      from DEVICE_READING 
      inner join 
      (SELECT          Device_ID,
                      MIN(Received_on) AS Received_on
      FROM            (
      SELECT Device_ID, Min(Received_on) AS Received_on, Grp
      FROM
      (
      SELECT  Device_ID, Received_on,
                               isNull(NullIF (DATEDIFF(MINUTE, MIN(Received_on) OVER (PARTITION BY Device_ID), Received_on) / 15 , 0),0) + 1  AS Grp
                       FROM    DEVICE_READING 
      WHERE [DEVICE_ID] IN (74,75)
      AND [RECEIVED_ON] >= ISNULL('2012-3-14', [RECEIVED_ON])
      AND [RECEIVED_ON] <= ISNULL('2012-3-17 23:59:59', [RECEIVED_ON])

      ) A
      group by Device_ID, Grp) cteLog
      GROUP BY        Device_ID,
                      Grp) dr2 on
      DEVICE_READING.Device_ID = dr2.Device_ID and 
      DEVICE_READING.Received_on = dr2.Received_on)


      The problem with this query is that, if datetime of two records (of two different devices) differs, then two different groups are shown.

      Is it possible to round the datetime field to a nearest datetime value. For example, the desired returns result as:

      1 '2012-03-17 11:00 AM' 22.5

      2 '2012-03-17 11:00 AM' 21.5

      3 '2012-03-17 11:00 AM' 18.0

      1 '2012-03-17 11:15 AM' 34.5


      2 '2012-03-17 11:15 AM' 10.4


      2 '2012-03-17 11:30 AM' 45.6


      3 '2012-03-17 11:30 AM' 25.5



       

      Best Wishes,
      Noman Aftab

       
      http://corpus.quran.com/wordbyword.jsp


      [Non-text portions of this message have been removed]
    Your message has been successfully submitted and would be delivered to recipients shortly.