need query to subtract data in 2 rows

#1

Hour Reading Data
8AM 100
9AM 500
10AM 700
11AM 900

I need a query to come up with the trend reading data in every 1 hr
like below

Time Trend
8 - 9 AM 500-100=400
9 - 10 AM 700-500=200

#2

On 5 Okt., 13:09, siva perumal <srivi.s... (AT) gmail (DOT) com> wrote:

Quote:

Hour * * * * * * * * * * * * * *Reading Data
8AM * * * * * * * * * * * * * *100
9AM * * * * * * * * * * * * * *500
10AM * * * * * * * * * * * * *700
11AM * * * * * * * * * * * * *900

I need a query to come up with the trend reading data in every 1 hr
like below

Time * * * * * * * * * * * *Trend
8 - 9 AM * * * * * * * * * 500-100=400
9 - 10 AM * * * * * * * * 700-500=200

Hi Siva

check out the DB2 OLAP functions.
"LAG" for example allows you to work with the content of two
succeeding rows.

Have look into the DB2 Cookbook ( http://mysite.verizon.net/Graeme_Birchall/id1.html
)
for more details and sample.

kind regards
Michael

#3

On Oct 5, 1:40*pm, globomike <M_Tiefenbac... (AT) gmx (DOT) de> wrote:

Quote:

On 5 Okt., 13:09, siva perumal <srivi.s... (AT) gmail (DOT) com> wrote:

Hour * * * * * * * * * * * * * *Reading Data
8AM * * * * * * * * * * * * * *100
9AM * * * * * * * * * * * * * *500
10AM * * * * * * * * * * * * *700
11AM * * * * * * * * * * * * *900

I need a query to come up with the trend reading data in every 1 hr
like below

Time * * * * * * * * * * * *Trend
8 - 9 AM * * * * * * * * * 500-100=400
9 - 10 AM * * * * * * * * 700-500=200

Hi Siva

check out the DB2 OLAP functions.
"LAG" for example allows you to work with the content of two
succeeding rows.

Have look into the DB2 Cookbook (http://mysite.verizon.net/Graeme_Birchall/id1.html
)
for more details and sample.

kind regards
Michael

Hi,

Without using OLAP functions, it's also possible by joining this table
with itself (although OLAP might be more performant):

select b.data - a.val
from tab a join tab b on a.time = b.time -1

--
Frederik Engelen

#4

If there is a lot of data and you do not have the right indexes (on
time), then OLAP will be significantly more performant.

Also, OLAP can handle missing hours/day boundaries better, depending
on how the data is stored. Check out the rows/range options for what
works best in your case.

-Chris

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

need query to subtract data in 2 rows

comp.databases.ibm-db2 comp.databases.ibm-db2

need query to subtract data in 2 rows - 10-05-2010 , 06:09 AM

Re: need query to subtract data in 2 rows - 10-05-2010 , 06:40 AM

Re: need query to subtract data in 2 rows - 10-05-2010 , 07:03 AM

Re: need query to subtract data in 2 rows - 10-05-2010 , 10:47 AM