Need elegant SQL solution
create table abcd
(
timestamp datetime hour to minute not null, /* Unique Key */
xyz smallint not null
)
Our application needs this table to be collapsed into records for
each 10 min interval, where value of xyz is the SUM for preceeding 10
minute period.
Initial table: Need:
|08:00|3| 08:00 3
|08:11|4| 08:20 4
|08:21|5|
|08:30|6| 08:30 11
|08:34|7|
|08:36|8|
|08:40|9| 08:40 24
Note: A record for 08:10 is missing because there were no initial records
for period between 08:00 to 08:10
Is there any elegant SQL solution for achieving this or will I have to write
a C-program to do this ?? If I were to use ESQL/C is following pseudo code
too expensive ?
/* ---------------------- */
declare update cursor for /* pick up bad records */
select timestamp, xyz
from abcd
where extend(timestamp,minute to minute) not in (0, 10, 20, 30, 40, 50)
order by timestamp;
for each row retrieved
{
have var_xyz, var_timestamp
adjustment = 10 - (extend(var_timestamp minute to minute) % 10)
newtimestamp = var_timestamp + adjustment
update abcd /* Try adding to existing record */
set xyz = xyz + var_xyz
where timestamp = newtimestamp
if (SQLNOTFOUND) /* If no boundary record ... create one */
{
insert into abcd values (newtimestamp, var_xyz)
}
delete-row /* delete bad record */
}
/* ---------------------- */
The initial table is not too large as we do this collapsing daily.
Of course I'll do this using begin/commit transactions
AS you can see that I'am not using any date-time functions or tricks.
This exactly is what I seek to make the solution look somewhat elegant.
Any help/suggestion welcome.
========================================================================
Bharat Shah Voice: (508)-663-7570
Unifi Communications Corp. Fax: (508)-663-7543
4 Federal St, Billerica, Mass 01821, USA uunet!bharat@unifi.com
========================================================================