1
\$\begingroup\$

I have written a query to generate payroll based on attendance for a month, for about 4000 employees. It takes quite a long time to execute. Basically, what I am doing is to joining two different queries using a common key for a single line of output per employee. But some blog suggests that UNION on this type of join would perform better. Is there any way to reduce time with my current query, or would UNION help?

DECLARE @EmployeePayrollDataTable AS TABLE (EmployeeId int,LegalOverTime int,ExtraOverTime int,ToatalOverTime int,OK int,LateDays int,
        AbsentDays int, LeaveDays int, HolidayOffdays int,BasicPay decimal,GrossPay decimal,HouseRent decimal,MedicalFoodTransportCost decimal,
        BasicPayRate decimal,GrossPayRate decimal,OverTimeRate float,AttendanceBonus decimal,StampDeduction decimal);

DECLARE @EmployeePayrollTable AS TABLE (EmployeeId int,BasicSalary decimal,HouseRent decimal,MedicalFoodTransportCost decimal,GrossSalary decimal, TotalPresents int,
        HolidayOffdays int, LeaveDays int,AbsentDays int, TotalDeduction decimal, TotalSalary decimal, AttendanceBonus decimal, OvertimeHours int, OvertimeRate float,
        OvertimeAmount decimal, StampCharge decimal, FinalSalary decimal)

INSERT INTO @EmployeePayrollDataTable
SELECT A.EmployeeId,A.LegalOverTime,A.ExtraOverTime,A.ToatalOverTime,A.OK,A.LateDays,A.AbsentDays,A.LeaveDays,A.HolidayOffdays,
   B.BasicPay,B.GrossPay,B.HouseRent,B.MedicalFoodTransportCost,B.BasicPayRate,B.GrossPayRate,B.OverTimeRate,B.AttendanceBonus,B.StampDeduction FROM 
        (SELECT TT.EmployeeId, 
        SUM(TT.LegalOverTime) AS LegalOverTime,
        SUM(TT.ExtraOverTime) AS ExtraOverTime, 
        SUM(TT.LegalOverTime)+SUM(TT.ExtraOverTime) AS ToatalOverTime,
        ISNULL(COUNT(OK), 0 ) OK,
        ISNULL(COUNT(LateDays), 0 ) LateDays,
        ISNULL(COUNT(AbsentDays), 0 ) AbsentDays,
        ISNULL(COUNT(LeaveDays), 0 ) LeaveDays,
        ISNULL(COUNT(HolidayOffdays), 0 ) HolidayOffdays
FROM
(SELECT HAH.EmployeeId,
        CASE WHEN HAH.AttendanceStatus!=5 OR HAH.AttendanceStatus!=6 THEN 
                CASE WHEN HAH.PayableOverTime<=2 THEN HAH.PayableOverTime
                WHEN HAH.PayableOverTime>2 THEN 2
                ELSE 0
                END
        ELSE 0
        END AS LegalOverTime,

        CASE WHEN HAH.AttendanceStatus!=5 OR HAH.AttendanceStatus!=6 THEN 
                CASE WHEN HAH.PayableOverTime<=2 THEN 0
                WHEN HAH.PayableOverTime>2 THEN HAH.PayableOverTime-2
                ELSE 0
                END
        ELSE HAH.PayableOverTime
        END AS ExtraOverTime,
        CASE WHEN 
        (AttendanceStatus=1 and HAH.[Status]=1) THEN AttendanceStatus END AS OK,
        CASE WHEN 
        (AttendanceStatus=2 and HAH.[Status]=1) THEN AttendanceStatus END AS LateDays,
        CASE WHEN 
        (AttendanceStatus=3 and HAH.[Status]=1) THEN AttendanceStatus END AS AbsentDays,
        CASE WHEN 
        (AttendanceStatus=4 and HAH.[Status]=1) THEN AttendanceStatus END AS LeaveDays,
        CASE WHEN 
        ((AttendanceStatus=5 or AttendanceStatus=6) and HAH.[Status]=1) THEN AttendanceStatus END AS HolidayOffdays

FROM HRMS_Attendance_History as HAH
JOIN HRMS_Employee HE on HAH.EmployeeId=HE.ID
WHERE HE.Present_Status=1 and CAST(HE.Joining_Date as Date)<='2019-04-01' and CAST([Date] as Date)>='2019-04-01' and CAST([Date] as Date)<='2019-04-30') TT
GROUP BY TT.EmployeeId)  A
JOIN 
(SELECT
T.EmployeeId,
ISNULL(SUM(BasicPay), 0 ) BasicPay,
ISNULL(SUM(GrossPay), 0 ) GrossPay,
ISNULL(SUM(HouseRent), 0 ) HouseRent,
ISNULL(SUM(MedicalFoodTransportCost), 0 ) MedicalFoodTransportCost,
ISNULL(SUM(BasicPay)/30, 0 ) BasicPayRate,
ISNULL(SUM(GrossPay)/30, 0 ) GrossPayRate,
ISNULL(SUM(OverTimeRate), 0 ) OverTimeRate,
ISNULL(SUM(AttendanceBonus), 0 ) AttendanceBonus,
ISNULL(SUM(StampDeduction), 0 ) StampDeduction 
FROM (SELECT EmployeeId,
CASE WHEN 
(Eod_RefFk=1 and er.[Status]=1) THEN ActualAmount END AS BasicPay,
CASE WHEN 
((Eod_RefFk=1 or Eod_RefFk=2 or Eod_RefFk=3) and er.[Status]=1) THEN ActualAmount END AS GrossPay,
CASE WHEN 
(Eod_RefFk=2 and er.[Status]=1) THEN ActualAmount END AS HouseRent,
CASE WHEN 
(Eod_RefFk=3 and er.[Status]=1) THEN ActualAmount END AS MedicalFoodTransportCost,
CASE WHEN 
(Eod_RefFk=11 and er.[Status]=1) THEN ActualAmount END AS OverTimeRate,
CASE WHEN 
(Eod_RefFk=7 and er.[Status]=1) THEN ActualAmount END AS AttendanceBonus,
CASE WHEN 
(Eod_RefFk=12 and er.[Status]=1) THEN ActualAmount END AS StampDeduction
FROM HRMS_EodRecord er JOIN HRMS_Employee e on er.EmployeeId=e.ID WHERE e.Present_Status=1 and CAST(e.Joining_Date as Date)<='2019-04-01') t 
group by t.EmployeeId) B ON A.EmployeeId=B.EmployeeId


