How to define for tables in script as per Present and Previous year?

[#1]

Hi guys,

I will describe my problem.

I have this structure

Table1:
PLS & '/' & OBJID as key1,
OBJID as key2,
field1.BEGDATE,
field2.ENDATE
;

Table2:
Key1
field3.BEGDATE
field4.ENDDATE
;
Table3:
Key1
field5.BEGDATE
field6.ENDDATE
;
Table4:
Key1
field7.BEGDATE
field8.ENDDATE
;

The Key1 is in lot of other tables and the whole datastructure is attached to table1. I define a Variable like

vDatePresent =31.12.2008 and write a code in script and get all the data corresponding to all the tables with the dates BEGDATE and ENDDATE as Below.

field X.BEGDATE<=vDate or field X.ENDDATE >=vDate Here X is the group of fields from 1 to 8

Now I wanted also the data corresponding to VDatePrevyear=31.12.2007

How can I achieve that any Ideas??

[#2]

I did like this

I create a table

Table1_PREVYEAR:
OBJID as Key2,
PlS as Key3,
field1_PREVYEAR.BEGDATE,
field2_PREVYEAR.ENDDATE
;

Table2_PREVYEAR:
OBJID as Key2,
PlS as Key4,
field3_PREVYEAR.BEGDATE,
field4_PREVYEAR.ENDDATE
;

etc....

and connect to the secondvariable of the previousyear VDatepreviousyear and done the same procedure..

I think as the key1 in the table1 is not same I have a problem..How can I generate 2 fields and connect it with the Datamodel of the presentyear and also to the datamodel of the Previousyear?

I hope I did explain in Detail...

[stephencredmond]

Hi,

I'm not sure if I totally understand your data structure. However, the simple answer to your question is just to do a second set of loads and concatenate them onto the relevant tables, or consider if the query:

field X.BEGDATE<=VDatePrevyear or field X.ENDDATE >=vDate

Will work for you.

I am also wondering if your begin date and end dates need to be separate in all the tables? If they are common to the Key, maybe have one table with all the values concatenated - Key1, BEGDATE, ENDDATE - and then you can use an Interval Match to create a calendar table linked to both date fields.

Regards,

Stephen

[#4]

Hi,

The problem is not very clear, but from what I understood I have few suggestions:

Why do you need to create separate model for current and previous years? You could create just one model and add some kind of flag to distinguish between current and previous year. Hope this is of some help...if the problem is something else, please provide more details (if possible with an example).

Thanks

Amit

[#5]

@ Stephen

Second set of loads I added as shown above but the Problem is I have to have a different Key for my second loads otherwise the Presentyearkey will collide..

Ya begin and Enddates should be separate in all the tables bcos there are thousands of them and are distinct for each Person.

@ Amit

I create seperate models bcos the second set of model ist compared to a variable vDatepreviousyear and the first set of Model is compared to vDatepresent

I have a routine in

Sub

Endsub

where according to the preferred variable and selected tables

for ex first set with vDatepresent = 31.12.2008 I compare the BEGDATE and ENDDATE of the tables

Table1, Table2, Table3,Table4... whether they are <= or >= 31.12.2008 and all others are excluded which do not fall in to the category..

In the sameway I compare in another routine where vDatepreviousyear =31.12.2007 the BEGDATE and ENDDATE of the tables

Table_1_PREVYEAR,Table_2_PREVYEAR,Table_3_PREVYEAR,Table_4_PREVYEAR

I hope I was clear in my explanation..