August 11, 2016 at 10:46 am
I'm having issues putting case statement inside the PIVOT.
What I'm trying to achieve is to Sum the "Hours" OR "ActualTime" based on the condition.
So if the employee's pay type is not equal to 4 then use [ActualTime] Field else use [Hours] Field.
I'm listing two snippets of query, one which is a working SQL with
no condition and one where I have the condition. I can't get to work with the condition.
This one works just fine
SELECT pp_id AS pay_period_id, start_period, end_period,
employee_no,
reg,
ovt,
pay_type
FROM (SELECT pp.id AS pp_id,
pr.employee_no, start_period, end_period,
[HOURS],
rate_type,
e.pay_type
FROM pay_records AS pr
INNER JOIN pay_period pp
ON pr.[date] BETWEEN pp.start_period AND pp.end_period
AND valid_payroll = 1
INNER JOIN employee E
ON E.Employee_no=pr.employee_no
) AS p
PIVOT (Sum([HOURS])
FOR rate_type IN ([REG],
[OVT])) AS pv
--- The one below doesn't work.
SELECT pp_id AS pay_period_id, start_period, end_period,
employee_no,
reg,
ovt,
pay_type
FROM (SELECT pp.id AS pp_id,
pr.employee_no, start_period, end_period,
CASE WHEN e.pay_type <> 4 THEN [ActualTime] ELSE [HOURS] END,
rate_type,
e.pay_type
FROM pay_records AS pr
INNER JOIN pay_period pp
ON pr.[date] BETWEEN pp.start_period AND pp.end_period
AND valid_payroll = 1
INNER JOIN employee E
ON E.Employee_no=pr.employee_no
) AS p
PIVOT (Sum(CASE WHEN e.pay_type <> 4 THEN [ActualTime] ELSE [HOURS] END)
FOR rate_type IN ([REG],
[OVT])) AS pv
Please advise
August 11, 2016 at 11:15 am
Here's my suggestion:
WITH CTE AS(
SELECT pp.id AS pay_period_id,
pr.employee_no,
start_period,
end_period,
SUM(CASE WHEN e.pay_type <> 4 THEN [ActualTime] ELSE [HOURS] END) AS PayTime,
rate_type,
e.pay_type
FROM pay_records AS pr
JOIN pay_period AS pp ON pr.[date] BETWEEN pp.start_period AND pp.end_period
AND valid_payroll = 1
JOIN employee AS E ON E.Employee_no=pr.employee_no
GROUP BY pp.id,
pr.employee_no,
start_period,
end_period,
rate_type,
e.pay_type
)
SELECT pay_period_id,
start_period,
end_period,
employee_no,
SUM( CASE WHEN rate_type = 'REG' THEN PayTime ELSE 0 END) AS REG,
SUM( CASE WHEN rate_type = 'OVT' THEN PayTime ELSE 0 END) AS OVT,
pay_type
FROM CTE
GROUP BY pay_period_id,
start_period,
end_period,
employee_no,
pay_type;
Of course, you could simply add an alias to the column with the CASE statement and use that alias in the column. But you'll keep the restrictive PIVOT.
August 11, 2016 at 11:25 am
Your both suggestions worked!.
I just tweaked my query and used alias inside the Sum .
Thank you so much!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply