vw_units_3g

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

serialnumber

varchar

√

null

prefix

text

2147483647

√

null

model

varchar

√

null

productonom

text

2147483647

√

null

subproduct

varchar

2147483647

√

null

client

varchar

256

√

null

clientcode

varchar

√

null

phonenumber

varchar

2147483647

√

null

lastgpsdate

timestamptz

35,6

√

null

offline_days

numeric

√

null

reporting

text

2147483647

√

null

comm

text

2147483647

√

null

billing_status

varchar

√

null

inet

2147483647

√

null

carrier

varchar

2147483647

√

null

country

text

2147483647

√

null

cotizationes

text

2147483647

√

null

Relationships

Close relationships within degrees of separation

View Definition

SELECT n.serialnumber,
    "substring"((n.serialnumber)::text, '\w{4}'::text) AS prefix,
    dm.name AS model,
    regexp_replace((n.productonom)::text, 'V2'::text, ''::text) AS productonom,
        CASE
            WHEN ((n.productonom)::text ~~ '%V2'::text) THEN n.productonom
            ELSE NULL::character varying
        END AS subproduct,
    c.name AS client,
    c.clientcode,
    COALESCE(n.phonenumber, d.unit_name) AS phonenumber,
    d.lastgpsdate,
    (date_part('epoch'::text, ((now() - d.lastgpsdate) / ((3600 * 24))::double precision)))::numeric(10,0) AS offline_days,
        CASE
            WHEN ((date_part('epoch'::text, ((now() - d.lastgpsdate) / ((3600 * 24))::double precision)))::numeric(10,0) > 90.0) THEN 'NOT_REPORTING'::text
            ELSE 'REPORTING'::text
        END AS reporting,
        CASE
            WHEN ((n.serialnumber)::text ~~ '1633%'::text) THEN '2g'::text
            WHEN ((n.serialnumber)::text ~~ '4831%'::text) THEN '2g'::text
            WHEN ((n.serialnumber)::text ~~ '4832%'::text) THEN '2g'::text
            WHEN ((n.serialnumber)::text ~~ '4631%'::text) THEN '2g'::text
            WHEN ((n.serialnumber)::text ~~ '4632%'::text) THEN '2g'::text
            WHEN (ltrim((n.serialnumber)::text, '0'::text) ~~ '3%'::text) THEN '2g'::text
            ELSE '3g'::text
        END AS comm,
    n.statusfacturacion AS billing_status,
    d.ip,
    COALESCE(pl.carrier, (
        CASE
            WHEN ((d.ip)::text ~~ '10.113%'::text) THEN 'AT&T'::text
            WHEN ((d.unit_name)::text = '1111111111'::text) THEN 'AT&T'::text
            ELSE 'TELCEL'::text
        END)::character varying) AS carrier,
        CASE
            WHEN ((c.clientcode)::text ~~ 'mx%'::text) THEN 'MX'::text
            WHEN ((c.clientcode)::text ~~ 'co%'::text) THEN 'CO'::text
            WHEN ((c.clientcode)::text = ANY (ARRAY[('66367'::character varying)::text, ('OmniFS2'::character varying)::text])) THEN 'MX'::text
            ELSE 'MX'::text
        END AS country,
        CASE
            WHEN (((c.name)::text ~~ 'COTIZACIONES%'::text) OR ((c.name)::text ~~ '1 CNR%'::text)) THEN 'withoutCustomer'::text
            ELSE 'withCustomer'::text
        END AS cotizationes
   FROM (((((staging.vw_qcsntoday_cc n
     JOIN device d ON ((ltrim((d.serialnumber)::text, '0'::text) = (n.serialnumber)::text)))
     JOIN devicemodel dm ON ((dm.idmodeldevice = d.idmodeldevice)))
     JOIN technicalaccount ta ON ((ta.idtechnicalaccount = d.idtechnicalaccount)))
     JOIN client c ON ((c.idclient = ta.idclient)))
     LEFT JOIN staging.phonelines pl ON (((pl.serialnumber)::text = ltrim((d.serialnumber)::text, '0'::text))))
  WHERE ((n.productonom)::text <> 'AFC'::text);

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views