July 15, 2016 at 10:31 am
SELECT EmployeeCode,Name,Allowance,Allow,Deduction,Dedu
FROM (
SELECT E.EmployeeCode
,E.Name
,AA.Descriptions AS Allowance
,A1.AllowanceAmount
,'' AS Deduction
,'' AS DeductionAmt
,'Allow' AS Type1
,'' AS Type
FROM HEmployee E
INNER JOIN MonthlyActivity M ON E.EmployeeCode = M.Empcode
INNER JOIN HEmpMonthlyPayBill Mo ON E.HEmployeeID = Mo.EmployeeID AND Mo.IsActive = 1 AND PayDate = '2016-07-01'
INNER JOIN HHeadAllowanceData A ON E.HEmployeeID = A.EmployeeID AND A.Amount>0
INNER JOIN HHeadAllowance AA ON A.HeadAllowanceID = AA.HHeadAllowanceID
INNER JOIN HEmpMonthAllowance A1 ON Mo.PayBillID = A1.PayBillID AND AA.HHeadAllowanceID = A1.HHeadAllowanceID
WHERE E.EmployeeCode = '1'
UNION ALL
SELECT E.EmployeeCode
,E.Name
,'' AS Allowance
,'' AS AllowanceAmount
,Du.Descriptions AS Deduction
,D1.DeductionAmount AS DeductionAmt
,'' AS Type1
,'Dedu' AS type
FROM HEmployee E
INNER JOIN MonthlyActivity M ON E.EmployeeCode = M.Empcode
INNER JOIN HEmpMonthlyPayBill Mo ON E.HEmployeeID = Mo.EmployeeID AND Mo.IsActive = 1 AND PayDate = '2016-07-01'
INNER JOIN HEmpMonthDeduction D1 ON Mo.PayBillID = D1.PayBillID AND D1.DeductionAmount>0
INNER JOIN HHeadDeduction Du ON D1.HHeadDeductionID = Du.HHeadDeductionID
WHERE E.EmployeeCode = '1'
) AS A
PIVOT
( SUM(AllowanceAmount) FOR Type1 IN([Allow])
) AS Pvt
PIVOT
( SUM(DeductionAmt) FOR Type IN(Dedu)
) AS Pvt1
OutPut
[p]EmpCode NameAllowanceAllowDeductionDedu
1ALLIPUSPAMNULLPF696
1ALLIPUSPAMNULLPF ER696
1ALLIPUSPAMNULLStaff Fund58
1ALLIPUSPAMBasic Pay4447NULL
1ALLIPUSPAMCity767NULL
1ALLIPUSPAMFTA575NULL
1ALLIPUSPAMH R A889NULL
1ALLIPUSPAMMA889NULL
1ALLIPUSPAMOthers214NULL
1ALLIPUSPAMPF Earn534NULL
1ALLIPUSPAMPhone115NULL[/p]
Expected Output
-------------------
Remove Null Rows in Allowance Column
July 15, 2016 at 10:57 am
You haven't written a question, provided any test data, or explained what you want.
Please provide information, and don't use the title as your question. We can't read the entire text and it doesn't help.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply