CODEWITHSUNDEEP
sqloracle11g
JONSY
JONSY

Reputation: 19

months between date in the same column

May I ask for some help? I need to calculate the months between the order dates for the same product ID. I have the following data set 

ORDER_NUM   PRODUCT_ID  ORDER_DATE
111111  222222  2015-05-20 18:30:38
111112  222223  2015-12-03 19:25:23
111113  222224  2015-12-30 18:16:25
111114  222225  2015-10-30 12:32:06
111115  222226  2015-12-26 16:14:33
111116  222227  2016-03-08 10:23:39
111117  222224  2015-10-01 09:04:56
111118  222223  2015-04-21 11:48:03
111119  222228  2015-11-14 10:00:38
111120  222229  2016-03-22 10:42:32
111121  222230  2015-11-10 12:14:41
111122  222231  2015-11-24 10:05:40
111123  222222  2015-12-05 12:18:28
111124  222232  2015-12-07 11:23:53
111125  222233  2015-07-17 10:47:54
111126  222234  2016-02-08 11:59:30
111127  222235  2015-11-08 15:40:08
111128  222223  2015-09-24 11:16:03
111129  222236  2015-11-09 12:30:04

where ORDER_NUM is unique value, PRODUCT_ID may appear many times and time also. I need the result to be like: 

ORDER_NUM   PRODUCT_ID  MONTHS_BETWEEN
111111  222222  0
111112  222223  2
111113  222224  3
111114  222225  0
111115  222226  0
111116  222227  0
111117  222224  0
111118  222223  0
111119  222228  0
111120  222229  0
111121  222230  0
111122  222231  0
111123  222222  7
111124  222232  0
111125  222233  0
111126  222234  0
111127  222235  0
111128  222223  5
111129  222236  0

The first appearance of PRODUCT_ID should have “0” value in MONTHS_BETWEEN and each next should have value the months between the current and the previous.

I am not sure that I managed to explain very well … Please help…

Upvotes: 1

Views: 145

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can use months_between() and lead():

select t.*,
       months_between(lead(order_date() over (partition by product_id order by order_date)),
                      order_date
                     ) as MonthsBetween
from t;

Notes:

  • This returns a number with decimal places. You might want to use trunc() or round() to get an integer.
  • This returns NULL when there is no "next" order. You can use COALESCE() to convert that to 0 (or something else) if you like.
  • To be honest, I can't tell if you want lead() or lag() (time to the next order or from the previous one). Your data is not ordered by date, making it hard to figure out the right ordering. But, you want one or the other.

Upvotes: 3

Related Questions