CODEWITHSUNDEEP
sqldatabaseoracle-databaselag
Andrei Paul
Andrei Paul

Reputation: 83

Lag function not working properly

I got this query

CREATE OR REPLACE View  QTY_VIEW  As 
SELECT       
  DATA_VIEW.RAW_ID as RAW_ID, 
  DATA_VIEW.CREATE_DATE as CREATE_DATE, 
  DATA_VIEW.UPDATE_DATE as UPDATE_DATE, 
  DATA_VIEW.INITIAL_QTY as INITIAL_QTY, 
  DATA_VIEW.ACTUAL_QTY as ACTUAL_QTY, 
  DATA_VIEW.USED_QTY as USED_QTY, 
  DATA_VIEW.LAST_USED_BY as LAST_USED_BY, 
  CASE 
    WHEN DATA_VIEW.ACTUAL_QTY = DATA_VIEW.INITIAL_QTY 
      THEN 0             
    WHEN LAG(DATA_VIEW.USED_QTY, 1, 0) 
           OVER (ORDER BY DATA_VIEW.RAW_ID ASC,DATA_VIEW.ACTUAL_QTY,DATA_VIEW.UPDATE_DATE)
           = DATA_VIEW.USED_QTY - DATA_VIEW.USED_QTY   
      THEN 0            
    ELSE
      LAG(DATA_VIEW.USED_QTY, 1, 0) 
        OVER (ORDER BY DATA_VIEW.RAW_ID ASC,DATA_VIEW.ACTUAL_QTY,DATA_VIEW.UPDATE_DATE)
      - DATA_VIEW.USED_QTY  
    END as SINGLE_USE_QTY 
FROM 
  DATA_VIEW 
order by RAW_ID asc, UPDATE_DATE,ACTUAL_QTY ;

I think i am not using the LAG function properly as the last result is a negative value instead of a positive one. Can you help me please with some advice? The DATA_VIEW is DATA_VIEW And QTY_VIEW is like thatQTY_VIEW What i did wrong?

Upvotes: 0

Views: 2859

Answers (1)

Ditto
Ditto

Reputation: 3344

Couple things seem "fishy" to me ..

1) you're sorting on RAW_ID .. which is a string ... which contains numerics ... your sort order will sort raw_1, then raw_10 .. then raw_11 (etc) ... then raw_19, then raw_2 .. then raw_20 .. then raw_21 ... etc ...then raw_3 .. etc. You should consider using a PROPER sequence ... pure numerics .. so you can sort poperly.

2) in your 2nd CASE clause .. you have .. essentially: when LAG(something) = qty - qty ... which is basically: when LAG(something) = 0 ... is this intended ? If you're after the first occurance, it might be more clear to say: when LAG(something) IS NULL

3) what you display as your results .. definitely do NOT match they query/data you provided ... after I re-type all the data in (thanks for the image btw .. O.o next time, please just put actual text in so we can copy/paste .. makes our job easier to help you :P ) I do NOT get same results as you ... so please re-post a COMPLETE test case ... with proper data, query .. and such IN TEXT FORM ... so we can verify that we aren't introducing typos in the example ;)

This is what I get from what you posted:

  RAW_ID UPDATE_DA INITIAL_QTY ACTUAL_QTY   USED_QTY SINGLE_USE_QTY
  ------ --------- ----------- ---------- ---------- --------------
  raw_1  06-JUN-17       20000   48.52559   19951.47              0
  raw_10 06-JUN-17       20000   20.76559   19979.23         -27.76
  raw_11 06-JUN-17       20000   17.29559    19982.7          -3.47
  raw_12 06-JUN-17       20000   13.82559   19986.17          -3.47
  raw_13 06-JUN-17       20000   10.35559   19989.64          -3.47
  raw_14 06-JUN-17       20000   6.885593   19996.11          -6.47
  raw_15 06-JUN-17       20000   3.415594   19996.58           -.47
  raw_2  06-JUN-17       20000    45.0556   19954.94          41.64
  raw_3  06-JUN-17       20000   41.58559   19958.41          -3.47
  raw_4  06-JUN-17       20000   38.11559   19961.88          -3.47
  raw_5  06-JUN-17       20000   38.11559   19961.88              0
  raw_6  06-JUN-17       20000    34.6456   19965.35          -3.47
  raw_7  06-JUN-17       20000   31.17559   19968.82          -3.47
  raw_8  06-JUN-17       20000   27.70559   19972.29          -3.47
  raw_9  06-JUN-17       20000   24.23559   19975.76          -3.47

  15 rows selected.

