4 Replies Latest reply: Jun 6, 2013 2:08 AM by Sushil Kumar RSS

    Calculate CTC Financial Year Wise for Employee

    vikas mahajan

      dear All

       

      I have following data for one employee  I want to show CTC of employee  during each financial year 

      for eg.   2011 -12  (april 2011 - Mar 2012) is  485784  and in next finyr it is  553824 

       

      wise how  do I calculate please help me.

       

      Following are the salary transaction table every month ctc is updated transaction in table.

       

      CTC_ERNN_EMPLOYEE_IDD_PAY_DATEBASIC_ERNHRA_ERNGROSS_EARN_ERNPF_ERNACESIGROSS_ERN
      485784121030/04/201116100805035556193231800
      485784121031/05/201116100805031800193231800
      485784121030/06/201116100805031800193231800
      NULL121031/07/2011NULLNULL53600241237250
      553824121031/08/201117100855037250205237250
      553824121030/09/201117100855045650205237250
      553824121031/10/201117100855037250205237250
      553824121030/11/201117100855037250205237250
      553824121031/12/201117100855063050205237250
      553824121031/01/201217100855037250205237250
      553824121029/02/201217100855037250205237250
      553824121031/03/201217100855037250205237250

      Thanks in advanace

       

      Vikas

        • Re: Calculate CTC Financial Year Wise for Employee
          Sushil Kumar

          Hi,

           

          Please find attached

           

          According to your data i am assuming that your next financial year starts from 31/08/2011..

           

           

          Change the "YEARNAME(DATEFIELD,0,OFFSET)" offset value according to your date field and requirement.

           

          HTH

          Sushil

            • Re: Calculate CTC Financial Year Wise for Employee
              vikas mahajan

              Hi Sushil

               

              Many thanks for reply will work for  if i have  start date April  and end date as  Mar.

               

              Vikas

                • Re: Calculate CTC Financial Year Wise for Employee
                  Sushil Kumar

                  Hi Vikas,

                   

                  Try this Code:

                   

                  Table1:

                  LOAD

                  YearName(D_PAY_DATE,0,4) as F_YEAR

                  ,

                  * Inline [

                  CTC_ERN,N_EMPLOYEE_ID,D_PAY_DATE,BASIC_ERN,HRA_ERN,GROSS_EARN_ERN,PF_ERN,ACESIGROSS_ERN

                  485784,          1210,          31/01/2011,          16100,          8050,          35556,          1932,          31800

                  485784,          1210,          28/02/2011,          16100,          8050,          31800,          1932,          31800

                  485784,          1210,          31/03/2011,          16100,          8050,          31800,          1932,          31800

                  NULL,          1210,          30/04/2011,          NULL,          NULL,          53600,          2412,          37250

                  553824,          1210,          31/05/2011,          17100,          8550,          37250,          2052,          37250

                  553824,          1210,          30/06/2011,          17100,          8550,          45650,          2052,          37250

                  553824,          1210,          31/07/2011,          17100,          8550,          37250,          2052,          37250

                  553824,          1210,          31/08/2011,          17100,          8550,          37250,          2052,          37250

                  553824,          1210,          30/09/2011,          17100,          8550,          63050,          2052,          37250

                  553824,          1210,          31/10/2011,          17100,          8550,          37250,          2052,          37250

                  553824,          1210,          30/11/2011,          17100,          8550,          37250,          2052,          37250

                  553824,          1210,          31/12/2011,          17100,          8550,          37250,          2052,          37250

                  553824,          1210,          31/01/2012,          17100,          8550,          63050,          2052,          37250

                  553824,          1210,          28/02/2012,          17100,          8550,          37250,          2052,          37250

                  553824,          1210,          31/03/2012,          17100,          8550,          37250,          2052,          37250

                  ];

                   

                  HTH

                  Sushil

              • Re: Calculate CTC Financial Year Wise for Employee
                sujeet singh

                SET date from March to jan .