Alternative way to replace Sub queries?

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

Please post create table script with insert statements with sample data.

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]
	);
1 Like

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.

1 Like

You are absolutely right. Adding PayPeriodID did the trick. The performance is so much better with partition than what I had with sub queries!

Thank you !!

1 Like

Thank you for the feedback. Glad to help. :slight_smile: