Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi Sushil
Many thanks for reply will work for if i have start date April and end date as Mar.
Vikas
SET date from March to jan .
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