Calculate CTC Financial Year Wise for Employee

[vikasmahajan]

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_ERN	N_EMPLOYEE_ID	D_PAY_DATE	BASIC_ERN	HRA_ERN	GROSS_EARN_ERN	PF_ERN	ACESIGROSS_ERN
485784	1210	30/04/2011	16100	8050	35556	1932	31800
485784	1210	31/05/2011	16100	8050	31800	1932	31800
485784	1210	30/06/2011	16100	8050	31800	1932	31800
NULL	1210	31/07/2011	NULL	NULL	53600	2412	37250
553824	1210	31/08/2011	17100	8550	37250	2052	37250
553824	1210	30/09/2011	17100	8550	45650	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	63050	2052	37250
553824	1210	31/01/2012	17100	8550	37250	2052	37250
553824	1210	29/02/2012	17100	8550	37250	2052	37250
553824	1210	31/03/2012	17100	8550	37250	2052	37250

Thanks in advanace

Vikas

[sushil353]

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

[vikasmahajan]

Hi Sushil

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

Vikas

[sujeetsingh]

SET date from March to jan .

[sushil353]

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