INSERT INTO @EmployeePayrollTable
SELECT  EmployeeId,
    BasicPay,
    HouseRent,
    MedicalFoodTransportCost,
    GrossPay,
    (OK+LateDays+LeaveDays+HolidayOffdays),
    HolidayOffdays,
    LeaveDays,
    AbsentDays,
    AbsentDays*BasicPayRate,
    GrossPay-(AbsentDays*BasicPayRate),
    CASE WHEN AbsentDays>0 or LeaveDays>0 or LateDays>2 THEN 0
    ELSE AttendanceBonus END AS AttendanceBonus,
    LegalOverTime,
    OverTimeRate,
    LegalOverTime*OverTimeRate,
    StampDeduction, 
    CASE WHEN AbsentDays>0 or LeaveDays>0 or LateDays>2 THEN(LegalOverTime*OverTimeRate)+(GrossPay-(AbsentDays*BasicPayRate))-StampDeduction
    ELSE (LegalOverTime*OverTimeRate)+(GrossPay-(AbsentDays*BasicPayRate)+AttendanceBonus)-StampDeduction END AS OvertimeAmount
FROM @EmployeePayrollDataTable 


select * from @EmployeePayrollTable
Improve this question
\$\endgroup\$

1 Answer 1

Reset to default
2
\$\begingroup\$

Without specific explain plan details, consider these broad stroke recommendations. Currently, I do not see how UNION fits into your needs as you are essentially joining two conditional aggregation queries. And due to re-use of calculated columns, you require sub-select derived tables.

  1. Of course as always, analyze each SELECT queries' explain plan and add necessary indexes on JOIN fields such as EmployeeId across the tables.

  2. Avoid unnecessary date casting such as with date fields in WHERE clauses using CAST(... AS Date). If date fields are already date or datetime, simply compare directly using the ISO-8601 standard format ('YYYYMMDD') that is agnostic to language and regional settings. If these fields are not dates, consider changing them with ALTER.

    WHERE HE.Present_Status = 1 
  AND HE.Joining_Date <= '20190401'
  AND [Date] >= '20190401' 
  AND [Date] <= '20190430'

  3. Avoid unnecessary ISNULL(..., 0) conversion. Instead use a 1 or 0 conditional calculation and then run SUM on top level query which are essentially counts of the boolean flags:

    CASE WHEN (AttendanceStatus=1 AND HAH.[Status]=1) THEN 1 ELSE 0 END AS OK,
