I have a query like -
SELECT
person_number,
pay_num,
reg_hours,
Hour3,
hour4,
Work_sched
FROM
(
-- a SUB QUERY
)
This is returning data like this:
person_number pay_num reg_hours Hour3 hour3_code hour4 hour4_code Work_sched
---------------------------------------------------------------------------------------------------------------
1 109 10 09 SCK 4 UNP Shift_a
1 109 8 JUR Shift_a
2 209 8 10 PTD 10 SOL Shift_b
2 209 6.5 REGR Shift_b
I want the reg_hours to be NULL for all cases, and the hour3 column should be added with one more row with the reg_hours value and code 'STR'.
Something like :
person_number pay_num reg_hours Hour3 hour3_code hour4 hour4_code Work_sched
1 109 09 SCK 4 UNP Shift_a
1 109 8 JUR Shift_a
1 109 10 STR Shift_a
2 209 10 PTD 10 SOL Shift_b
2 209 6.5 REGR Shift_b
2 209 8 STR Shift_b
I wrote this query for it:
SELECT
person_number,
pay_num,
NULL reg_hours,
CASE
WHEN reg_hours IS NOT NULL
THEN 'STR'
ELSE HOUR3_CODE
END AS HOUR3_CODE
CASE
WHEN reg_hours IS NOT NULL
THEN reg_hours
ELSE Hour3
END AS Hour3,
hour4,
Work_sched
FROM
(
-- a SUB QUERY
)
This is not returning the desired output, and is in fact replacing the existing hour3 and hour3_code instead of adding a line of reg_hours.
How to tweak this ?