Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2008
    Posts
    13

    Unanswered: selecting data via intervals

    I have some data in a table that has a timestamp (Date) and a value (Number). I want to look at only the last value for each day and subtract from the value the last value for the previous day to produce a running total of how much the value has increased each day. So I'd really be throwing out 99% of the data in the table and just pulling out a single value (the last one) for each day. How would I go about doing this in SQL?

    Thanks!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Post DDL for table
    Post DML for test data

    Post expected/desired results
    (Formated nicely using <code tags> as described in #1 STICKY post above)
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2005
    Posts
    57
    Sounds like:

    select trunc(timestamp), max(value) from table
    group by trunc(timestamp)

    would be a good place to start.

  4. #4
    Join Date
    Nov 2008
    Posts
    13
    Thanks for that suggestion. That definitely gives me the max value for each day, but what if the max value is not the last value for that day. And I still need to subtract the value from the previous day for the final results.

  5. #5
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    This is brute force ugly, but it works as a starting point:

    Code:
    select a.value - b.value
    from (select value
            from test
            where tstamp in
    	(select max(tstamp)
    	 from test
    	 where trunc(tstamp) = trunc(sysdate)))  a,
           (select value
            from test
            where tstamp in
    	(select max(tstamp)
    	 from test
    	 where trunc(tstamp) = trunc(sysdate-1)))  b;

  6. #6
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by Solerous
    Thanks for that suggestion. That definitely gives me the max value for each day, but what if the max value is not the last value for that day.
    Something like:
    Code:
    select distinct trunc(ts_column), 
           max(value_column) over (partition by trunc(ts_column)) as max_per_day,
           first_value(value_column) over (partition by trunc(ts_column) order by ts_column desc) as last_per_day
    from the_table
    Edit: I have just seen your comment: And I still need to subtract the value from the previous day for the final results.

    So the difference between the "last value of a day" and the "last value of the previous day" could be done like this:
    Code:
    SELECT the_day, 
           last_per_day,
           last_per_day - lag(last_per_day) over (order by the_day) as calculated_value
    FROM 
    (
      SELECT DISTINCT trunc(ts_column) as the_day, 
             first_value(value_column) over (partition by trunc(ts_column) order by ts_column desc) as last_per_day
      FROM the_table
    )
    ORDER BY 1
    Last edited by shammat; 01-27-09 at 15:16.

  7. #7
    Join Date
    Nov 2008
    Posts
    13
    Thank you so much Shammat, that works!

Posting Permissions

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