Well not yet. INCR_LOAD has to be subtracted by the TARGET and SUB_TARGET, so if TARGET A is 192.168.1.1 and SUB_ is 1a, INCR_LOAD has to be a result only for that pair of that targets. So in case of such situation
2008-01-01 12:14 192.168.1.1 1a 100 0 2008-01-01 12:14 192.168.1.1 1b 140 0 2008-01-01 12:24 192.168.1.1 1a 104 4 2008-01-01 12:24 192.168.1.1 1b 143 3 2008-01-01 12:34 192.168.1.1 1a 108 4 2008-01-01 12:34 192.168.1.1 1b 145 2 2008-01-01 12:44 192.168.1.1 1a 117 9 2008-01-01 12:44 192.168.1.1 1b 149 4 2008-01-01 12:54 192.168.1.1 1a 122 5 2008-01-01 12:54 192.168.1.1 1b 157 6 Result has to be in the same row, but Subtract has to be a result of two values from the same target and sub_. There can't be minus values. On 16 Gru, 16:46, ddf <orat...@msn.com> wrote: > On Dec 16, 8:52 am, Rem-8 <lukasz.r...@gmail.com> wrote: > > > > > TS TARGET SUB_TARGET LOAD > > 2008-01-01 12:14 192.168.1.1 1a 144 > > 2008-01-01 12:14 192.168.1.1 1b 145 > > 2008-01-01 12:14 192.168.1.1 2a 142 > > 2008-01-01 12:14 192.168.1.1 2b 145 > > 2008-01-01 12:14 192.168.1.2 6s 53 > > 2008-01-01 12:14 192.168.1.2 6t 56 > > 2008-01-01 12:14 192.168.1.2 6u 53 > > 2008-01-01 12:14 192.168.1.2 6v 58 > > 2008-01-01 12:14 192.168.1.3 7a 22 > > 2008-01-01 12:14 192.168.1.4 8a 34 > > 2008-01-01 12:14 192.168.1.5 9a 11 > > 2008-01-01 12:14 192.168.1.6 9g 8 > > 2008-01-01 12:14 192.168.1.7 9h 4 > > 2008-01-01 12:14 192.168.1.8 9i 2 > > 2008-01-01 12:24 192.168.1.1 1a 147 > > 2008-01-01 12:24 192.168.1.1 1b 150 > > 2008-01-01 12:24 192.168.1.1 2a 142 > > 2008-01-01 12:24 192.168.1.1 2b 147 > > 2008-01-01 12:24 192.168.1.2 6s 66 > > 2008-01-01 12:24 192.168.1.2 6t 62 > > 2008-01-01 12:24 192.168.1.2 6u 63 > > 2008-01-01 12:24 192.168.1.2 6v 71 > > 2008-01-01 12:24 192.168.1.3 7a 29 > > 2008-01-01 12:24 192.168.1.4 8a 35 > > 2008-01-01 12:24 192.168.1.5 9a 15 > > 2008-01-01 12:24 192.168.1.6 9g 9 > > 2008-01-01 12:24 192.168.1.7 9h 5 > > 2008-01-01 12:24 192.168.1.8 9i 7 > > > This is correct one. Previous has wrong days column. Generated values > > in Excel... > > Is this what you want: > > SQL> select target, sub_target,load, > 2 case when target = prev_target then load - lag(load) over > (order by target, sub_target, ts) end incr_load > 3 from > 4 (select target, sub_target, ts, load, lag(target) over (order by > target, sub_target, ts) prev_target > 5 from sales_data); > > TARGET SUB_ LOAD INCR_LOAD > ---------------- ---- ---------- ---------- > 192.168.1.1 1a 144 > 192.168.1.1 1a 147 3 > 192.168.1.1 1b 145 -2 > 192.168.1.1 1b 150 5 > 192.168.1.1 2a 142 -8 > 192.168.1.1 2a 142 0 > 192.168.1.1 2b 145 3 > 192.168.1.1 2b 147 2 > 192.168.1.2 6s 53 > 192.168.1.2 6s 66 13 > 192.168.1.2 6t 56 -10 > > TARGET SUB_ LOAD INCR_LOAD > ---------------- ---- ---------- ---------- > 192.168.1.2 6t 62 6 > 192.168.1.2 6u 53 -9 > 192.168.1.2 6u 63 10 > 192.168.1.2 6v 58 -5 > 192.168.1.2 6v 71 13 > 192.168.1.3 7a 22 > 192.168.1.3 7a 29 7 > 192.168.1.4 8a 34 > 192.168.1.4 8a 35 1 > 192.168.1.5 9a 11 > 192.168.1.5 9a 15 4 > > TARGET SUB_ LOAD INCR_LOAD > ---------------- ---- ---------- ---------- > 192.168.1.6 9g 8 > 192.168.1.6 9g 9 1 > 192.168.1.7 9h 4 > 192.168.1.7 9h 5 1 > 192.168.1.8 9i 2 > 192.168.1.8 9i 7 5 > > 28 rows selected. > > SQL> > > David Fitzjarrell --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en -~----------~----~----~----~------~----~------~--~---