Here's my re-worked query .. but no idea what you really want .. 

  with data_view as (
  select 'raw_1'  raw_id, 1 raw_id2, to_date('6/6/2017 16:01','dd/mm/yyyy hh24:mi') update_date, 20000 initial_qty, 48.52559 actual_qty, 19951.47 used_qty from dual union all
  select 'raw_2'  raw_id, 2 raw_id2, to_date('6/6/2017 16:01','dd/mm/yyyy hh24:mi') update_date, 20000 initial_qty, 45.0556  actual_qty, 19954.94 used_qty from dual union all
  select 'raw_3'  raw_id, 3 raw_id2, to_date('6/6/2017 16:01','dd/mm/yyyy hh24:mi') update_date, 20000 initial_qty, 41.58559 actual_qty, 19958.41 used_qty from dual union all
  select 'raw_4'  raw_id, 4 raw_id2, to_date('6/6/2017 16:01','dd/mm/yyyy hh24:mi') update_date, 20000 initial_qty, 38.11559 actual_qty, 19961.88 used_qty from dual union all
  select 'raw_5'  raw_id, 5 raw_id2, to_date('6/6/2017 16:01','dd/mm/yyyy hh24:mi') update_date, 20000 initial_qty, 38.11559 actual_qty, 19961.88 used_qty from dual union all
  select 'raw_6'  raw_id, 6 raw_id2, to_date('6/6/2017 16:01','dd/mm/yyyy hh24:mi') update_date, 20000 initial_qty, 34.6456  actual_qty, 19965.35 used_qty from dual union all
  select 'raw_7'  raw_id, 7 raw_id2, to_date('6/6/2017 16:01','dd/mm/yyyy hh24:mi') update_date, 20000 initial_qty, 31.17559 actual_qty, 19968.82 used_qty from dual union all
  select 'raw_8'  raw_id, 8 raw_id2, to_date('6/6/2017 16:01','dd/mm/yyyy hh24:mi') update_date, 20000 initial_qty, 27.70559 actual_qty, 19972.29 used_qty from dual union all
  select 'raw_9'  raw_id, 9 raw_id2, to_date('6/6/2017 16:01','dd/mm/yyyy hh24:mi') update_date, 20000 initial_qty, 24.23559 actual_qty, 19975.76 used_qty from dual union all
  select 'raw_10' raw_id, 10 raw_id2, to_date('6/6/2017 16:01','dd/mm/yyyy hh24:mi') update_date, 20000 initial_qty, 20.76559 actual_qty, 19979.23 used_qty from dual union all
  select 'raw_11' raw_id, 11 raw_id2, to_date('6/6/2017 16:01','dd/mm/yyyy hh24:mi') update_date, 20000 initial_qty, 17.29559 actual_qty, 19982.7  used_qty from dual union all
  select 'raw_12' raw_id, 12 raw_id2, to_date('6/6/2017 16:01','dd/mm/yyyy hh24:mi') update_date, 20000 initial_qty, 13.82559 actual_qty, 19986.17 used_qty from dual union all
  select 'raw_13' raw_id, 13 raw_id2, to_date('6/6/2017 16:01','dd/mm/yyyy hh24:mi') update_date, 20000 initial_qty, 10.35559 actual_qty, 19989.64 used_qty from dual union all
  select 'raw_14' raw_id, 14 raw_id2, to_date('6/6/2017 16:01','dd/mm/yyyy hh24:mi') update_date, 20000 initial_qty, 6.885593 actual_qty, 19996.11 used_qty from dual union all
  select 'raw_15' raw_id, 15 raw_id2, to_date('6/6/2017 16:01','dd/mm/yyyy hh24:mi') update_date, 20000 initial_qty, 3.415594 actual_qty, 19996.58 used_qty from dual 
  )
     SELECT       
        DATA_VIEW.RAW_ID as RAW_ID, 
        DATA_VIEW.RAW_ID2 as RAW_ID2, 
        DATA_VIEW.UPDATE_DATE as UPDATE_DATE, 
        DATA_VIEW.INITIAL_QTY as INITIAL_QTY, 
        DATA_VIEW.ACTUAL_QTY as ACTUAL_QTY, 
        DATA_VIEW.USED_QTY as USED_QTY, 
        LAG(DATA_VIEW.USED_QTY, 1, 0) OVER     
                 (ORDER BY DATA_VIEW.RAW_ID2 ASC,DATA_VIEW.ACTUAL_QTY,DATA_VIEW.UPDATE_DATE) 
           as lag_used_qty,
        CASE 
           WHEN   DATA_VIEW.ACTUAL_QTY =  DATA_VIEW.INITIAL_QTY 
              THEN 0             
           WHEN LAG(DATA_VIEW.USED_QTY, 1, 0) OVER 
                 (ORDER BY DATA_VIEW.RAW_ID2 ASC,DATA_VIEW.ACTUAL_QTY,DATA_VIEW.UPDATE_DATE) IS NULL
              THEN 0            
           ELSE
              LAG(DATA_VIEW.USED_QTY, 1, 0) OVER 
                 (ORDER BY DATA_VIEW.RAW_ID2 ASC,DATA_VIEW.ACTUAL_QTY,DATA_VIEW.UPDATE_DATE) 
                 - DATA_VIEW.USED_QTY  
        END as SINGLE_USE_QTY 
     FROM 
        DATA_VIEW 
     order by RAW_ID2 asc, ACTUAL_QTY, UPDATE_DATE ;

and here's the results :

  RAW_ID    RAW_ID2 UPDATE_DA INITIAL_QTY ACTUAL_QTY   USED_QTY SINGLE_USE_QTY
  ------ ---------- --------- ----------- ---------- ---------- --------------
  raw_1           1 06-JUN-17       20000   48.52559   19951.47              0
  raw_2           2 06-JUN-17       20000    45.0556   19954.94          -3.47
  raw_3           3 06-JUN-17       20000   41.58559   19958.41          -3.47
  raw_4           4 06-JUN-17       20000   38.11559   19961.88          -3.47
  raw_5           5 06-JUN-17       20000   38.11559   19961.88              0
  raw_6           6 06-JUN-17       20000    34.6456   19965.35          -3.47
  raw_7           7 06-JUN-17       20000   31.17559   19968.82          -3.47
  raw_8           8 06-JUN-17       20000   27.70559   19972.29          -3.47
  raw_9           9 06-JUN-17       20000   24.23559   19975.76          -3.47
  raw_10         10 06-JUN-17       20000   20.76559   19979.23          -3.47
  raw_11         11 06-JUN-17       20000   17.29559    19982.7          -3.47
  raw_12         12 06-JUN-17       20000   13.82559   19986.17          -3.47
  raw_13         13 06-JUN-17       20000   10.35559   19989.64          -3.47
  raw_14         14 06-JUN-17       20000   6.885593   19996.11          -6.47
  raw_15         15 06-JUN-17       20000   3.415594   19996.58           -.47

  15 rows selected.

Upvotes: 1

Related Questions