vw_msgnoack

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

serialnumber

varchar

64

√

null

device_rnt.serialnumber

Implied Constraint

R

messagestatus

int2

5

√

null

devmessageid

int4

10

√

null

senddate

timestamptz

35,6

√

null

lastgpsdate

timestamptz

35,6

√

null

Relationships

Close relationships within degrees of separation

View Definition

WITH q AS (
         SELECT d.serialnumber,
            m.device_aknowledge AS mensaje,
            m.devmessageid,
            m.messagestatus,
            d.lastgpsdate,
            m.senddate
           FROM (message m
             LEFT JOIN device d ON ((d.iddevice = m.iddevice)))
          WHERE ((d.idtechnicalaccount = 1111) AND (m.senddate > '2015-12-01 00:00:00-06'::timestamp with time zone) AND (m.idmayauser IS NOT NULL) AND (m.messagetype = 1) AND (m.messagestatus < 2) AND (d.lastgpsdate > m.senddate))
          ORDER BY d.serialnumber
        ), q1 AS (
         SELECT q.serialnumber,
            q.mensaje,
            q.devmessageid,
            q.messagestatus,
            q.lastgpsdate,
            q.senddate,
            ((date_part('epoch'::text, (q.lastgpsdate - q.senddate)) / (60)::double precision))::integer AS timelag
           FROM q
        )
 SELECT q1.serialnumber,
    q1.messagestatus,
    q1.devmessageid,
    q1.senddate,
    q1.lastgpsdate
   FROM q1
  ORDER BY q1.timelag DESC;

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views