I"m trying to figure how I can replace sub queries and optmize it? if you notice its calling same table multiple times , the only difference is the condition. Does it to be pivoted or partition? any example? my code is below. Notice I have different pay types ("REG",OVT") and Im creating seperate sub queires.
SELECT employee_no,
timecategoryid AS TimeCategoryID,
travel,
UREG,
REG,
OVT,
OVTU
FROM (SELECT E.employee_no,
PR1.pay_period_id,
PR1.timecategoryid,
PR1.travel,
(SELECT Sum(hours) AS Expr1 FROM dbo.pay_records AS PR
WHERE ( employee_no = E.employee_no ) AND ( pay_period_id = PR1.pay_period_id ) AND ( timecategoryid = PR1.timecategoryid) AND (valid_payroll = 0) and (rate_type = 'REG')
AND Travel = PR1.Travel
) AS UREG,
(SELECT Sum(hours) AS Expr1 FROM dbo.pay_records AS PR
WHERE ( employee_no = E.employee_no ) AND ( pay_period_id = PR1.pay_period_id ) AND ( timecategoryid = PR1.timecategoryid) AND (valid_payroll = 1) and (rate_type = 'REG')
) AS REG,
(SELECT Sum(hours) AS Expr1 FROM dbo.pay_records AS PR
WHERE ( employee_no = E.employee_no ) AND ( pay_period_id = PR1.pay_period_id ) AND ( timecategoryid = PR1.timecategoryid) AND (valid_payroll = 0) and (rate_type = 'OVT')
AND Travel = PR1.Travel
) AS OVTU,
(SELECT Sum(hours) AS Expr1 FROM dbo.pay_records AS PR
WHERE ( employee_no = E.employee_no ) AND ( pay_period_id = PR1.pay_period_id ) AND ( timecategoryid = PR1.timecategoryid) AND (valid_payroll = 1) and (rate_type = 'OVT')
) AS OVT
FROM dbo.employee AS E
INNER JOIN dbo.pay_records AS PR1
ON PR1.employee_no = E.employee_no
GROUP BY E.employee_no,
PR1.travel,
PR1.pay_period_id,
PR1.timecategoryid) AS X
WHERE employee_no = '327'
AND pay_period_id = 16
GROUP BY employee_no,
timecategoryid,
travel,
UREG,
REG,
OVT,
OVTU
It's tough to verify without test data but the following appears to be logically equivalent to your query and should be perform much better...
SELECT
prx.employee_no,
prx.timecategoryid,
prx.travel,
UREG = MAX(CASE WHEN prx.valid_payroll = 0 AND prx.rate_type = 'REG' THEN prx.travel_hours END),
REG = MAX(CASE WHEN prx.valid_payroll = 1 AND prx.rate_type = 'REG' THEN prx.timecard_hours END),
OVYU = MAX(CASE WHEN prx.valid_payroll = 0 AND prx.rate_type = 'OVT' THEN prx.travel_hours END),
OVT = MAX(CASE WHEN prx.valid_payroll = 1 AND prx.rate_type = 'OVT' THEN prx.timecard_hours END)
FROM
( SELECT
pr.employee_no,
pr.timecategoryid,
pr.travel,
pr. pay_period_id,
pr.rate_type,
pr.valid_payroll,
travel_hours = SUM(pr.[hours]) OVER (PARTITION BY pr.employee_no, pr.timecategoryid, pr.valid_payroll, pr.travel),
timecard_hours = SUM(pr.[hours]) OVER (PARTITION BY pr.employee_no, pr.timecategoryid, pr.valid_payroll)
FROM
dbo.pay_records pr
WHERE
prx.employee_no = 327
AND prx.pay_period_id = 16
) prx
GROUP BY
prx.employee_no,
prx.timecategoryid,
prx.travel;
Also... A good "POC" covering index should help a great deal as well... Something like the following...
CREATE NONCLUSTERED INDEX ix_PayRecords_employeeno, payperiodid, timecategoryid, valid_payroll, travel ON dbo.pay_records (
employee_no,
pay_period_id,
timecategoryid,
valid_payroll,
travel
)
INCLUDE (
[hours]
);
This is exactly what I was looking for..thank you so much. Here is the question . When I filter by payperiod from outside query it doesn't give me the desired result only when payperiod is filtered(where condition) from inner query as shown in your example, it calculates correctly. Any idea how I can filter from outside query and so it filters the inner query.
The reason I need to filter from outside query instead of inner query is because I'm converting it to view statement and It can filter by outside query. So basically this is what I have done.
SELECT
prx.employee_no,
prx.PayPeriodID,
prx.Name,
prx.ID as TimeCategoryID,
prx.Travel,
prx.valid_payroll,
REGU = Max(COALESCE(CASE WHEN prx.valid_payroll = 0 AND prx.rate_type = 'REG' THEN prx.Sum_hours END,0)),
REG = Max(COALESCE(CASE WHEN prx.valid_payroll = 1 AND prx.rate_type = 'REG' THEN prx.Sum_hours END,0)),
OVTU = Max(COALESCE(CASE WHEN prx.valid_payroll = 0 AND prx.rate_type = 'OVT' THEN prx.Sum_hours END,0)),
OVT = Max(COALESCE(CASE WHEN prx.valid_payroll = 1 AND prx.rate_type = 'OVT' THEN prx.Sum_hours END,0))
FROM
( SELECT Distinct
E.employee_no,
pp.id as PayPeriodID,
TC.Name,
TC.ID,
PR.Travel,
pr.rate_type,
pr.valid_payroll,
Sum_hours = SUM(pr.[hours]) OVER (PARTITION BY pr.employee_no, pr.timecategoryid, pr.travel, pr.rate_type,pr.valid_payroll)
FROM
dbo.pay_records pr
INNER JOIN
Employee E on pr.employee_no=E.employee_no
INNER JOIN
TimeCategory tc ON TC.ID=pr.TimeCategoryID
INNER JOIN
Pay_Period pp on pp.ID= pr.pay_period_id
) prx
Where prx.PayPeriodID=16
Group by
prx.employee_no,
prx.Name,
prx.ID,
prx.Travel,
prx.valid_payroll,
prx.PayPeriodID
If if place where condition "Where prx.PayPeriodID=16" inside prx query it works perfectly. but outside it doesn't properly sum the hours. Any suggestions?
Try adding PayPeriodID to the PARTITION BY clauses of both of the windowed sums.
Another option, that may work better than a view, would be to create this as an inline table valued function (iTVF) having employee_no & pay_period_id as parameter values... I'd try writing both and do some performance testing.