Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2012
    Posts
    3

    Post Unanswered: How to get the latest temperature reading for each date in a date range?

    I have to display the last temperature reading from an activity table for all the dates in a selected date range.

    So if I select the date range from 09/01/2012 to 09/30/2012, the results should look like this:
    Date Temperature
    09/01/2012 73.5
    09/02/2012 75.2
    09/03/2012 76.3
    09/04/2012 73.3
    09/05/2012 77.0
    09/06/2012 74.5
    and so on.

    I am using this to get the dates listed:
    WITH CTE_DatesTable
    AS
    (
    SELECT CAST('20120901' as date) AS [Date]
    UNION ALL
    SELECT DATEADD(dd, 1, [Date])
    FROM CTE_DatesTable
    WHERE DATEADD(dd, 1, [Date]) <= '20120930'
    )
    SELECT [Date]
    FROM CTE_DatesTable

    How could I get the temperature if I did a sub-query here?

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Can you post the structure and sample data of activity table?

  3. #3
    Join Date
    Sep 2012
    Posts
    3
    Sorry, should've included this:

    ACTIVITY_ID____READER_ID____TEMP_UOM_TEMP_READING_ __TIMESTAMP
    1784__________0012BB9F_____F___________77.56______ ____2012-09-18 23:46:14:00
    1785__________0015FS8A_____F___________78.49______ ____2012-09-18 23:46:29:00
    1786__________0012BB9F_____F___________75.26______ ____2012-09-18 23:50:48:00
    1787__________0015FS8A_____F___________77.06______ ____2012-09-18 23:50:50:00


    For each reader, there are hundreds of activity records with the reported temperatures. For one day, I need to display the date and the last recorded reader temperature for that date.

  4. #4
    Join Date
    Sep 2012
    Posts
    3
    Also, I'm using SQL 2012

  5. #5
    Join Date
    Apr 2012
    Posts
    213
    Try something like this:

    Code:
    with CTE as
    (
        select 
            *, 
            ROW_NUMBER() OVER(PARTITION BY CAST(TIMESTAMP AS DATE) 
                              ORDER BY TIMESTAMP DESC) as RowNum
        from Activity
        where TIMESTAMP >= '2012/09/01' and TIMESTAMP < '2012/10/01'
    )
    
    select * from CTE where RowNum = 1
    Hope this helps.

  6. #6
    Join Date
    Sep 2011
    Location
    Greenville, SC USA
    Posts
    34
    I like the use of windowing as an integral part of the solution.
    If you want a listing of readers, you'll need to partition by READER_ID instead of TIMESTAMP.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •