1

I am having trouble solve. I am suppose to be getting a record every time there is a change to an account in our data warehouse, but I am only receiving one. The table below is a sample of what I am working with.

Row     Acct1   Acct2   Date        Total_Reissued  Reissue_Per_Day
1       A        1      1/1/2016    2               2
2       A        1      1/2/2016    3               1
3       A        1      1/3/2016    5               2
4       A        1      1/4/2016    6               1

1       B       3       1/1/2016    1               1
2       B       3       1/2/2016    2               1
1       B       4       1/1/2016    1               1
2       B       4       1/2/2016    2               1

The Reissued Column is a running total. For Acct A on 1/1/2016 there were 2 reissues, then On 1/2/2016 there was 1 more making a total of 3. My problem is calculating the actual number of reissues per day.

Improve this question
1
  • Please edit your question to include additional information. As it reads now I don't understand how to get from what you show in the "Reissued" column to what you've shown in the "Results I want" column. ??? Commented May 9, 2016 at 15:06

1 Answer 1

Reset to default
2

You can use the lag() function to peek back at the previous row; assuming that 'previous' is the last date you saw for the acct1/acct2 combination you can do:

select row_number() over (partition by acct1, acct2 order by dt) as row_num,
  acct1, acct2, dt, total_reissued,
  total_reissued - nvl(lag(total_reissued)
    over (partition by acct1, acct2 order by dt), 0) as reissue_per_day
from your_table;

   ROW_NUM A      ACCT2 DT         TOTAL_REISSUED REISSUE_PER_DAY
---------- - ---------- ---------- -------------- ---------------
         1 A          1 2016-01-01              2               2
         2 A          1 2016-01-02              3               1
         3 A          1 2016-01-03              5               2
         4 A          1 2016-01-04              6               1
         1 B          3 2016-01-01              1               1
         2 B          3 2016-01-02              2               1
         1 B          4 2016-01-01              1               1
         2 B          4 2016-01-02              2               1

I'm not sure if your 'row' column actually exists, or is required, or was just to illustrate your data. I've generated it anyway, in case you need it.

The main bit of interest is:

lag(total_reissued) over (partition by acct1, acct2 order by dt)

which finds the previous date's value (using dt as a column name, since date isn't a valid name). That then has an nvl() wrapper so the first row sees a dummy value of zero instead of null. And then that is subtracted from the current row's value to get the difference.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This is exactly what I am looking for! Thank you!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.