RMV_ANTIBIOTICO

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

CODCOLIGADA

DCODCOLIGADA

null

PRONTUARIO

int

√

null

PACIENTE

varchar

√

null

CODCLINICA

int

√

null

CODESPECIALIDADE

varchar

√

null

CRM

varchar

√

null

DATADISPENSACAO

datetime

16,3

null

HORADISPENSACAO

varchar

√

null

NOMEANTIBIOTICO

varchar

√

null

VIA

varchar

√

null

CONCENTRACAO

int

null

QUANTIDADE

numeric

19,4

√

null

DOSETOTAL

int

null

INDICACAO

varchar

√

null

INFECCAO

varchar

√

null

PROFILAXIA

varchar

√

null

SITIO1

varchar

√

null

OUTRASINFORMACOES

int

√

null

NUMPEDIDOANTIBIOTICO

int

null

NUMITEMPEDIDOANTIBIOTICO

varchar

√

null

View Definition

CREATE VIEW RMV_ANTIBIOTICO AS
SELECT A.CODCOLIGADA,
       COALESCE(D.PRONTUARIO,D.CODPACIENTE) AS PRONTUARIO,
       SUBSTRING(D.NOMEPACIENTE,1,50) AS PACIENTE,
       E.IDUNIDATEND AS CODCLINICA,
       C.CODESPECGRUPO AS CODESPECIALIDADE,
       G.IDENTIDADEPROF AS CRM,
       A.DATAREQUISICAO AS DATADISPENSACAO,
       A.HORA AS HORADISPENSACAO,
       SUBSTRING(F.NOMEFANTASIA, 1, 50) AS NOMEANTIBIOTICO,
       CASE
         WHEN F.CODUNDCONTROLE = 'UN' OR F.CODUNDCONTROLE = 'FA' OR
              F.CODUNDCONTROLE = 'AM' OR F.CODUNDCONTROLE = 'FR' THEN
          'P'
         ELSE
          CASE
            WHEN F.CODUNDCONTROLE = 'ML' OR F.CODUNDCONTROLE = 'CO' OR
                 F.CODUNDCONTROLE = 'CA' OR F.CODUNDCONTROLE = 'DR' THEN
             'O'
            ELSE
             CASE
               WHEN F.CODUNDCONTROLE = 'GR' OR F.CODUNDCONTROLE = 'GT' THEN
                'T'
             END
          END
       END AS VIA,
       0 AS CONCENTRACAO,
       CASE
         WHEN A.TIPOMOVIMENTO = 2 THEN
          B.QUANTIDADE * -1
         ELSE
          B.QUANTIDADE
       END AS QUANTIDADE,
       0 AS DOSETOTAL,
       CASE
         WHEN J.TIPO IS NOT NULL THEN
          J.TIPO
         ELSE
          'S'
       END AS INDICACAO,
       J.TIPOINFECCAO AS INFECCAO,
       CASE
         WHEN J.INDICACAOPROFILAXIA = '1' THEN
          'L'
         ELSE
          CASE
            WHEN J.INDICACAOPROFILAXIA = '2' THEN
             'P'
            ELSE
             CASE
               WHEN J.INDICACAOPROFILAXIA = '3' THEN
                'I'
               ELSE
                CASE
                  WHEN J.INDICACAOPROFILAXIA = '4' THEN
                   'C'
                  ELSE
                   CASE
                     WHEN J.INDICACAOPROFILAXIA = '5' THEN
                      'D'
                   END
                END
             END
          END
       END AS PROFILAXIA,
       CASE
         WHEN J.CODSITIO = '99999' THEN
          NULL
         ELSE
          J.CODSITIO
       END AS SITIO1,
       NULL AS OUTRASINFORMACOES,
       B.CODREQUISICAO AS NUMPEDIDOANTIBIOTICO,
       CAST(B.CODREQUISICAO AS VARCHAR) + CAST(B.SEQUENCIAL AS VARCHAR)AS NUMITEMPEDIDOANTIBIOTICO
  FROM DBO.SZMOVIESTOQUE AS A
 INNER JOIN DBO.SZMOVIESTOQUEITEM AS B
    ON A.CODCOLIGADA = B.CODCOLIGADA
   AND A.CODREQUISICAO = B.CODREQUISICAO
 INNER JOIN DBO.SZATENDIMENTO AS C
    ON A.CODCOLIGADA = C.CODCOLIGADA
   AND A.NUMEROCONTA = C.NUMEROCONTA
   AND A.SEQUENCIALCONTA = C.SEQUENCIALCONTA
 INNER JOIN DBO.SZPACIENTE AS D
    ON C.CODCOLIGADA = D.CODCOLIGADA
   AND C.CODPACIENTE = D.CODPACIENTE
 INNER JOIN DBO.SZLEITO AS E
    ON C.CODCOLIGADA = E.CODCOLIGADA
   AND C.CODLEITO = E.CODLEITO
 INNER JOIN DBO.TPRD AS F
    ON B.CODCOLIGADA = F.CODCOLIGADA
   AND B.IDPRD = F.IDPRD
 INNER JOIN DBO.SZCADGERAL AS G
    ON G.CODCOLIGADA = C.CODCOLIGADA
   AND G.CODGERAL = C.CODMEDRESPONSAVEL
 INNER JOIN DBO.SZPRD AS H
    ON H.CODCOLIGADA = F.CODCOLIGADA
   AND H.IDPRD = F.IDPRD
  LEFT OUTER JOIN DBO.SZPRESCRICAO AS P
    ON P.CODCOLIGADA = A.CODCOLIGADA
   AND P.CODREQUISICAO = A.CODREQUISICAO
  LEFT OUTER JOIN DBO.SZJUSTPRESC AS J
    ON J.CODCOLIGADA = P.CODCOLIGADA
   AND J.PRESCRICAOORIGINAL = P.CODPRESCRICAO
 WHERE (A.TIPOMOVIMENTO IN (1, 2, 3, 6))
   AND (A.STATUS = 'P')
   AND (C.TIPOPACIENTE = 'I')
   AND (C.STATUSCONTA IS NULL OR C.STATUSCONTA <> 'C')
   AND (H.ANTIMICROBIANO = 'T')

Columns

View Definition

Possibly Referenced Tables/Views