Dear Friends......I have the data like:-
Emp_ID Start_Date End_Date Pay_Due Pay_Drawn
===== ======== ======= ======= ========
101 07-APR-2008 31-JAN-2009 15000 12000
101 01-FEB-2009 20-MAR-2009 20000 17000
102 01-FEB-2008 15-APR-2009 10000 90000
Each employee can have multiple lines in the table. We have to calculate each employees salary difference (from pay_due to pay_drawn) between startate and
End_date calculation is based on the number of days in corresponding month and the differnece of pay_due and pay_drwan.
for example data is:-
Emp_ID Start_Date End_Date Pay_Due Pay_Drawn
===== ======== ======= ======= ========
101 07-APR-2008 31-JAN-2009 15000 12000
101 01-FEB-2009 20-MAR-2009 20000 17000
an employee can have multiple lines:
each line should be treated separately:
Note: salary is calculated on [differnece of pay_due and pay_drawb] no. of days in each month based on the start and end dates)
solution is required by the query if possible otherwise any procedure be acceptable.
Solution is required:-
1) select employees first row/line criteria is based on its Start and End data and then data is splited from start_date to end_date by month title
and Pay_due and Pay_Drawn is calculted according to the days in a month and days are based on Start and End Dates.
like---
Start_Date:07-APR-2008
End_Date: 31-JAN-2009
In above case data is formatted from emp_id = 101
Calculates no. days in each month
OUTPUT SHOULD BE LIKE THiS:
---------------------------
Month Name: April May-08 June-08 July-08 Aug-08 Sep-08 Oct-08 Nov-08 Dec-08 Jan-09
No. of days: 24 31 30 31 31 30 31 30 31 31
Then caluclate the Pay_Due and Pay_Drawn according to the days in a particular month.
Thanx
Experts Please help out.....
Emp_ID Start_Date End_Date Pay_Due Pay_Drawn
===== ======== ======= ======= ========
101 07-APR-2008 31-JAN-2009 15000 12000
101 01-FEB-2009 20-MAR-2009 20000 17000
102 01-FEB-2008 15-APR-2009 10000 90000
Each employee can have multiple lines in the table. We have to calculate each employees salary difference (from pay_due to pay_drawn) between startate and
End_date calculation is based on the number of days in corresponding month and the differnece of pay_due and pay_drwan.
for example data is:-
Emp_ID Start_Date End_Date Pay_Due Pay_Drawn
===== ======== ======= ======= ========
101 07-APR-2008 31-JAN-2009 15000 12000
101 01-FEB-2009 20-MAR-2009 20000 17000
an employee can have multiple lines:
each line should be treated separately:
Note: salary is calculated on [differnece of pay_due and pay_drawb] no. of days in each month based on the start and end dates)
solution is required by the query if possible otherwise any procedure be acceptable.
Solution is required:-
1) select employees first row/line criteria is based on its Start and End data and then data is splited from start_date to end_date by month title
and Pay_due and Pay_Drawn is calculted according to the days in a month and days are based on Start and End Dates.
like---
Start_Date:07-APR-2008
End_Date: 31-JAN-2009
In above case data is formatted from emp_id = 101
Calculates no. days in each month
OUTPUT SHOULD BE LIKE THiS:
---------------------------
Month Name: April May-08 June-08 July-08 Aug-08 Sep-08 Oct-08 Nov-08 Dec-08 Jan-09
No. of days: 24 31 30 31 31 30 31 30 31 31
Then caluclate the Pay_Due and Pay_Drawn according to the days in a particular month.
Thanx
Experts Please help out.....
