November 5, 2001 at 8:31 pm
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)
November 5, 2001 at 10:47 pm
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
November 6, 2001 at 5:17 am
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
November 6, 2001 at 1:44 pm
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply