Reconstructing Time-Series Data

• bigdave

  SSC Eights!

  Points: 853

  More actions

  November 5, 2001 at 8:31 pm

  #77877

  I want to plot a chart reconstructing the frequency of record saves from a history table.

  In the history table I create a new record (with savedatetime) for every save that is made.

  For this I need to place a count of saves made in every 10 minute interval. Importantly I need missing intervals to be included with a zero (or the chart will not accurately represent the data)

  I could do a group by (using date parts) and group by day, then by hour, then by 10 min date part. But then I will not get zero's for intervals where there was no activity.

  To complicate this, the Excel chart does not plot time-data down to hours or minutes.

  Anyone have any ideas??

  Thanks in advance,

  Dave

  (PS Thanks Andy - for your help earlier on this discussion list)

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738160

  More actions

  November 5, 2001 at 10:47 pm

  #419698

  Two ideas,

  1. Use a temp table/table datatpye to loop through the time interval and count each time,

  2. Build a temp tabel of ten minute intervals. Join this to your table.

  Steve Jones

  steve@dkranch.net

• Andy Warren

  SSC Guru

  Points: 119902

  More actions

  November 6, 2001 at 5:17 am

  #419711

  You're welcome! Thanks for participating.

  I think it makes sense to do the group by, you don't want to issue a query for each time region. Once you have the group by, you could join as Steve suggests to a table has has all the time portions you want to report on - just use a left join and show zero where null on the right side of the join.

  Seemsssssss like it should work, have not run a test! If you post the DDL for the table and whatever code you have, we'll experiment.

  Andy

  Andy
  Connect with me on LinkedIn

• nigelrivett

  SSCertifiable

  Points: 5472

  More actions

  November 6, 2001 at 1:44 pm

  #419748

  You could join to a derived table with the 10 min intervals rather than a temp table - but it's not pretty, depends if you want to do it in a single query.

  
  Cursors never.
  DTS - only when needed and never to control.