问题描述:

I have Following MySQL Query.How can I optimize this.

Basically what this query is doing .


I have two different table for employee and his pay history, I need to show the sum of national insurance contribution for each employee and for each month as column.

I am able to acheive this functionality but I dont think the approach is right.
How can I modify this statement in light of performance.

DECLARE firstStartDate DATETIME;

SET firstStartDate = GetFirstPeriodStartDateForFiscalYear(iGroupID,iFiscalYear);

SELECT

(SELECT fullname) as 'Employee Name',

(SELECT SUM(EE_NI_Contrib)

FROM eepayhistory

WHERE eepayhistory.employee_id = Employee.pkEmpid

AND MONTHNAME(firstStartDate)=MONTHNAME(PeriodEndDate)) as 'Mon1',

(SELECT SUM(EE_NI_Contrib)

FROM eepayhistory

WHERE eepayhistory.employee_id = Employee.pkEmpid

AND MONTHNAME(DATE_ADD(firstStartDate, INTERVAL 1 MONTH) )=MONTHNAME(PeriodEndDate)) as 'Mon2' ,

(SELECT SUM(EE_NI_Contrib)

FROM eepayhistory

WHERE eepayhistory.employee_id = Employee.pkEmpid

AND MONTHNAME(DATE_ADD(firstStartDate, INTERVAL 2 MONTH) )=MONTHNAME(PeriodEndDate)) as 'Mon3' ,

(SELECT SUM(EE_NI_Contrib)

FROM eepayhistory

WHERE eepayhistory.employee_id = Employee.pkEmpid

AND MONTHNAME(DATE_ADD(firstStartDate, INTERVAL 3 MONTH) )=MONTHNAME(PeriodEndDate)) as 'Mon4' ,

(SELECT SUM(EE_NI_Contrib)

FROM eepayhistory

WHERE eepayhistory.employee_id = Employee.pkEmpid

AND MONTHNAME(DATE_ADD(firstStartDate, INTERVAL 4 MONTH) )=MONTHNAME(PeriodEndDate)) as 'Mon5' ,

(SELECT SUM(EE_NI_Contrib)

FROM eepayhistory

WHERE eepayhistory.employee_id = Employee.pkEmpid

AND MONTHNAME(DATE_ADD(firstStartDate, INTERVAL 5 MONTH) )=MONTHNAME(PeriodEndDate)) as 'Mon6' ,

(SELECT SUM(EE_NI_Contrib)

FROM eepayhistory

WHERE eepayhistory.employee_id = Employee.pkEmpid

AND MONTHNAME(DATE_ADD(firstStartDate, INTERVAL 6 MONTH) )=MONTHNAME(PeriodEndDate)) as 'Mon7' ,

(SELECT SUM(EE_NI_Contrib)

FROM eepayhistory

WHERE eepayhistory.employee_id = Employee.pkEmpid

AND MONTHNAME(DATE_ADD(firstStartDate, INTERVAL 7 MONTH) )=MONTHNAME(PeriodEndDate)) as 'Mon8' ,

(SELECT SUM(EE_NI_Contrib)

FROM eepayhistory

WHERE eepayhistory.employee_id = Employee.pkEmpid

AND MONTHNAME(DATE_ADD(firstStartDate, INTERVAL 8 MONTH) )=MONTHNAME(PeriodEndDate)) as 'Mon9' ,

(SELECT SUM(EE_NI_Contrib)

FROM eepayhistory

WHERE eepayhistory.employee_id = Employee.pkEmpid

AND MONTHNAME(DATE_ADD(firstStartDate, INTERVAL 9 MONTH) )=MONTHNAME(PeriodEndDate)) as 'Mon10' ,

(SELECT SUM(EE_NI_Contrib)

FROM eepayhistory

WHERE eepayhistory.employee_id = Employee.pkEmpid

AND MONTHNAME(DATE_ADD(firstStartDate, INTERVAL 10 MONTH) )=MONTHNAME(PeriodEndDate)) as 'Mon11' ,

(SELECT SUM(EE_NI_Contrib)

FROM eepayhistory

WHERE eepayhistory.employee_id = Employee.pkEmpid

AND MONTHNAME(DATE_ADD(firstStartDate, INTERVAL 11 MONTH) )=MONTHNAME(PeriodEndDate)) as 'Mon12'

FROM Employee

LEFT OUTER JOIN eepayhistory

ON eepayhistory.employee_id = Employee.pkEmpid

AND Employee.CompanyId = iCompanyID

Group by 'Employee Name';

How can I optimize this query.

相关阅读:
Top