CASE WHEN (AttendanceStatus=2 AND HAH.[Status]=1) THEN 1 ELSE 0 END AS LateDays,
CASE WHEN (AttendanceStatus=3 AND HAH.[Status]=1) THEN 1 ELSE 0 END AS AbsentDays,
CASE WHEN (AttendanceStatus=4 AND HAH.[Status]=1) THEN 1 ELSE 0 END AS LeaveDays,

...

SUM(TT.LateDays) AS LateDays,
SUM(TT.AbsentDays) AS AbsentDays,
SUM(TT.LeaveDays) AS LeaveDays,
SUM(TT.HolidayOffdays) AS HolidayOffdays

  4. Consider a CTE instead of a table variable to bypass the DECLARE and INSERT lines. However, do note this may depend on your situation.

    WITH EmployeePayrollDataTable AS
  (...inner join of A and B aggregate subqueries...)

SELECT EmployeeId, BasicPay, HouseRent, MedicalFoodTransportCost, GrossPay,
       (OK+LateDays+LeaveDays+HolidayOffdays), HolidayOffdays, LeaveDays,
       AbsentDays, AbsentDays*BasicPayRate,  GrossPay-(AbsentDays*BasicPayRate),
       CASE WHEN AbsentDays>0 or LeaveDays>0 or LateDays>2 
            THEN 0
            ELSE AttendanceBonus 
       END AS AttendanceBonus,
       LegalOverTime, OverTimeRate, LegalOverTime*OverTimeRate, StampDeduction, 
       CASE WHEN AbsentDays>0 or LeaveDays>0 or LateDays>2 
            THEN (LegalOverTime*OverTimeRate) +
                 (GrossPay-(AbsentDays*BasicPayRate))-StampDeduction
            ELSE (LegalOverTime*OverTimeRate) + 
                 (GrossPay-(AbsentDays*BasicPayRate) + AttendanceBonus) -
                 StampDeduction 
      END AS OvertimeAmount
FROM EmployeePayrollDataTable

  5. Since you use INNER JOIN across all SELECT queries and levels, you can conceivably join both aggregates to avoid the main query's JOIN and facilitate readability. 

    SELECT ...all unit level CASE statements...

FROM HRMS_Attendance_History HAH
JOIN HRMS_Employee HE ON HAH.EmployeeId = HE.ID
JOIN HRMS_EodRecord er ON HAH.EmployeeId = er.EmployeeId
JOIN HRMS_Employee e ON er.EmployeeId = e.ID                 
WHERE HE.Present_Status = 1 
  AND HE.Joining_Date <= '20190401'
  AND [Date] >= '20190401' 
  AND [Date] <= '20190430'
  AND e.Present_Status = 1
          AND e.Joining_Date as Date <= '20190401'

Overall re-factoring of SQL:

