create view test (
customer_key,
customer_name,
project_key,
project_code,
task_key,
task_name,
task_number,
quantity,
budget_hours,
extended_cost,
budget_dollars,
amount,
budget_dollars_cap,
cost_date,
primary_key,
project_hours,
project_labors,
project_expenses
)
as
select c.customer_key,
c.customer_name,
pr.project_key,
pr.project_code,
ptd.task_key,
t.task_name,
t.sequence,
ptd.quantity,
t.budget_hours,
ptd.bill_rate * ptd.quantity * charindex(prt.billable,'Y'),
t.budget_dollars_op,
0,
t.budget_dollars_cap,
ptd.work_date,
ptd.time_data_key,
pr.budget_hours,
pr.budget_dollars_cap,
pr.budget_dollars_op
from person_time_data ptd,
task t,
project pr,
customer c,
project_type prt,
person_time pt,
person p
where pt.person_time_key = ptd.person_time_key
and p.person_key = pt.person_key
and t.task_key =* ptd.task_key
and pr.project_key = ptd.project_key
and c.customer_key = pr.customer_key
and prt.project_type_key = ptd.project_type_key
union
select c.customer_key,
c.customer_name,
pr.project_key,
pr.project_code,
eda.task_key,
t.task_name,
t.sequence,
0,
t.budget_hours,
0,
t.budget_dollars_op,
eda.amount * charindex(prt.billable,'Y'),
t.budget_dollars_cap,
ed.expense_date,
eda.expense_data_allocation_key,
pr.budget_hours,
pr.budget_dollars_cap,
pr.budget_dollars_op
from expense_data_allocation eda,
expense_data ed,
task t,
project pr,
customer c,
project_type prt,
expense_type et
where ed.expense_data_key = eda.expense_data_key
and et.expense_type_key = ed.expense_type_key
and prt.project_type_key = ed.project_type_key
and t.task_key =* eda.task_key
and pr.project_key = eda.project_key
and c.customer_key = pr.customer_key
and et.expense_type not in ('ADVANCE','CASH-RETURN')
|