view_time_sheets_by_day

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

invoice_code

varchar

√

null

cost_centre

varchar

√

null

tbl_cost_centre.cost_centre

Implied ConstraintR

task_code

varchar

√

null

staff_refid

int4

√

null

date

√

null

year

float8

17,17

√

null

week

float8

17,17

√

null

hours

numeric

131089

√

null

numeric

131089

√

null

fname

varchar

√

null

lname

varchar

√

null

project_manager

text

2147483647

√

null

charge_cost

numeric

131089

√

null

staff_position

varchar

100

√

null

tbl_positions.position

Implied ConstraintR

Relationships

Close relationships within degrees of separation

View Definition

SELECT
        CASE
            WHEN (a.invoice_code IS NOT NULL) THEN a.invoice_code
            ELSE b.invoice_code
        END AS invoice_code,
        CASE
            WHEN (a.cost_centre IS NOT NULL) THEN a.cost_centre
            ELSE b.cost_centre
        END AS cost_centre,
        CASE
            WHEN (a.task_code IS NOT NULL) THEN a.task_code
            ELSE b.task_code
        END AS task_code,
        CASE
            WHEN (a.staff_refid IS NOT NULL) THEN a.staff_refid
            ELSE b.staff_refid
        END AS staff_refid,
        CASE
            WHEN (a.date_worked IS NOT NULL) THEN a.date_worked
            ELSE b.date_worked
        END AS date,
    date_part('year'::text,
        CASE
            WHEN (a.date_worked IS NOT NULL) THEN a.date_worked
            ELSE b.date_worked
        END) AS year,
    date_part('week'::text,
        CASE
            WHEN (a.date_worked IS NOT NULL) THEN a.date_worked
            ELSE b.date_worked
        END) AS week,
        CASE
            WHEN (a.hours IS NULL) THEN (0)::numeric
            ELSE a.hours
        END AS hours,
        CASE
            WHEN (b.ot IS NULL) THEN (0)::numeric
            ELSE b.ot
        END AS ot,
    s.fname,
    s.lname,
    (((s2.lname)::text || ' '::text) || (s2.fname)::text) AS project_manager,
        CASE
            WHEN (a.charge_cost IS NOT NULL) THEN a.charge_cost
            ELSE b.charge_cost
        END AS charge_cost,
        CASE
            WHEN (a.staff_position IS NOT NULL) THEN a.staff_position
            ELSE b.staff_position
        END AS staff_position
   FROM ((((view_time_sheets_by_day_part_one a
     FULL JOIN view_time_sheets_by_day_part_two b ON ((((a.invoice_code)::text = (b.invoice_code)::text) AND ((a.task_code)::text = (b.task_code)::text) AND (a.staff_refid = b.staff_refid) AND (a.date_worked = b.date_worked))))
     LEFT JOIN tbl_staff_lookup s ON (((a.staff_refid = s.refid) OR (b.staff_refid = s.refid))))
     LEFT JOIN tbl_finance_project fp ON ((((a.invoice_code)::text = (fp.invoice_code)::text) OR ((b.invoice_code)::text = (fp.invoice_code)::text))))
     LEFT JOIN tbl_staff_lookup s2 ON ((fp.manager = s2.refid)))
  ORDER BY (date_part('year'::text,
        CASE
            WHEN (a.date_worked IS NOT NULL) THEN a.date_worked
            ELSE b.date_worked
        END)), (date_part('week'::text,
        CASE
            WHEN (a.date_worked IS NOT NULL) THEN a.date_worked
            ELSE b.date_worked
        END)), a.invoice_code, a.task_code, a.staff_refid DESC;

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views