2 Replies Latest reply: Oct 30, 2014 10:57 AM by Uladzimir Kruhlik RSS

    Original supplier in scd type 2

    Uladzimir Kruhlik

      I have source table with the following structure:

       

      from

      to

      prodID

      date

      supplier1

      warehouse1

      1

      1. 10.10.2014

      warehouse1

      warehouse2

      1

      1. 12.10.2014

      supplier2

      warehouse1

      1

      1. 13.10.2014

      warehouse2

      shop1

      1

      1. 14.10.2014

      supplier3

      shop4

      1

      1. 15.10.2014

      warehouse1

      shop3

      1

      1. 16.10.2014

       

      and I want to find the last original supplier in the separate field:

       

      from

      to

      prodID

      date

      original supplier

      supplier1

      warehouse1

      1

      1. 10.10.2014

      supplier1

      warehouse1

      warehouse2

      1

      1. 12.10.2014

      supplier1

      supplier2

      warehouse1

      1

      1. 13.10.2014

      supplier2

      warehouse2

      shop1

      1

      1. 14.10.2014

      supplier1

      supplier3

      shop4

      1

      1. 15.10.2014

      supplier3

      warehouse1

      shop3

      1

      1. 16.10.2014

      supplier2

       

       

      I struggle with this task for months. The difficulty is in the  size of source table ( about 15M records for 15 month ) and that I can't build the tree using "hierarchy" cause I have to connect with the closest last date (not the same).

      Any ideas would be appreciated.