Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: April 05, 2017 - 5:20 pm UTC

Last updated: April 18, 2017 - 2:24 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi,

I have a requirement to write a view on the table containing historic price information of different products.

The 1 st history record(when sorted in an asecnding order of date) for a product should have the GRANT_DATE as the effective_date and the consequent history records should have the effective date as the Cessation_date of the previous record. THE GRANT_DATE FOR THE FIRST RECORD COMES FROM another table when joined on the product id

eg:

productid price cessationdate
1 10 9/3/17
1 11 24/3/17
1 12 30/3/17


so my view should produce

product id effective_date price
1 4/3/17 10
1 9/3/17 11
1 24/3/17 12

Could someone help me in how to implement this logic.

Please find the test cases below:

create table historic_price (product_id number(10),price number(5),cessation_date date)

insert into hisotric_price values(1,10,9-3-17);

insert into hisotric_price values(1,11,24-3-17);

insert into hisotric_price values(1,12,30-3-17);

create table product_info (product_id number(10),grant_date date)

insert into product_info (1,4-3-17);

And if there are multiple entries of a product in historic_price table on the same day, we need to consider max(cessation_date) to get the last entry in the day.

Please let me know if any further information needed.


Cheers
Sirisha

and Connor said...

You know... you should perhaps consider posting a test case that has some sort of remote chance of even working. Something that suggests you put a bit of effort into it. Something that suggests you had a vested interest in working with us *together* to get to solution....

SQL> create table historic_price (product_id number(10),price number(5),cessation_date date);

Table created.

SQL> insert into hisotric_price values(1,10,9-3-17);
insert into hisotric_price values(1,10,9-3-17)
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> insert into hisotric_price values(1,11,24-3-17);
insert into hisotric_price values(1,11,24-3-17)
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> insert into hisotric_price values(1,12,30-3-17);
insert into hisotric_price values(1,12,30-3-17)
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create table product_info (product_id number(10),grant_date date)
  2  insert into product_info (1,4-3-17);
insert into product_info (1,4-3-17)
*
ERROR at line 2:
ORA-00922: missing or invalid option


Because as it stands...it sort of reads like "You spent only 10 seconds on this, but you want us to spend 10 mins or more on it".... sigh

SQL> create table historic_price (product_id number(10),price number(5),cessation_date date);

Table created.

SQL> insert into historic_price values(1,10,date '2017-03-09');

1 row created.

SQL> insert into historic_price values(1,11,date '2017-03-24');

1 row created.

SQL> insert into historic_price values(1,12,date '2017-03-30');

1 row created.

SQL> create table product_info (product_id number(10),grant_date date);

Table created.

SQL> insert into product_info values (1,date '2017-03-04');

1 row created.

SQL>
SQL> with all_records as
  2   ( select product_id, price, cessation_date d from historic_price
  3     union all
  4     select product_id, null, grant_date d from product_info )
  5  select
  6    product_id, d, lead(price) over ( partition by product_id order by d )
  7  from all_records
  8  order by 1,2;

PRODUCT_ID D         LEAD(PRICE)OVER(PARTITIONBYPRODUCT_IDORDERBYD)
---------- --------- ----------------------------------------------
         1 04-MAR-17                                             10
         1 09-MAR-17                                             11
         1 24-MAR-17                                             12
         1 30-MAR-17

4 rows selected.



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.