<?php
-----------------------Final--------------------------------------
SELECT MainTable.EMP_ID,MainTable.NAME,(SUM(MainTable.FINANCE_FN_FINAL_AMOUNT)-(SUM(MainTable.TOTAL_ADVANCE_GIVEN)+SUM(MainTable.FTA_ADDITIONAL_ADJUSTMENT))) AS remainIngAmount,CONCAT(FinancialYearFromDate(MainTable.FORTNIGHT),'_',FinancialYearToDate(MainTable.FORTNIGHT))AS financialYear, COALESCE((SELECT fn_advances.ADVANCE AS TransactionAmount FROM fn_advances WHERE fn_advances.TRANSACTION_ID = CONCAT_WS("_",MainTable.EMP_ID, financialYear,"yearAdjustment")),0) AS TrasactionAmount FROM `fort_night_claims` AS MainTable GROUP BY MainTable.EMP_ID, financialYear

--------------------------end-------------------------------------------






SELECT subTable.* FROM (SELECT fort_night_claims.EMP_ID,fort_night_claims.NAME,(SUM(FINANCE_FN_FINAL_AMOUNT)-(SUM(TOTAL_ADVANCE_GIVEN)+SUM(FTA_ADDITIONAL_ADJUSTMENT))) AS remainIngAmount,CONCAT(FinancialYearFromDate(FORTNIGHT),'_',FinancialYearToDate(FORTNIGHT))AS financialYear FROM `fort_night_claims` GROUP BY EMP_ID, financialYear) AS subTable


SELECT * FROM fort_night_claims INNER JOIN (SELECT MAX(FROM_DATE) AS lastWorkingFortNightInCurrentLoopFinancialYear,temp.EMP_ID,temp.financialYear from (SELECT *,CONCAT(FinancialYearFromDate(FORTNIGHT),'_',FinancialYearToDate(FORTNIGHT)) AS financialYear FROM `fort_night_claims`) AS temp GROUP BY temp.EMP_ID, temp.financialYear) AS Table2
ON  fort_night_claims.EMP_ID= Table2.EMP_ID AND fort_night_claims.FROM_DATE=Table2.lastWorkingFortNightInCurrentLoopFinancialYear
WHERE fort_night_claims.FINAL_PAYABLE_VALUE <0


SELECT subTable.* FROM (SELECT *,SUM(FINANCE_APPROVED) AS totalFinanceApprovedAmount, SUM(TOTAL_ADVANCE_GIVEN) AS totalAdvanceGiven, SUM(FTA_ADDITIONAL_ADJUSTMENT) AS totalAdditionalAdjustments,CONCAT(FinancialYearFromDate(FORTNIGHT),'_',FinancialYearToDate(FORTNIGHT))AS financialYear  FROM `fort_night_claims`  GROUP BY EMP_ID, financialYear) AS subTable
WHERE  (subTable.totalFinanceApprovedAmount-(subTable.totalAdvanceGiven + subTable.totalAdditionalAdjustments)) < 0



SELECT *, mainTable.EMP_ID,CONCAT(FinancialYearFromDate(mainTable.FORTNIGHT),'_',FinancialYearToDate(mainTable.FORTNIGHT)) AS financialYear, (SELECT SubTable.FINAL_PAYABLE_VALUE FROM fort_night_claims AS SubTable WHERE SubTable.FROM_DATE < FinancialYearToDate(mainTable.FORTNIGHT) AND SubTable.EMP_ID=mainTable.EMP_ID ORDER BY SubTable.FROM_DATE DESC LIMIT 1 ) AS finalPaybleValue FROM fort_night_claims AS mainTable GROUP BY mainTable.EMP_ID,financialYear HAVING finalPaybleValue < 0
?>
SELECT MainTable.EMP_ID,MainTable.NAME,(SUM(MainTable.FINANCE_FN_FINAL_AMOUNT)-(SUM(MainTable.TOTAL_ADVANCE_GIVEN)+SUM(MainTable.FTA_ADDITIONAL_ADJUSTMENT))) AS remainIngAmount,CONCAT(FinancialYearFromDate(MainTable.FORTNIGHT),'_',FinancialYearToDate(MainTable.FORTNIGHT))AS financialYear, COALESCE((SELECT fn_advances.ADVANCE AS TransactionAmount FROM fn_advances WHERE fn_advances.TRANSACTION_ID = CONCAT_WS("_",MainTable.EMP_ID, financialYear,"yearAdjustment")),0) AS TrasactionAmount FROM `fort_night_claims` AS MainTable GROUP BY MainTable.EMP_ID, financialYear
