4 Replies Latest reply: Aug 31, 2009 1:36 PM by Sravan Puppala RSS

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

    Sravan Puppala

      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??

       

       

       

       

       

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

          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...

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

            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

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

              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

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

                  @ 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..