SQLScript Heavy Lifting

29 Views

Hi,

I need your opinion on how to code the following view using SQL. This is for a research project on HANA.

The table has sequential meter readings with the following table structure:

CustomerID, MeterID, Date, Time, Meter Reading

Now I need to know the difference from one reading to the next in order to calculate the amount of electricity consumed in that time period. So if I am taking out a trend over a year, I would need an average on the difference of sequential meter readings.

One idea is to do an inner join on the same table with an offset of 15 seconds and then minus the Meter Reading fields.

There will be millions of customer records in the table and this calculation needs to be done for different customers individually or as groups.

For example:

Date Time Reading

12-Feb 12:00 11000

12-Feb 1300 12000

My problem is that I cant apply a simple average function in SQL over the reading. Power consumed = 12000 - 11000 = 1000

Can you help?

Best Regards,

Adeel Hashmi