Hi,
please help me in solving this case study...
This is the scenario to be consider to implement scd 2 in my case study,
i have the following columns with the data in the input sequential file...
-->input_sequential file
----------------------------
contrid contlineid product_name start_date end_date Lic_cunt
10 1 a1 1/2/08 6/9/08 10
10 1 a1 4/4/08 6/9/08 20
10 1 a1 5/6/08 6/9/08 30
20 2 a2 31/6/08 8/12/08 80
20 2 a2 07/9/08 8/12/08 100
20 2 a2 22/11/08 8/12/08 180
------------------------------------------------------------------------------------
The output must be,as follows
*Note: concentrate on start_date and end_date fields
The output :
--------------
contrid contractlineid product_name start_date end_date Lic_cunt
10 1 a1 1/2/08 3/4/08 10
10 1 a1 4/4/08 4/6/08 20
10 1 a1 5/6/08 6/9/08 30
20 2 a2 31/6/08 6/9/08 80
20 2 a2 07/9/08 21/11/08 100
20 2 a2 22/11/08 8/12/08 180
------------------------------------------------------------------------------------
conditions:
1. from the inputfile,you will notice that endate for all contractlineid=1 will be 6/9/08 and for all contractlineid=2 will be 8/12/08. so,when ever the contractlineid=1 ,the previous record of contractlineid=1 end_date field must be the current record start_date - 1 ,you can consider the following example,
input:
contrid contlineid product_name start_date end_date Lic_cunt
10 1 a1 1/2/08 6/9/08 10
10 1 a1 4/4/08 6/9/08 20
10 1 a1 5/6/08 6/9/08 30
output:
contrid contractlineid product_name start_date end_date Lic_cunt
10, 1, a1, 1/2/08 , 3/4/08 , 10
10, 1, a1, 4/4/08 , 4/6/08 , 20
10 1 a1 5/6/08, 6/9/08, 30
you can notice that, in the o/p of first record end_date field changed to
3/4/08 from 6/9/08 i.e. (4/4/08)-1
To get the records in this manner in the end_date,what is the condition i have to implement and whether i have to use stage variable/routine?
If any one help me in getting this output by implementing in datastage,i am thankful to them in advance.... :)
Code: Select all
