SELECT |
b.ed_code, b.worker_id, b.sin_no, a.sin_dup_count, b.last_name, b.first_name, |
b.apt_no,b.address,b.city,b.prov_code, b.postal_code, b.birth_date, |
c.data_type,c.position_id,c.position_desc,c.trans_date,c.total_hrs,c.payabl e_t4,c.kilometers,c.payable_expenses,c.payable_expenses_oth, |
fieldhq_ind |
FROM |
( select sin_no, count(distinct ed_code) sin_dup_count |
from worker |
where sin_no <> 0 |
group by sin_no |
HAVING COUNT(distinct ed_code) > 1 ) a, |
worker b, |
( |
select |
ed_code,worker_id,data_type,position_id,position_desc,trans_date,total_hrs, payable_t4,kilometers,payable_expenses,payable_expenses_oth, fieldhq_ind |
from |
( SELECT |
e.ed_code, e.worker_id, e.position_id, f.desc_e position_desc, CASE WHEN temp_query.requisition_paid >= '2015-11-27 13:39:10.383' THEN 1 ELSE 0 END as fieldhq_ind, |
temp_query.data_type, temp_query.trans_date, temp_query.total_hrs, temp_query.payable_t4, temp_query.kilometers, temp_query.payable_expenses, temp_query.payable_expenses_oth |
FROM |
( SELECT ed_code, worker_id, position_id, '1-Timesheet' data_type, worked_date trans_date, id, total_hrs, payable payable_t4, NULL kilometers, NULL payable_expenses, NULL payable_expenses_oth, requisition_paid |
FROM timesheets WHERE requisition_paid IS NOT NULL |
UNION ALL |
SELECT ed_code, worker_id, position_id, '2-Training' data_type, training_date trans_date, 1, 2.0 total_hrs, payable payable_t4, NULL kilometers, NULL payable_expenses, NULL payable_expenses_oth, requisition_paid |
FROM positions_worked pw WHERE attended_training ='Y' and requisition_paid IS NOT NULL |
UNION ALL |
SELECT ed_code, worker_id, position_id, '3-Expenses (Kilometers)' data_type, expense_date trans_date, id, NULL total_hrs, NULL payable_t4, kilometers, payable payable_expenses, NULL payable_expenses_oth, requisition_paid |
FROM expenses WHERE requisition_paid IS NOT NULL AND tariff_id IN (41,50) |
UNION ALL |
SELECT ed_code, worker_id, position_id, '4-Other Expenses (Cell Phone)' data_type, expense_date trans_date, id, NULL total_hrs, NULL payable_t4, NULL kilometers,NULL payable_expenses, payable payable_expenses_oth, requisition_paid |
FROM expenses WHERE requisition_paid IS NOT NULL AND tariff_id NOT IN (41,50) |
) temp_query, |
positions_worked e, positions f |
WHERE e.ed_code = temp_query.ed_code |
AND e.worker_id = temp_query.worker_id |
AND e.position_id = temp_query.position_id |
AND e.position_id = f.position_id |
) cc |
) c |
WHERE a.sin_no = b.sin_no |
AND b.ed_code *= c.ed_code |
AND b.worker_id *= c.worker_id |
ORDER BY b.sin_no, b.ed_code, b.worker_id, c.data_type,c.position_id,c.trans_date; |