WITH EmployeePayrollDataTable AS
  (SELECT A.EmployeeId, A.LegalOverTime, A.ExtraOverTime, A.ToatalOverTime, 
          A.OK, A.LateDays, A.AbsentDays, A.LeaveDays, A.HolidayOffdays,
          A.BasicPay, A.GrossPay, A.HouseRent, A.MedicalFoodTransportCost, A.BasicPayRate, 
          A.GrossPayRate, A.OverTimeRate, A.AttendanceBonus, A.StampDeduction 
   FROM 
        (SELECT TT.EmployeeId, 
                SUM(TT.LegalOverTime) AS LegalOverTime,
                SUM(TT.ExtraOverTime) AS ExtraOverTime, 
                SUM(TT.LegalOverTime) + SUM(TT.ExtraOverTime) AS ToatalOverTime,
                SUM(TT.OK) AS OK,
                SUM(TT.LateDays) AS LateDays,
                SUM(TT.AbsentDays) AS AbsentDays,
                SUM(TT.LeaveDays) AS LeaveDays,
                SUM(TT.HolidayOffdays) AS HolidayOffdays,

                SUM(TT.BasicPay) AS BasicPay,
                SUM(TT.GrossPay) AS GrossPay,
                SUM(TT.HouseRent) AS HouseRent,
                SUM(TT.MedicalFoodTransportCost) AS MedicalFoodTransportCost,
                SUM(TT.BasicPay)/30 AS BasicPayRate,
                SUM(TT.GrossPay)/30 AS GrossPayRate,
                SUM(TT.OverTimeRate) AS OverTimeRate,
                SUM(TT.AttendanceBonus) AS AttendanceBonus,
                SUM(TT.StampDeduction) AS StampDeduction 
        FROM
                (SELECT HAH.EmployeeId,
                        CASE WHEN HAH.AttendanceStatus!=5 OR HAH.AttendanceStatus!=6 
                             THEN 
                                  CASE WHEN HAH.PayableOverTime <= 2 THEN HAH.PayableOverTime
                                       WHEN HAH.PayableOverTime > 2 THEN 2
                                       ELSE 0
                                  END
                             ELSE 0
                        END AS LegalOverTime,

                        CASE WHEN HAH.AttendanceStatus!=5 OR HAH.AttendanceStatus!=6 
                             THEN 
                                  CASE WHEN HAH.PayableOverTime <= 2 THEN 0
                                       WHEN HAH.PayableOverTime > 2 THEN HAH.PayableOverTime-2
                                       ELSE 0
                                  END
                            ELSE HAH.PayableOverTime
                        END AS ExtraOverTime,

                        CASE WHEN (AttendanceStatus=1 AND HAH.[Status]=1) THEN 1 ELSE 0 END AS OK,
                        CASE WHEN (AttendanceStatus=2 AND HAH.[Status]=1) THEN 1 ELSE 0 END AS LateDays,
                        CASE WHEN (AttendanceStatus=3 AND HAH.[Status]=1) THEN 1 ELSE 0 END AS AbsentDays,
                        CASE WHEN (AttendanceStatus=4 AND HAH.[Status]=1) THEN 1 ELSE 0 END AS LeaveDays,
                        CASE WHEN ((AttendanceStatus=5 OR AttendanceStatus=6) AND HAH.[Status]=1) THEN AttendanceStatus END AS HolidayOffdays,

                        CASE WHEN (Eod_RefFk=1 AND er.[Status]=1) THEN ActualAmount ELSE 0 END AS BasicPay,
                        CASE WHEN ((Eod_RefFk=1 OR Eod_RefFk=2 OR Eod_RefFk=3) AND er.[Status]=1) THEN ActualAmount ELSE 0 END AS GrossPay,
                        CASE WHEN (Eod_RefFk=2 AND er.[Status]=1) THEN ActualAmount ELSE 0 END AS HouseRent,
                        CASE WHEN (Eod_RefFk=3 AND er.[Status]=1) THEN ActualAmount ELSE 0 END AS MedicalFoodTransportCost,
                        CASE WHEN (Eod_RefFk=11 AND er.[Status]=1) THEN ActualAmount ELSE 0 END AS OverTimeRate,
                        CASE WHEN (Eod_RefFk=7 AND er.[Status]=1) THEN ActualAmount ELSE 0 END AS AttendanceBonus,
                        CASE WHEN (Eod_RefFk=12 AND er.[Status]=1) THEN ActualAmount ELSE 0 END AS StampDeduction

                FROM HRMS_Attendance_History HAH
                JOIN HRMS_Employee HE ON HAH.EmployeeId = HE.ID
                JOIN HRMS_EodRecord er ON HAH.EmployeeId = er.EmployeeId
                JOIN HRMS_Employee e ON er.EmployeeId=e.ID 
                WHERE HE.Present_Status = 1 
                  AND HE.Joining_Date <= '20190401'
                  AND [Date] >= '20190401' 
                  AND [Date] <= '20190430'
                  AND e.Present_Status = 1
                  AND e.Joining_Date as Date <= '20190401'
               ) TT
        GROUP BY TT.EmployeeId)  A
  )
Improve this answer
\$\endgroup\$
3
  • \$\begingroup\$ thanks @Parfait for your analysis and answer.Your query very nice to remove some redundant cast and ISNULL expression with simple if else. However, I can't get required result using single join as you had answered. Basically problem is you join Salary table with attendance and then SUM. The problem is this sum produce huge figure as it not represent single salary rather than 30 same salary due to attendance of 30 days. For example. If basic salary 5000 then 30 days sum make (5000*30)=150000 which is incorrect. \$\endgroup\$
    – Parvez
    May 27, 2019 at 6:34
  • \$\begingroup\$ I solved this using MAX rather than SUM. \$\endgroup\$
    – Parvez
    May 27, 2019 at 6:45
  • \$\begingroup\$ Great to hear! Yes, this was untested solution without data but glad you can work it for final solution. Please don't forget the StackExchange way of saying thanks if solution helped of course! \$\endgroup\$
    – Parfait
    May 27, 2019 at 19:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.