I will like to know how can I calculate the differences of two dates in a table avoiding the use of Oracle analytic functions and subqueries.
I have a table of product sales and I will like to know how sales have been going eversince consumers started buying it(Table 1). I need to get the minimum date of each product when sales started and subtract it from successive dates and show the relative sale. I know I can achieve it using analytic functions like ( DENSE_RANK FIRST ORDER BY) or subqueries. If possible I will like to avoid it . The result I will like to have is available in Table 2
Period Prod Sale
200810 ref 2
200811 ain 6
200811 ref 3
200812 ain 7
200812 del 3
200901 ain 9
200901 ref 5
200901 del 4
Here is the result I will like to expect.
Prod Period Min_Date Month_Diff Sale
ref 200810 200810 0 2
ain 200811 200811 0 6
ref 200811 200810 1 3
ain 200812 200811 1 7
del 200812 200812 0 3
ref 200901 200810 3 5
del 200901 200812 4 5
Another question. What is the data type of "period"?
You refer to this field as dates ("can I calculate the differences of two dates") but they aren't actually dates that are shown. They could be integers
More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…