Finding Difference between two rows grouped by a date or between two pivoted columns

Question

I have a set of values for two different dates in my OBIEE report:

------------------------------------------------------------------
Option   Date     Value 
------|---------|-------
OPT1    Date 1    5     
OPT1    Date 2    2     
OPT2    Date 1    9     
OPT2    Date 2    1     
OPT3    Date 1    7     
OPT3    Date 2    13    
OPT4    Date 1    5     
OPT4    Date 2    6

I hope to get the difference of Values between each set of date, and present the data in the following format, grouped by option:

Option  Date               Diff
        Date 1   Date 2 
------|--------|--------|-------  
OPT1    5        2        3
OPT2    9        1        8
OPT3    7        13       -6
OPT4    5        6        -1

The Date portion can be handled using Pivot, however I am unable to find the difference between the now pivoted columns.

I believe if the difference between the two values be found for a set of date (as shown below) and then pivoted, it might do the trick but i can't find the difference for a set.

Option  Date      Value   Diff
-------|---------|-------|-------
OPT1    Date 1    5       null
OPT1    Date 2    2        3
OPT2    Date 1    9       null
OPT2    Date 2    1        8
OPT3    Date 1    7       null
OPT3    Date 2    13       -6
OPT4    Date 1    5       null 
OPT4    Date 2    6       -1

Any help is appreciated.

Thanks, Junaid

Please edit your question to include the query you used to pivot the data. — Boneist, Apr 25 '19 at 15:29
Also, how do you know which Date 1 rows are linked to Date 2 rows? There's column to link them. — Boneist, Apr 25 '19 at 15:34
@JunaidAhmad . . . SQL tables represent *unordered* sets. There is no ordering, unless a column specifies the ordering. And you don't seem to have such a column. — Gordon Linoff, Apr 25 '19 at 19:05
I have edited my question. Added a column to link the dates. Helpful? — Junaid Ahmad, Apr 26 '19 at 06:29
Just to add my 10 cents - that's not how OBIEE is built nor how it works. OBI uses *models*. You're not writing SQL queries. — Chris, Apr 27 '19 at 20:15

score 1 · Answer 1 · answered Apr 25 '19 at 15:52

You can refer to, and do calculations using, the columns generated by the pivot clause. Assuming you have some key value linking the pairs of values for dates 1 and 2, you can do something like:

-- CTE for sample data, with made-up keys
with your_table (some_key, some_date, value) as (
            select 1, date '2019-04-01', 5 from dual
  union all select 1, date '2019-04-15', 2 from dual
  union all select 2, date '2019-04-01', 9 from dual
  union all select 2, date '2019-04-15', 1 from dual
  union all select 3, date '2019-04-01', 7 from dual
  union all select 3, date '2019-04-15', 13 from dual
  union all select 4, date '2019-04-01', 5 from dual
  union all select 4, date '2019-04-15', 6 from dual
)
-- actual query
select some_key, date1, date2, date1 - date2 as diff
from your_table
pivot (max(value) for some_date in (date '2019-04-01' as date1, date '2019-04-15' as date2))
order by some_key;

  SOME_KEY      DATE1      DATE2       DIFF
---------- ---------- ---------- ----------
         1          5          2          3
         2          9          1          8
         3          7         13         -6
         4          5          6         -1

In the date1 - date2 as diff expression, date1 and date2 are the names/aliases from the pivot. You can't usually use a column alias in the same level of query that defines it, but pivot lets you get away with it.

score 0 · Answer 2 · answered Apr 26 '19 at 14:29

OK, I think i got the solution for doing the row-wise subtraction partitioned by option

VALUE - LAG(VALUE, 1, NULL) OVER (PARTITION BY OPTION ORDER BY OPTION)

However, due to certain restrictions, I am unable to use DB functions in OBIEE. I would like to learn of a solution which doesn't involve using DB functions (like LAG/LEAD).

Finding Difference between two rows grouped by a date or between two pivoted columns

2 Answers2