/*
Estructura de la Base de Datos MONIN
2008-Abr-25
2008-Abr-28

Grupo Delta
Miguel Campos, Viper@upch.edu.pe
Mane Gómez de la Torre, mane@upch.edu.pe
Yuliana Castillo, yulianae.castillo@gmail.com
Jonathan Guzmán, 11618@upch.edu.pe
Yuli Malaver, 13708@upch.edu.pe
Sofía Carrasco, sofiac@upch.edu.pe
Josefina Pérez, fina@upch.edu.pe
*/



-- Generación de Tablas de Usuarios y Administración
CREATE TABLE [dbo].[Roles]( 			-- Tipos de rol del personal
	[CodRol] [char](1) NOT NULL, 		-- código de rol
	[NomRol] [char](25) NOT NULL, 	-- descripción del rol
 	CONSTRAINT [PK_Roles] PRIMARY KEY ( [CodRol] ))
CREATE TABLE [dbo].[LogRecti]( 		-- Registro de Mensajes de Rectificación
	[CodPer] [char](5) NOT NULL, 		-- código del usuario que pide la rectificación
	[NomForm] [char](45) NULL, 		-- nombre del formulario para rectifciar
	[NumPre] [char](5) NULL, 		-- número de la pregunta para rectificar
	[Comenta] [text] NULL, 		-- texto descriptivo de la rectificación
	[Dia] [tinyint] NULL, 			-- fecha de la rectificación
	[Mes] [tinyint] NULL,
	[Ano] [smallint] NULL
 	)
CREATE TABLE [dbo].[Identis]( 		-- Registro de identidades del personal
	[CodIden] [char](5) NOT NULL, 	-- código interno de identificación
	[Nombres] [char](45) NOT NULL, 	-- nombres completos de la persona
	[Ape1] [char](40) NOT NULL, 		-- primer apellido de la persona
	[Ape2] [char](40) NULL, 		-- segundo apellido de la persona
	[Sexo] [char](1) NOT NULL, 		-- sexo (M o F)
	[TDoc] [char](3) NULL, 		-- tipo de documento registrado (ej. DNI)
	[NDoc] [char](12) NOT NULL, 		-- código del documento
	[CodPerCH] [char](5) NULL, 		-- código único de identificación en UPCH
 	CONSTRAINT [PK_Identis] PRIMARY KEY ( [CodIden] ))
CREATE TABLE [dbo].[NTeam]( 			-- Tabla de Equipos de Trabajo
	[Equipo] [tinyint] 
 		IDENTITY(1,1) NOT NULL, 	-- código del equipo
	[NomEquipo] [char](10) NULL, 		-- descripción del equipo
 	CONSTRAINT [PK_NTeam] PRIMARY KEY ( [Equipo] ))
CREATE TABLE [dbo].[Teams]( 			-- Tabla de miembros de los equipos
	[Equipo] [tinyint] NOT NULL, 		-- código del equipo
	[CodIden] [char](5) NOT NULL, 	-- código de la persona
	[CodRol] [char](1) NOT NULL, 		-- código del rol de la persona en el equipo
 	CONSTRAINT [PK_Team] PRIMARY KEY ( [Equipo] ASC,[CodIden] ASC,[CodRol] ))
CREATE TABLE [dbo].[Users]( 			-- Registro de usuarios de sistema (logins)
	[Login] [char](12) NOT NULL, 		-- código del login
	[CodIden] [char](5) NOT NULL, 	-- código de la persona
 	CONSTRAINT [PK_Users] PRIMARY KEY ( [Login] ))
CREATE TABLE [dbo].[UserXRol]( 		-- Tabla de roles de los usuarios
	[IdUsRol] [smallint]
 		IDENTITY(1,1) NOT NULL, 	-- código de la asignación
	[Login] [char](12) NOT NULL, 		-- código del login
	[CodRol] [char](1) NOT NULL, 		-- código del rol
	[FIni] [smalldatetime] NULL, 		-- fecha de inicio de la validez
	[FFin] [smalldatetime] NULL, 		-- fecha de fin de la validez
 	CONSTRAINT [PK_UserXRol] PRIMARY KEY ( [IdUsRol] ))
CREATE TABLE [dbo].[Sched]( 			-- Tabla de asignación de salidas
	[Equipo] [tinyint] NOT NULL, 		-- código del equipo
	[Amb] [char](3) NOT NULL, 		-- código del ámbito
	[Cong] [char](4) NOT NULL, 		-- código del conglomerado
	[NuevoOrd] [smallint] NULL, 		-- código de la salida
 	CONSTRAINT [PK_Sched] PRIMARY KEY ( [Equipo] ASC,[Amb] ASC,[Cong] ))
CREATE TABLE [dbo].[NuevoOrd]( 		-- Tabla de salidas
	[NuevoOrd] [smallint]
 		IDENTITY(1,1) NOT NULL, 	-- código de la salida (semana)
	[FIni] [datetime] NULL, 		-- fecha de inicio de la salida
	[FFin] [datetime] NULL, 		-- fecha de find de la salida
 	CONSTRAINT [PK_NuevoOrd] PRIMARY KEY ( [NuevoOrd] ))
CREATE TABLE [dbo].[UserXCong]( 		-- Tabla intermedia de asignación
	[Login] [char](12) NOT NULL,
	[Amb] [char](3) NOT NULL,
	[Cong] [char](4) NOT NULL,
	[IniEnc] [datetime] NULL,
	[FinEnc] [datetime] NULL,
 	CONSTRAINT [PK_UserXCong] PRIMARY KEY ( [Login] ASC,[Cong] ASC,[Amb] ))


-- Generación de Tablas de Referencia
CREATE TABLE [dbo].[Status]( 			-- Tipos de Status (Resultado de la Visita)
	[idst] [char](1) NOT NULL, 		-- código del tipo de status
	[status] [char](55) NOT NULL, 	-- descripción del status
 	PRIMARY KEY ( [idst] ))
CREATE TABLE [dbo].[Prendas]( 		-- Tipos de Prenda de Vestir
	[CodP] [char](3) NOT NULL, 		-- código de la prenda
	[NomP] [char](255) NOT NULL, 		-- descripción de la prenda
 	CONSTRAINT [PK_Prendas] PRIMARY KEY ( [CodP] ))
CREATE TABLE [dbo].[Beneficio]( 		-- Tipos de beneficio (pregunta P250)
	[CodBene] [char](1) NOT NULL,		-- código del tipo de beneficio
	[NomBene] [char](30) NULL, 		-- descripción del beneficio
 	CONSTRAINT [PK_Beneficio] PRIMARY KEY ( [CodBene] ))
CREATE TABLE [dbo].[Programas]( 		-- Tipos de programa social (pregunta P254)
	[CodProg] [char](1) NOT NULL, 	-- código del programa
	[NomProg] [char](45) NOT NULL, 	-- descripción del programa
 	CONSTRAINT [PK_Programas] PRIMARY KEY ( [CodProg] ))


-- Generación de Tablas de Datos
CREATE TABLE [dbo].[EncFecha]( 		-- Registro de digitación de los formularios
	[Amb] [char](3) NULL,  		-- códigos de ámbito,conglomerado,vivienda,hogar
	[Cong] [char](4) NULL,
	[Viv] [char](4) NULL,
	[Hog] [char](1) NULL,
	[Hoja] [char](12) NULL, 		-- nombre de la hoja (ASP)
	[EncDia] [tinyint] NULL, 		-- fecha de la encuesta
	[EncMes] [tinyint] NULL,
	[EncAno] [smallint] NULL,
	[EncHora] [tinyint] NULL,
	[EncMin] [tinyint] NULL,
	[FReg] [datetime] NULL 		-- fecha y hora de digitación (automático)
 	)

CREATE TABLE [dbo].[ReViVis]( 		-- Registro de visitas a las viviendas
	[Amb] [char](3) NULL,  		-- códigos de ámbito,conglomerado,vivienda,hogar
	[Cong] [char](4) NULL,
	[Viv] [char](4) NULL,
	[Hog] [char](1) NULL,
	[Fecha] [datetime] NULL, 		-- fecha y hora de la visita
	[Manza] [char](5) NULL, 		-- código de la manzana
	[Resulta] [tinyint] NULL 		-- resultado (según Status.idst)
 	)


CREATE TABLE [dbo].[Conglos]( 		-- Tabla de conglomerados
	[CCDD] [char](2) NULL, 		-- código INEI de departamento
	[CCPP] [char](2) NULL, 		-- código INEI de provincia
	[CCDI] [char](2) NULL, 		-- código INEI de distrito
	[CODCCPP] [char](4) NULL, 		-- código INEI de centro poblado
	[CONGLOME] [char](4) NOT NULL,	-- código del conglomerado
	[ZONA] [char](5) NULL, 		-- zona en el plano INEI
	[MANZANAS] [char](255) NULL,  	-- manzanas en el plano INEI
	[AERINI] [char](3) NULL, 		-- AER de inicio en el plano INEI
	[AERFIN] [char](3) NULL, 		-- AER de fin en el plano INEI
	[VIV] [char](4) NULL,  		-- número de viviendas en el marco INEI 2005
	[POB] [char](4) NULL,  		-- número de habitantes en el marco INEI 2005
	[AREA] [char](1) NULL, 		-- clasificación INEI 1 urbano 2 rural
	[ESTRATO] [char](1) NULL,		-- clasificación demográfica INEI (8 niveles)
	[REGION] [char](1) NULL,		-- clasificación geográfica INEI (Co, Si, Se, LM)
	[P1] [float] NULL, 			-- IGNORAR (P1 originalmente recibido de INEI)
	[SECDA] [tinyint] NULL, 		-- IGNORAR (secuencia inicialmente sorteada)
	[AMBITOS] [char](100) NULL,		-- descripción del ámbito (estrato)
	[DEPARTA] [char](100) NULL,		-- descripción del departamento
	[PROVINCIA] [char](200) NULL,		-- descripción de la provincia
	[DISTRITO] [char](200) NULL,		-- descripción del distrito
	[NOMVCCPP] [char](255) NULL,		-- descripción del centro poblado
	[FEXP] [decimal](6, 2) NULL,		-- IGNORAR (factor de expansión 1/P1)
	[POBREC] [float] NULL, 		-- IGNORAR (población expandida)
	[NUEVOORD] [tinyint] NULL,		-- código de la salida
	[AMBITO] [char](3) NOT NULL,		-- código del ámbito (estrato)
	[TotViv] [smallint] NULL,		-- total de viviendas enumeradas en plano
	[Rojas] [smallint] NULL,		-- total de viviendas rojas (eleg.ent.) en plano
	[Azul] [smallint] NULL,		-- total de viviendas azules (eleg. no ent.)
	[OrdRandom] [smallint] NULL,		-- columna del primer número aleatorio
	[NumRandom] [smallint] NULL,		-- valor del primer número aleatorio (vivienda)
	[PlaImp] [char](1) NULL,		-- croquis similar a plano INEI (1 si)
	[FLAT] [decimal](14, 10) NULL,	-- latitud (grados)
	[FLON] [decimal](14, 10) NULL,	-- longitud (grados)
	[FALT] [decimal](14, 10) NULL,	-- altitud (m)
	[NVivsL] [smallint] NULL, 		-- número estimado de viviendas en marco local
	[NVivsLE] [smallint] NULL, 		-- número de viviendas en subunidad visitada
	[NClasV] [smallint] NULL,		-- número de viviendas clasificadas (eleg.)
	[VertOc] [tinyint] NULL, 		-- vertiente (1 Oeste, 2 Este)
 	CONSTRAINT [PK_Conglos] PRIMARY KEY ( [CONGLOME] ASC,[AMBITO] ))


CREATE TABLE [dbo].[Dires000]( 		-- Tabla de direcciones (primera página, S0CA)
	[Amb] [char](3) NOT NULL, 		-- códigos de ámbito,conglomerado,vivienda,hogar
	[Cong] [char](4) NOT NULL,
	[Viv] [char](4) NOT NULL,
	[Hog] [char](1) NOT NULL,
	[TCenPo] [char](2) NULL, 		-- tipo de centro poblado (sección A)
	[Manza] [char](5) NULL, 		-- manzana
	[AER] [char](2) NULL,  		-- área de empadronamiento rural (sección B)
	[JefeApes] [char](45) NULL,		-- apellidos del jefe del hogar
	[JefeNom] [char](45) NULL, 		-- nombres del jefe del hogar
	[TDire] [char](10) NULL,		-- dirección de la vivienda
	[Dire] [char](70) NULL,
	[Km] [decimal](5, 1) NULL,
	[TGru] [char](10) NULL,
	[Gru] [char](10) NULL,
	[TSec] [char](10) NULL,
	[Sec] [char](10) NULL,
	[Mz] [char](5) NULL,
	[Lt] [char](5) NULL,
	[Num] [char](5) NULL,
	[Piso] [char](5) NULL,
	[TInte] [char](12) NULL,
	[Inte] [char](10) NULL,
	[Ref] [char](120) NULL,		-- referencia para la dirección
	[TCom] [char](20) NULL,		-- comunidad
	[Com] [char](100) NULL,
	[ECodIden] [char](5) NULL,		-- código y fecha del encuestador nutricional
	[EncuD] [tinyint] NULL,
	[EncuM] [tinyint] NULL,
	[EncuA] [smallint] NULL,
	[CCodIden] [char](5) NULL, 		-- código y fecha del crítico-codificador
	[CodiD] [tinyint] NULL,
	[CodiM] [tinyint] NULL,
	[CodiA] [smallint] NULL,
	[SCodIden] [char](5) NULL, 		-- código y fecha del supervisor
	[SupD] [tinyint] NULL,
	[SupM] [tinyint] NULL,
	[SupA] [smallint] NULL,
	[DCodIden] [char](5) NULL, 		-- código y fecha del digitador
	[DigiD] [tinyint] NULL,
	[DigiM] [tinyint] NULL,
	[DigiA] [smallint] NULL,
	[BQCodIden] [char](5) NULL, 		-- código y fecha del encuestador bioquímico
	[BQD] [tinyint] NULL,
	[BQM] [tinyint] NULL,
	[BQA] [smallint] NULL,
	[Status] [char](1) NULL,		-- código del resultado
	[Comenta] [text] NULL, 		-- comentarios y notas
 	CONSTRAINT [PK_Dires000] PRIMARY KEY ( [Amb] ASC,[Cong] ASC,[Viv] ASC,[Hog] ))


CREATE TABLE [dbo].[Vivienda100]( 		-- Tabla de hogares (formulario 100)
	[Amb] [char](3) NOT NULL,		-- códigos de ámbito,conglomerado,vivienda,hogar
	[Cong] [char](4) NOT NULL,
	[Viv] [char](4) NOT NULL,
	[Hog] [char](1) NOT NULL,
	[Altitud] [smallint] NULL,		-- altitud m snm
	[Pared] [tinyint] NULL,		-- pregunta P101
	[ParedOtros] [char](45) NULL,
	[Techo] [smallint] NULL,		-- pregunta P102
	[TechoOtros] [char](45) NULL,
	[Piso] [smallint] NULL,		-- pregunta P103
	[PisoOtros] [char](45) NULL,
	[Agua] [smallint] NULL,		-- pregunta P104
	[AguaOtros] [char](45) NULL,
	[Excre] [smallint] NULL,		-- pregunta P105
	[ExcreOtros] [char](45) NULL,
	[LetrinaOK] [tinyint] NULL,		-- pregunta P105A
	[LetrinaI] [tinyint] NULL,		-- pregunta P105B
	[LetrinaINom] [char](40) NULL,
	[LetTubo] [tinyint] NULL,		-- pregunta P105C
	[LetTaza] [tinyint] NULL,
	[LetPiso] [tinyint] NULL,
	[LetPapel] [tinyint] NULL,
	[Alumbra] [smallint] NULL,		-- pregunta P106
	[AlumbraO] [char](45) NULL,
	[NHabita] [smallint] NULL,		-- pregunta P107
	[NDorm] [smallint] NULL,		-- pregunta P108
	[CocinaT] [tinyint] NULL,		-- pregunta P109
	[CocinaTOtro] [char](25) NULL,
	[Chim] [tinyint] NULL, 		-- pregunta P109A
	[CocinaA] [tinyint] NULL,		-- pregunta P109B
	[CocinaAT] [tinyint] NULL,		-- pregunta P109C
	[CocinaANom] [nchar](40) NULL,
	[CocinaUbi] [tinyint] NULL,		-- pregunta P109D
	[CocinaUbiOtro] [char](25) NULL,
	[Elec] [tinyint] NULL, 		-- pregunta P110
	[Radio] [tinyint] NULL,
	[TV] [tinyint] NULL,
	[Telf] [tinyint] NULL,
	[Refri] [tinyint] NULL,
	[Compu] [tinyint] NULL,
	[Bici] [tinyint] NULL, 		-- Pregunta P111
	[Moto] [tinyint] NULL,
	[Carro] [tinyint] NULL,
	[OMedio] [tinyint] NULL,
	[OMedioNom] [char](40) NULL,
	[SalRx] [tinyint] NULL,		-- Pregunta P151
	[SalM] [char](15) NULL,		-- Pregunta P152
	[SalNS] [tinyint] NULL,
	[Fuente] [tinyint] NULL,
	[FuenteOtro] [nchar](25) NULL,	-- fuente del dato de altitud
	[Comenta] [text] NULL,
 	CONSTRAINT [PK_Vivienda100] PRIMARY KEY ( [Amb] ASC,[Cong] ASC,[Viv] ASC,[Hog] ))


CREATE TABLE [dbo].[Hogar200]( 		-- Tabla de habitantes (formulario 200)
	[Amb] [char](3) NOT NULL, 		-- códigos de ámbito,conglomerado,vivienda,hogar
	[Cong] [char](4) NOT NULL,
	[Viv] [char](4) NOT NULL,
	[Hog] [char](1) NOT NULL,
	[IdM] [char](2) NOT NULL,		-- código de miembro del hogar (pregunta P201)
	[Nombre] [char](40) NULL,		-- pregunta P202A
	[Apellido] [char](60) NULL,
	[Paren] [tinyint] NULL,		-- pregunta P202B
	[Sexo] [tinyint] NULL, 		-- pregunta P203
	[Edad] [tinyint] NULL, 		-- pregunta P204
	[EFisMEF] [tinyint] NULL,		-- pregunta P205
	[EGest] [tinyint] NULL,		-- pregunta P206
	[EstCiv] [tinyint] NULL, 		-- pregunta P207
	[EduAnos] [tinyint] NULL, 		-- pregunta P208A
	[EduNiv] [tinyint] NULL, 		-- pregunta P208B
	[EduMat] [tinyint] NULL, 		-- pregunta P208C
	[EduAsiste] [tinyint] NULL, 		-- pregunta P208D
	[ProAlfa] [tinyint] NULL, 		-- pregunta P208E
	[Activ] [tinyint] NULL, 		-- pregunta P209
	[MenorEle] [tinyint] NULL, 		-- pregunta P210A
	[MEFEle] [tinyint] NULL, 		-- pregunta P210B
	[Comenta] [text] NULL, 		-- comentarios y notas
 	CONSTRAINT [PK_Hogar200]
 		PRIMARY KEY ( [Amb] ASC,[Cong] ASC,[Viv] ASC,[Hog] ASC,[IdM] ))

CREATE TABLE [dbo].[ProgAli200A](		-- Tabla de beneficiarios (formulario 200A)
	[Amb] [char](3) NOT NULL,		-- códigos de ámbito,conglomerado,vivienda,hogar
	[Cong] [char](4) NOT NULL,
	[Viv] [char](4) NOT NULL,
	[Hog] [char](1) NOT NULL,
	[IdM] [char](2) NOT NULL, 		-- código de miembro del hogar (pregunta P201)
	[CodProg] [char](1) NOT NULL,		-- código del tipo de programa (P254)
	[CodBene] [char](1) NOT NULL,		-- código del mimebro beneficiario (P251)
	[Donde] [tinyint] NULL, 		-- pregunta P252
	[SabeNom] [tinyint] NULL, 		-- pregunta P253
	[TApoyo] [tinyint] NULL, 		-- pregunta P255
	[ReMes] [tinyint] NULL, 		-- pregunta P256
	[Afil] [tinyint] NULL, 		-- pregunta P257
	[AfilFuente] [tinyint] NULL,
	[AfilCod] [char](15) NULL,
	[Comenta] [text] NULL, 		-- comentarios y notas
 	CONSTRAINT [PK_ProgAli200A] PRIMARY KEY 
 	( [Amb] ASC,[Cong] ASC,[Viv] ASC,[Hog] ASC,[IdM] ASC,[CodProg] ASC,[CodBene] ))


CREATE TABLE [dbo].[Lacta300AB]( 		-- Tabla de niños (formulario 300 secciones A,B)
	[Amb] [char](3) NOT NULL, 		-- códigos de ámbito,conglomerado,vivienda,hogar
	[Cong] [char](4) NOT NULL,
	[Viv] [char](4) NOT NULL,
	[Hog] [char](1) NOT NULL,
	[IdMadre] [char](2) NULL,		-- código de la madre (pregunta P301)
	[IdNino] [char](2) NOT NULL, 		-- código del niño (pregunta P302)
	[DNac] [tinyint] NULL, 		-- fecha de nacimiento (pregunta P303)
	[MNac] [tinyint] NULL,
	[ANac] [smallint] NULL,
	[Ins] [tinyint] NULL,			-- pregunta P304
	[CUI] [char](9) NULL,			-- pregunta P305
	[SIS] [tinyint] NULL,  		-- pregunta P306
	[SISCod] [char](12) NULL, 		-- pregunta P306A
	[SISDia] [tinyint] NULL,
	[SISMes] [tinyint] NULL,
	[SISAno] [smallint] NULL,
	[coprena] [tinyint] NULL, 		-- pregunta P307A
	[dnacio] [tinyint] NULL, 		-- pregunta P307B
	[nomdnacio] [char](45) NULL,
	[ODNacio] [char](45) NULL,
	[Pecho] [tinyint] NULL, 		-- pregunta P307C
	[PechoIni] [tinyint] NULL, 		-- pregunta P308
	[PechoTIni] [tinyint] NULL,
	[TomoOt] [tinyint] NULL, 		-- pregunta P309
	[OLeche] [tinyint] NULL, 		-- pregunta P310
	[OAgua] [tinyint] NULL,
	[ODexG] [tinyint] NULL,
	[OAguaA] [tinyint] NULL,
	[OJugo] [tinyint] NULL,
	[OForm] [tinyint] NULL,
	[OTe] [tinyint] NULL,
	[OMiel] [tinyint] NULL,
	[OOtro] [tinyint] NULL,
	[OOtroNom] [char](45) NULL,
	[SiguePecho] [tinyint] NULL, 		-- pregunta P311
	[MesesPecho] [tinyint] NULL, 		-- pregunta P312
	[DejoPecho] [tinyint] NULL, 		-- pregunta P313
	[ODejoPecho] [char](35) NULL,
	[Charla] [tinyint] NULL, 		-- pregunta P314
	[NomLugar] [char](45) NULL		-- pregunta P315,
	[TipLugar] [tinyint] NULL,
	[OTipLugar] [char](35) NULL,
	[VLactoD] [tinyint] NULL, 		-- pregunta P317
	[VLactoN] [tinyint] NULL, 		-- pregunta P318
	[TAgua] [tinyint] NULL, 		-- pregunta P319
	[TForm] [tinyint] NULL,
	[TLeTa] [tinyint] NULL,
	[TLeVa] [tinyint] NULL,
	[TJugo] [tinyint] NULL,
	[TOtro] [tinyint] NULL,
	[TCaldo] [tinyint] NULL,
	[TNada] [tinyint] NULL,
	[NCome] [tinyint] NULL,
	[CGrano] [tinyint] NULL, 		-- pregunta P320
	[CAma] [tinyint] NULL,
	[CTube] [tinyint] NULL,
	[CVerde] [tinyint] NULL,
	[CFruA] [tinyint] NULL,
	[COVeg] [tinyint] NULL,
	[CCar] [tinyint] NULL,
	[CLeg] [tinyint] NULL,
	[CLac] [tinyint] NULL,
	[CGra] [tinyint] NULL,
	[CPrep] [tinyint] NULL,
	[ANComio] [tinyint] NULL, 		-- pregunta P321
	[VComio] [tinyint] NULL,
	[VitA] [tinyint] NULL, 		-- pregunta P322
	[VFe] [tinyint] NULL,  		-- pregunta P323
	[CAguaje] [tinyint] NULL, 		-- pregunta P324
	[CCol] [tinyint] NULL,
	[CPapaya] [tinyint] NULL,
	[CNaranja] [tinyint] NULL,
	[CMandarina] [tinyint] NULL,
	[CTomate] [tinyint] NULL,
	[CZapallo] [tinyint] NULL,
	[CZanahoria] [tinyint] NULL,
	[CLeche] [tinyint] NULL,
	[CQueso] [tinyint] NULL,
	[CYogur] [tinyint] NULL,
	[CVisceras] [tinyint] NULL,
	[CCarne] [tinyint] NULL,
	[CPescado] [tinyint] NULL,
	[CHuevo] [tinyint] NULL,
	[MasGra] [tinyint] NULL, 		-- pregunta P325
	[Juntos] [tinyint] NULL, 		-- pregunta P327
	[DNIMad] [char](8) NULL, 		-- pregunta P327A
	[JuntosD] [tinyint] NULL,
	[JuntosM] [tinyint] NULL,
	[JuntosA] [smallint] NULL,
	[PapBolsa] [tinyint] NULL, 		-- pregunta P326
	[NQuedaPB] [tinyint] NULL, 		-- pregunta P326A
	[VComioPB] [tinyint] NULL, 		-- pregunta P326B
	[Comenta] [text] NULL, 		-- comentarios y notas
 	CONSTRAINT [PK_Lacta300AB]
 		PRIMARY KEY ( [Amb] ASC,[Cong] ASC,[Viv] ASC,[Hog] ASC,[IdNino] ))

CREATE TABLE [dbo].[Salud300C]( 		-- Tabla de niños (formulario 300 sección C)
	[Amb] [char](3) NOT NULL, 		-- códigos de ámbito,conglomerado,vivienda,hogar
	[Cong] [char](4) NOT NULL,
	[Viv] [char](4) NOT NULL,
	[Hog] [char](1) NOT NULL,
	[IdNino] [char](2) NOT NULL, 		-- código del niño (pregunta P302)
	[San1] [tinyint] NULL, 		-- pregunta P331
	[San2] [tinyint] NULL,
	[San3] [tinyint] NULL,
	[San4] [tinyint] NULL,
	[San5] [tinyint] NULL,
	[San6] [tinyint] NULL,
	[San7] [tinyint] NULL,
	[Tos1] [tinyint] NULL,
	[Tos2] [tinyint] NULL,
	[Tos3] [tinyint] NULL,
	[Tos4] [tinyint] NULL,
	[Tos5] [tinyint] NULL,
	[Tos6] [tinyint] NULL,
	[Tos7] [tinyint] NULL,
	[DR1] [tinyint] NULL,
	[DR2] [tinyint] NULL,
	[DR3] [tinyint] NULL,
	[DR4] [tinyint] NULL,
	[DR5] [tinyint] NULL,
	[DR6] [tinyint] NULL,
	[DR7] [tinyint] NULL,
	[DS1] [smallint] NULL,
	[DS2] [smallint] NULL,
	[DS3] [smallint] NULL,
	[DS4] [smallint] NULL,
	[DS5] [smallint] NULL,
	[DS6] [smallint] NULL,
	[DS7] [smallint] NULL,
	[Fiebre] [tinyint] NULL,		-- pregunta P332
	[Tos] [tinyint] NULL,			-- pregunta P333
	[EnfTos] [tinyint] NULL, 		-- pregunta P334
	[BusTx] [tinyint] NULL, 		-- pregunta P335
	[BusTxNom] [char](40) NULL,
	[Minsa] [tinyint] NULL, 		-- pregunta P336
	[EsSal] [tinyint] NULL,
	[FFAAPP] [tinyint] NULL,
	[CenSal] [tinyint] NULL,
	[PuSal] [tinyint] NULL,
	[PosSal] [tinyint] NULL,
	[ProSal] [tinyint] NULL,
	[Cli] [tinyint] NULL,
	[Cons] [tinyint] NULL,
	[Bot] [tinyint] NULL,
	[Farm] [tinyint] NULL,
	[Fam] [tinyint] NULL,
	[Cura] [tinyint] NULL,
	[Otro] [tinyint] NULL,
	[OtroNom] [char](40) NULL,
	[TNoLocal] [tinyint] NULL, 		-- pregunta P337
	[TLejos] [tinyint] NULL,
	[TNoConfia] [tinyint] NULL,
	[TMaltrato] [tinyint] NULL,
	[TNoMed] [tinyint] NULL,
	[TNoPlata] [tinyint] NULL,
	[TNoNece] [tinyint] NULL,
	[TYaSabe] [tinyint] NULL,
	[TNoTiempo] [tinyint] NULL,
	[TNoPadre] [tinyint] NULL,
	[TNoOtro] [tinyint] NULL,
	[TNoOtroNom] [char](40) NULL,
	[Diarrea] [tinyint] NULL, 		-- pregunta P338
	[Intran] [tinyint] NULL, 		-- pregunta P339
	[ConSed] [tinyint] NULL,
	[LloraSeco] [tinyint] NULL,
	[PielSeca] [tinyint] NULL,
	[DepSan] [tinyint] NULL,
	[NDep] [tinyint] NULL, 		-- pregunta P340
	[CantBeb] [tinyint] NULL, 		-- pregunta P341
	[CantCom] [tinyint] NULL, 		-- pregunta P342
	[SRO] [tinyint] NULL,  		-- pregunta P343
	[Fruti] [tinyint] NULL,
	[SueCas] [tinyint] NULL,
	[AlgoMas] [tinyint] NULL, 		-- pregunta P344
	[Jarabe] [tinyint] NULL, 		-- pregunta P345
	[AntiB] [tinyint] NULL,
	[Suero] [tinyint] NULL,
	[Reme] [tinyint] NULL,
	[MasOtro] [tinyint] NULL,
	[MasOtroNom] [char](40) NULL,
	[DBusTx] [tinyint] NULL, 		-- pregunta P346
	[DBusTxEst] [char](40) NULL, 		-- pregunta P347
	[DMinsa] [tinyint] NULL,
	[DEsSal] [tinyint] NULL,
	[DFFAAPP] [tinyint] NULL,
	[DCenSal] [tinyint] NULL,
	[DPuSal] [tinyint] NULL,
	[DPosSal] [tinyint] NULL,
	[DProSal] [tinyint] NULL,
	[DCli] [tinyint] NULL,
	[DCons] [tinyint] NULL,
	[DBot] [tinyint] NULL,
	[DFarm] [tinyint] NULL,
	[DFam] [tinyint] NULL,
	[DCura] [tinyint] NULL,
	[DOtro] [tinyint] NULL,
	[DOtroNom] [char](40) NULL,
	[DNoLocal] [tinyint] NULL, 		-- pregunta P347A
	[DLejos] [tinyint] NULL,
	[DNoConfia] [tinyint] NULL,
	[DMaltrato] [tinyint] NULL,
	[DNoMed] [tinyint] NULL,
	[DNoPlata] [tinyint] NULL,
	[DNoNece] [tinyint] NULL,
	[DYaSabe] [tinyint] NULL,
	[DNoTiempo] [tinyint] NULL,
	[DNoPadre] [tinyint] NULL,
	[DNoOtro] [tinyint] NULL,
	[DNoOtroNom] [char](40) NULL,
	[Comenta] [text] NULL, 		-- comentarios y notas
 	CONSTRAINT [PK_Salud300C]
 	PRIMARY KEY ( [Amb] ASC,[Cong] ASC,[Viv] ASC,[Hog] ASC,[IdNino] ))

CREATE TABLE [dbo].[Inmuni300](		-- Tabla de niños (formulario 300 sección D)
	[Amb] [char](3) NOT NULL, 		-- códigos de ámbito,conglomerado,vivienda,hogar
	[Cong] [char](4) NOT NULL,
	[Viv] [char](4) NOT NULL,
	[Hog] [char](1) NOT NULL,
	[IdM] [char](2) NOT NULL, 		-- código del niño (pregunta P302)
	[Tarjeta] [tinyint] NULL, 		-- pregunta P351
	[TarjetaTuvo] [tinyint] NULL, 	-- pregunta P352
	[DBCG] [tinyint] NULL, 		-- pregunta P353
	[MBCG] [tinyint] NULL,
	[ABCG] [smallint] NULL,
	[DPolio0] [tinyint] NULL,
	[MPolio0] [tinyint] NULL,
	[APolio0] [smallint] NULL,
	[DPolio1] [tinyint] NULL,
	[MPolio1] [tinyint] NULL,
	[APolio1] [smallint] NULL,
	[DPolio2] [tinyint] NULL,
	[MPolio2] [tinyint] NULL,
	[APolio2] [smallint] NULL,
	[DPolio3] [tinyint] NULL,
	[MPolio3] [tinyint] NULL,
	[APolio3] [smallint] NULL,
	[DDPT1] [tinyint] NULL,
	[MDPT1] [tinyint] NULL,
	[ADPT1] [smallint] NULL,
	[DDPT2] [tinyint] NULL,
	[MDPT2] [tinyint] NULL,
	[ADPT2] [smallint] NULL,
	[DDPT3] [tinyint] NULL,
	[MDPT3] [tinyint] NULL,
	[ADPT3] [smallint] NULL,
	[DHVB1] [tinyint] NULL,
	[MHVB1] [tinyint] NULL,
	[AHVB1] [smallint] NULL,
	[DHVB2] [tinyint] NULL,
	[MHVB2] [tinyint] NULL,
	[AHVB2] [smallint] NULL,
	[DHVB3] [tinyint] NULL,
	[MHVB3] [tinyint] NULL,
	[AHVB3] [smallint] NULL,
	[DHib1] [tinyint] NULL,
	[MHib1] [tinyint] NULL,
	[AHib1] [smallint] NULL,
	[DHib2] [tinyint] NULL,
	[MHib2] [tinyint] NULL,
	[AHib2] [smallint] NULL,
	[DHib3] [tinyint] NULL,
	[MHib3] [tinyint] NULL,
	[AHib3] [smallint] NULL,
	[DPenta1] [tinyint] NULL,
	[MPenta1] [tinyint] NULL,
	[APenta1] [smallint] NULL,
	[DPenta2] [tinyint] NULL,
	[MPenta2] [tinyint] NULL,
	[APenta2] [smallint] NULL,
	[DPenta3] [tinyint] NULL,
	[MPenta3] [tinyint] NULL,
	[APenta3] [smallint] NULL,
	[DTetra] [tinyint] NULL,
	[MTetra] [tinyint] NULL,
	[ATetra] [smallint] NULL,
	[DVitA1] [tinyint] NULL,
	[MVitA1] [tinyint] NULL,
	[AVitA1] [smallint] NULL,
	[DVitA2] [tinyint] NULL,
	[MVitA2] [tinyint] NULL,
	[AVitA2] [smallint] NULL,
	[DVitA3] [tinyint] NULL,
	[MVitA3] [tinyint] NULL,
	[AVitA3] [smallint] NULL,
	[DAmar] [tinyint] NULL,
	[MAmar] [tinyint] NULL,
	[AAmar] [smallint] NULL,
	[DSar] [tinyint] NULL,
	[MSar] [tinyint] NULL,
	[ASar] [smallint] NULL,
	[DSPR] [tinyint] NULL,
	[MSPR] [tinyint] NULL,
	[ASPR] [smallint] NULL,
	[DHexa1] [tinyint] NULL,
	[MHexa1] [tinyint] NULL,
	[AHexa1] [smallint] NULL,
	[DHexa2] [tinyint] NULL,
	[MHexa2] [tinyint] NULL,
	[AHexa2] [smallint] NULL,
	[DHexa3] [tinyint] NULL,
	[MHexa3] [tinyint] NULL,
	[AHexa3] [smallint] NULL,
	[DSR] [tinyint] NULL,
	[MSR] [tinyint] NULL,
	[ASR] [smallint] NULL,
	[BCG] [tinyint] NULL,  		-- pregunta P354
	[Polio] [tinyint] NULL, 		-- pregunta P355
	[Comenta1] [text] NULL,
	[PolioTime] [tinyint] NULL, 		-- pregunta P355A
	[PolioVeces] [tinyint] NULL, 		-- pregunta P355B
	[DPT] [tinyint] NULL,  		-- pregunta P356
	[DPTVeces] [tinyint] NULL, 		-- pregunta P356A
	[HVB] [tinyint] NULL,  		-- pregunta P357
	[HVBTime] [tinyint] NULL, 		-- pregunta P357A
	[HVBVeces] [tinyint] NULL, 		-- pregunta P357B
	[Hib] [tinyint] NULL,  		-- pregunta P358
	[HibTime] [tinyint] NULL,		-- IGNORAR
	[HibVeces] [tinyint] NULL, 		-- pregunta P358A
	[Penta] [tinyint] NULL, 		-- pregunta P359
	[PentaVeces] [tinyint] NULL, 		-- pregunta P359A
	[Tetra] [tinyint] NULL, 		-- pregunta P360
	[VitA] [tinyint] NULL, 		-- pregunta P361
	[Amar] [tinyint] NULL, 		-- pregunta P362
	[Sar] [tinyint] NULL,  		-- pregunta P363
	[SPR] [tinyint] NULL,  		-- pregunta P364
	[Comenta2] [text] NULL,		-- comentarios y notas
 	CONSTRAINT [PK_Inmuni300]
 		PRIMARY KEY ( [Amb] ASC,[Cong] ASC,[Viv] ASC,[Hog] ASC,[IdM] ))

CREATE TABLE [dbo].[Creci300]( 		-- Tabla de niños (formulario 300 sección E)
	[Amb] [char](3) NOT NULL, 		-- códigos de ámbito,conglomerado,vivienda,hogar
	[Cong] [char](4) NOT NULL,
	[Viv] [char](4) NOT NULL,
	[Hog] [char](1) NOT NULL,
	[IdM] [char](2) NOT NULL, 		-- código del niño (pregunta P302)
	[Cont] [tinyint] NULL, 		-- pregunta P365
	[ContQuien] [tinyint] NULL, 		-- pregunta P365A
	[ContQOtro] [char](30) NULL,
	[ContDonde] [tinyint] NULL, 		-- pregunta P365B
	[ContDOtro] [char](30) NULL,
	[ContEst] [char](40) NULL,
	[ContVeces] [tinyint] NULL, 		-- pregunta P365C
	[DNac] [tinyint] NULL, 		-- pregunta P366
	[MNac] [tinyint] NULL,
	[ANac] [int] NULL,
	[PeNac] [decimal](4, 0) NULL,
	[TaNac] [decimal](4, 1) NULL,
	[D1] [tinyint] NULL,
	[M1] [tinyint] NULL,
	[A1] [smallint] NULL,
	[P1] [decimal](4, 2) NULL,
	[T1] [decimal](4, 1) NULL,
	[D2] [tinyint] NULL,
	[M2] [tinyint] NULL,
	[A2] [smallint] NULL,
	[P2] [decimal](4, 2) NULL,
	[T2] [decimal](4, 1) NULL,
	[D3] [tinyint] NULL,
	[M3] [tinyint] NULL,
	[A3] [smallint] NULL,
	[P3] [decimal](4, 2) NULL,
	[T3] [decimal](4, 1) NULL,
	[D4] [tinyint] NULL,
	[M4] [tinyint] NULL,
	[A4] [smallint] NULL,
	[P4] [decimal](4, 2) NULL,
	[T4] [decimal](4, 1) NULL,
	[D5] [tinyint] NULL,
	[M5] [tinyint] NULL,
	[A5] [smallint] NULL,
	[P5] [decimal](4, 2) NULL,
	[T5] [decimal](4, 1) NULL,
	[D6] [tinyint] NULL,
	[M6] [tinyint] NULL,
	[A6] [smallint] NULL,
	[P6] [decimal](4, 2) NULL,
	[T6] [decimal](4, 1) NULL,
	[D7] [tinyint] NULL,
	[M7] [tinyint] NULL,
	[A7] [smallint] NULL,
	[P7] [decimal](4, 2) NULL,
	[T7] [decimal](4, 1) NULL,
	[D8] [tinyint] NULL,
	[M8] [tinyint] NULL,
	[A8] [smallint] NULL,
	[P8] [decimal](4, 2) NULL,
	[T8] [decimal](4, 1) NULL,
	[D9] [tinyint] NULL,
	[M9] [tinyint] NULL,
	[A9] [smallint] NULL,
	[P9] [decimal](4, 2) NULL,
	[T9] [decimal](4, 1) NULL,
	[depali] [tinyint] NULL, 		-- pregunta P367
	[dechn] [tinyint] NULL, 		-- pregunta P368
	[delama] [tinyint] NULL, 		-- pregunta P369
	[alrihi] [tinyint] NULL, 		-- pregunta P370
	[Comenta] [text] NULL, 		-- comentarios y notas
	[sdemos] [tinyint] NULL, 		-- pregunta P380
	[sdemosm] [char](2) NULL, 		-- pregunta P381
	[sdemosq] [tinyint] NULL, 		-- pregunta P382
	[sdemosot] [char](25) NULL,
 	CONSTRAINT [PK_Creci300]
 		PRIMARY KEY ( [Amb] ASC,[Cong] ASC,[Viv] ASC,[Hog] ASC,[IdM] ))


CREATE TABLE [dbo].[Antropo400]( 		-- Tabla de antropometrías (formulario 400)
	[Amb] [char](3) NOT NULL, 		-- códigos de ámbito,conglomerado,vivienda,hogar
	[Cong] [char](4) NOT NULL,
	[Viv] [char](4) NOT NULL,
	[Hog] [char](1) NOT NULL,
	[IdM] [char](2) NOT NULL, 		-- código de la persona (pregunta P401A/P402A)
	[Visita] [char](1) NOT NULL,		-- código de encuestador (repetición)
	[TipoM] [char](3) NULL,		-- tipo (NIN: niño, MEF: mujer)
	[Met] [tinyint] NULL,  		-- método P401B/P402B (1 decúbito, 2 de pié)
	[TallaCm] [decimal](4, 1) NULL,	-- talla cm (P401C/P402C)
	[PesoK] [decimal](4, 2) NULL,		-- peso Kg (P401D/P402D)
	[PesoNacG] [decimal](4, 0) NULL,	-- peso al nacer g (P401E/P402E)
	[Comenta] [text] NULL, 		-- comentarios y notas
 	CONSTRAINT [PK_AntNino401]
 	PRIMARY KEY ( [Amb] ASC,[Cong] ASC,[Viv] ASC,[Hog] ASC,[IdM] ASC,[Visita] ))

CREATE TABLE [dbo].[AntropoP]( 		-- Tabla de prendas usadas (formulario 400)
	[Amb] [char](3) NOT NULL, 		-- códigos de ámbito,conglomerado,vivienda,hogar
	[Cong] [char](4) NOT NULL,
	[Viv] [char](4) NOT NULL,
	[Hog] [char](1) NOT NULL,
	[IdM] [char](2) NOT NULL, 		-- código de la persona (pregunta P401A)
	[Visita] [char](1) NOT NULL, 		-- código de encuestador (repetición)
	[CodP] [char](3) NOT NULL,		-- código de la prenda (P401G)
	[Cant] [tinyint] NULL, 		-- cantidad de prendas (P401F)
 	CONSTRAINT [PK_AntropoP] PRIMARY KEY 
 		( [Amb] ASC,[Cong] ASC,[Viv] ASC,[Hog] ASC,[IdM] ASC,[Visita] ASC,[CodP] ))

CREATE TABLE [dbo].[MEF500](  		-- Tabla de MEF (formulario 500)
	[Amb] [char](3) NOT NULL, 		-- códigos de ámbito,conglomerado,vivienda,hogar
	[Cong] [char](4) NOT NULL,
	[Viv] [char](4) NOT NULL,
	[Hog] [char](1) NOT NULL,
	[IdM] [char](2) NOT NULL, 		-- código de la MEF
	[FeFo] [tinyint] NULL, 		-- pregunta P501
	[VitA] [tinyint] NULL,
	[Multi] [tinyint] NULL,
	[Otros] [tinyint] NULL,
	[Ninguno] [tinyint] NULL,
	[OtrosNom] [char](45) NULL,
	[Comenta] [text] NULL, 		-- comentarios y notas
 	CONSTRAINT [PK_MEF600] PRIMARY KEY ( [Amb] ASC,[Cong] ASC,[Viv] ASC,[Hog] ASC,[IdM] ))

CREATE TABLE [dbo].[Bioqui600]( 		-- Tabla de bioquímica (formulario 600)
	[Amb] [char](3) NOT NULL, 		-- códigos de ámbito,conglomerado,vivienda,hogar
	[Cong] [char](4) NOT NULL,
	[Viv] [char](4) NOT NULL,
	[Hog] [char](1) NOT NULL,
	[IdM] [char](2) NOT NULL, 		-- código de la persona
	[TipoM] [char](3) NULL, 		-- tipo (NIN: niño, MEF: mujer)
	[SelOrina] [tinyint] NULL,		-- pregunta P604/P624
	[SelSuero] [tinyint] NULL,
	[Hb] [decimal](3, 1) NULL,		-- hemoglobina g/dL P605/P625
	[Suero] [decimal](7, 2) NULL, 	-- pregunta P606/P626
	[Orina] [decimal](7, 2) NULL, 	-- pregunta P607/P627
	[Comenta] [text] NULL, 		-- comentarios y notas
 	CONSTRAINT [PK_Bioqui600]
 		PRIMARY KEY ( [Amb] ASC,[Cong] ASC,[Viv] ASC,[Hog] ASC,[IdM] ))

CREATE TABLE [dbo].[Agua700]( 		-- Tabla de agua (formulario 700)
	[Amb] [char](3) NOT NULL, 		-- códigos de ámbito,conglomerado,vivienda,hogar
	[Cong] [char](4) NOT NULL,
	[Viv] [char](4) NOT NULL,
	[Hog] [char](1) NOT NULL,
	[Muestra] [tinyint] NULL,		-- pregunta P701
	[ConTx] [tinyint] NULL,		-- pregunta P702
	[Tx] [tinyint] NULL,			-- pregunta P703
	[TxOtro] [char](12) NULL,
	[Rx] [tinyint] NULL,
	[Comenta] [text] NULL, 		-- comentarios y notas
 	CONSTRAINT [PK_Agua700] PRIMARY KEY ( [Amb] ASC,[Cong] ASC,[Viv] ASC,[Hog] ))

-- Resumen del registro de viviendas para los reportes
CREATE VIEW [dbo].[RViv] AS SELECT D.Amb, D.Cong, D.Viv, 
	MAX(E.EncDia) AS dia, MAX(E.EncMes) AS mes, MAX(E.EncAno) AS ano,
	MAX(E.EncHora) AS hora, MAX(E.EncMin) AS min, 
	MAX(CASE WHEN dbo.Status.status IS NULL THEN '' ELSE STATUS.STATUS END ) AS status,
	MAX(D.Mz) AS mz, MAX(D.AER) AS aer, MAX(C.nhog) AS nhog,
	MAX(CASE WHEN n5 IS NULL THEN 0 ELSE n5 END) AS n5,
	MAX(CASE WHEN nmef IS NULL THEN 0 ELSE nmef END) AS nmef,
	MAX(CASE WHEN n5ele IS NULL THEN 0 ELSE n5ele END) AS n5ele, 
	MAX(CASE WHEN nmefele IS NULL THEN 0 ELSE nmefele END) AS nmefele,
	MAX(CASE WHEN n5selorina IS NULL THEN 0 ELSE n5selorina END) AS n5selorina,
	MAX(CASE WHEN n5selsuero IS NULL THEN 0 ELSE n5selsuero END) AS n5selsuero,
	MAX(CASE WHEN n5hb IS NULL THEN 0 ELSE n5hb END) AS n5hb,
	MAX(CASE WHEN n5suero IS NULL THEN 0 ELSE n5suero END) AS n5suero,
	MAX(CASE WHEN n5orina IS NULL THEN 0 ELSE n5orina END) AS n5orina,
	MAX(CASE WHEN n5hbant IS NULL THEN 0 ELSE n5hbant END) AS n5hbant, 
	MAX(CASE WHEN nmefhbant IS NULL THEN 0 ELSE nmefhbant END) AS nmefhbant,
	MAX(CASE WHEN n5antropo IS NULL THEN 0 ELSE n5antropo END) AS n5antropo,
	MAX(CASE WHEN nmefselorina IS NULL THEN 0 ELSE nmefselorina END) AS nmefselorina,
	MAX(CASE WHEN nmefselsuero IS NULL THEN 0 ELSE nmefselsuero END) AS nmefselsuero,
	MAX(CASE WHEN nmefhb IS NULL THEN 0 ELSE nmefhb END) AS nmefhb, 
	MAX(CASE WHEN nmefsuero IS NULL THEN 0 ELSE nmefsuero END) AS nmefsuero,
	MAX(CASE WHEN nmeforina IS NULL THEN 0 ELSE nmeforina END) AS nmeforina,
	MAX(CASE WHEN nmefantropo IS NULL THEN 0 ELSE nmefantropo END) AS nmefantropo, 
 	MAX(CASE WHEN nmeftodo IS NULL THEN 0 ELSE nmeftodo END) AS nmeftodo,
	MAX(CASE WHEN nmefparte IS NULL THEN 0 ELSE nmefparte END) AS nmefparte,
	MAX(CASE WHEN n5todo IS NULL THEN 0 ELSE n5todo END) AS n5todo
 	FROM dbo.Dires000 AS D 
 	LEFT JOIN dbo.Status ON D.Status = dbo.Status.idst
 	LEFT JOIN dbo.EncFecha AS E
 		ON E.Amb = D.Amb AND E.Cong = D.Cong AND E.Viv = D.Viv AND E.Hog = D.Hog 
 	LEFT OUTER JOIN
 		(SELECT     h.Amb, h.Cong, h.Viv, 
		COUNT(DISTINCT h.Hog) AS nhog,
		SUM(CASE WHEN edad < 5 THEN 1 ELSE 0 END) AS n5, 
		SUM(CASE WHEN edad > 14 AND sexo = 2 THEN 1 ELSE 0 END) AS nmef,
		SUM(CASE WHEN menorele = 1 THEN 1 ELSE 0 END) AS n5ele,
		SUM(CASE WHEN mefele = 1 THEN 1 ELSE 0 END) AS nmefele,
		SUM(CASE WHEN selorina = 1 AND edad < 5 THEN 1 ELSE 0 END) AS N5selorina,
		SUM(CASE WHEN selsuero = 1 AND edad < 5 THEN 1 ELSE 0 END) AS n5selsuero,
		SUM(CASE WHEN hb > 0 AND edad < 5 THEN 1 ELSE 0 END) AS n5hb,
		SUM(CASE WHEN suero > 0 AND edad < 5 THEN 1 ELSE 0 END) AS n5suero, 
		SUM(CASE WHEN orina > 0 AND edad < 5 THEN 1 ELSE 0 END) AS n5orina,
		SUM(CASE WHEN (tallacm>0 AND pesok>0 AND edad<5) THEN 1 ELSE 0 END) 
 			AS N5antropo,
		SUM(CASE WHEN (hb > 0 AND tallacm > 0 AND pesok > 0 AND edad < 5) THEN 1 
 			ELSE 0 END) AS N5HbAnt,
		SUM(CASE WHEN (hb > 0 AND tallacm > 0 AND pesok > 0 AND edad > 14 AND sexo = 2)
 			THEN 1 ELSE 0 END) AS NMEFHbAnt,
		SUM(CASE WHEN selorina = 1 AND edad > 14 AND SEXO = 2 THEN 1 ELSE 0 END)
 			AS NMEFselorina,
		SUM(CASE WHEN selsuero = 1 AND edad > 14 AND SEXO = 2 THEN 1 ELSE 0 END)
 			AS nMEFselsuero,
		SUM(CASE WHEN hb > 0 AND edad > 14 AND SEXO = 2 THEN 1 ELSE 0 END) AS nMEFhb,
		SUM(CASE WHEN suero>0 AND edad>14 AND SEXO=2 THEN 1 ELSE 0 END) AS nMEFsuero,
		SUM(CASE WHEN orina>0 AND edad>14 AND SEXO=2 THEN 1 ELSE 0 END) AS nMEForina,
		SUM(CASE WHEN (tallacm > 0 AND pesok > 0 AND edad > 14 AND SEXO = 2) 
 			THEN 1 ELSE 0 END) AS NMEFantropo, 
		SUM(CASE WHEN (hb > 0 AND suero > 0 AND orina > 0 AND tallacm > 0 AND pesok > 0
 			AND edad > 14 AND SEXO = 2) THEN 1 ELSE 0 END) AS NMEFTodo, 
		SUM(CASE WHEN (hb > 0 AND suero > 0 AND (orina = 0 OR orina IS NULL) 
 			AND tallacm > 0 AND pesok > 0 AND edad > 14 AND SEXO = 2) 
 			THEN 1 ELSE 0 END) AS NMEFParte, 
		SUM(CASE WHEN (hb > 0 AND suero > 0 AND (orina = 0 OR orina IS NULL) 
 			AND tallacm>0 AND pesok>0 AND edad<5) THEN 1 ELSE 0 END) AS N5todo
 	FROM dbo.Hogar200 AS h 
 		LEFT OUTER JOIN dbo.Bioqui600 AS b 
 		ON h.Amb = b.Amb AND h.Cong = b.Cong
 			AND h.Viv = b.Viv AND h.Hog = b.Hog AND h.IdM = b.IdM
		LEFT OUTER JOIN dbo.Antropo400 AS a ON a.Amb = h.Amb AND h.Cong = a.Cong
 			AND h.Viv = a.Viv AND h.Hog = a.Hog 
			AND h.IdM = a.IdM AND '1' = a.Visita
 	GROUP BY h.Amb, h.Cong, h.Viv ) AS C
	 ON C.Amb = D.Amb AND C.Cong = D.Cong AND C.Viv = D.Viv
 	GROUP BY D.Amb, D.Cong, D.Viv


-- Resumen de los miembros del equipo
CREATE view [dbo].[requipo] as
 	Select amb, cong, t.equipo, 
	i.codiden,
	rtrim(nombres)+' '+Rtrim(ape1)+ ' '+rtrim(ape2) as sujeto, 
	nomrol, nuevoord
 	from teams t inner join roles r on r.codrol = t.codrol
 	inner join identis i on i.codiden = t.codiden
 	inner join sched s on s.equipo = t.equipo


-- Definición de relaciones entre tablas (claves foráneas)
ALTER TABLE [dbo].[Agua700]  WITH NOCHECK ADD  CONSTRAINT [FK_Agua700_Vivienda100]
 	FOREIGN KEY([Amb], [Cong], [Viv], [Hog])
 	REFERENCES [dbo].[Vivienda100] ([Amb], [Cong], [Viv], [Hog]) ON UPDATE CASCADE
ALTER TABLE [dbo].[Agua700] CHECK CONSTRAINT [FK_Agua700_Vivienda100]
ALTER TABLE [dbo].[Alimenta300]  WITH NOCHECK ADD  CONSTRAINT [FK_Alimenta300_Hogar200]
 	FOREIGN KEY([Amb], [Cong], [Viv], [Hog], [IdM])
 	REFERENCES [dbo].[Hogar200] ([Amb], [Cong], [Viv], [Hog], [IdM]) ON UPDATE CASCADE
ALTER TABLE [dbo].[Alimenta300] CHECK CONSTRAINT [FK_Alimenta300_Hogar200]
ALTER TABLE [dbo].[Antropo400]  WITH NOCHECK ADD  CONSTRAINT [FK_Antropo400_Hogar200]
 	FOREIGN KEY([Amb], [Cong], [Viv], [Hog], [IdM])
 	REFERENCES [dbo].[Hogar200] ([Amb], [Cong], [Viv], [Hog], [IdM]) ON UPDATE CASCADE
ALTER TABLE [dbo].[Antropo400] CHECK CONSTRAINT [FK_Antropo400_Hogar200]
ALTER TABLE [dbo].[AntropoP]  WITH NOCHECK ADD  CONSTRAINT [FK_AntropoP_Antropo400]
 	FOREIGN KEY([Amb], [Cong], [Viv], [Hog], [IdM], [Visita])
 	REFERENCES [dbo].[Antropo400] ([Amb], [Cong], [Viv], [Hog], [IdM], [Visita])
 	ON UPDATE CASCADE
ALTER TABLE [dbo].[AntropoP] CHECK CONSTRAINT [FK_AntropoP_Antropo400]
ALTER TABLE [dbo].[AntropoP]  WITH NOCHECK ADD  CONSTRAINT [FK_AntropoP_Prendas]
 	FOREIGN KEY([CodP]) REFERENCES [dbo].[Prendas] ([CodP]) ON UPDATE CASCADE
ALTER TABLE [dbo].[AntropoP] CHECK CONSTRAINT [FK_AntropoP_Prendas]
ALTER TABLE [dbo].[Bioqui600]  WITH NOCHECK ADD  CONSTRAINT [FK_Bioqui600_Hogar200]
 	FOREIGN KEY([Amb], [Cong], [Viv], [Hog], [IdM])
 	REFERENCES [dbo].[Hogar200] ([Amb], [Cong], [Viv], [Hog], [IdM]) ON UPDATE CASCADE
ALTER TABLE [dbo].[Bioqui600] CHECK CONSTRAINT [FK_Bioqui600_Hogar200]
ALTER TABLE [dbo].[Creci300]  WITH NOCHECK ADD  CONSTRAINT [FK_Creci300_Hogar200]
 	FOREIGN KEY([Amb], [Cong], [Viv], [Hog], [IdM])
 	REFERENCES [dbo].[Hogar200] ([Amb], [Cong], [Viv], [Hog], [IdM]) ON UPDATE CASCADE
ALTER TABLE [dbo].[Creci300] CHECK CONSTRAINT [FK_Creci300_Hogar200]
ALTER TABLE [dbo].[Hogar200]  WITH NOCHECK ADD  CONSTRAINT [FK_Hogar200_Vivienda100]
 	FOREIGN KEY([Amb], [Cong], [Viv], [Hog])
 	REFERENCES [dbo].[Vivienda100] ([Amb], [Cong], [Viv], [Hog]) ON UPDATE CASCADE
ALTER TABLE [dbo].[Hogar200] CHECK CONSTRAINT [FK_Hogar200_Vivienda100]
ALTER TABLE [dbo].[Inmuni300]  WITH NOCHECK ADD  CONSTRAINT [FK_Inmuni300_Hogar200]
 	FOREIGN KEY([Amb], [Cong], [Viv], [Hog], [IdM])
 	REFERENCES [dbo].[Hogar200] ([Amb], [Cong], [Viv], [Hog], [IdM]) ON UPDATE CASCADE
ALTER TABLE [dbo].[Inmuni300] CHECK CONSTRAINT [FK_Inmuni300_Hogar200]
ALTER TABLE [dbo].[Lacta300AB]  WITH NOCHECK ADD  CONSTRAINT [FK_Lacta300AB_Hogar200]
 	FOREIGN KEY([Amb], [Cong], [Viv], [Hog], [IdNino])
 	REFERENCES [dbo].[Hogar200] ([Amb], [Cong], [Viv], [Hog], [IdM]) ON UPDATE CASCADE
ALTER TABLE [dbo].[Lacta300AB] CHECK CONSTRAINT [FK_Lacta300AB_Hogar200]
ALTER TABLE [dbo].[MEF500]  WITH NOCHECK ADD  CONSTRAINT [FK_MEF500_Hogar200]
 	FOREIGN KEY([Amb], [Cong], [Viv], [Hog], [IdM])
 	REFERENCES [dbo].[Hogar200] ([Amb], [Cong], [Viv], [Hog], [IdM]) ON UPDATE CASCADE
ALTER TABLE [dbo].[MEF500] CHECK CONSTRAINT [FK_MEF500_Hogar200]
ALTER TABLE [dbo].[ProgAli200A]  WITH NOCHECK ADD  CONSTRAINT [FK_ProgAli200A_Beneficio]
 	FOREIGN KEY([CodBene]) REFERENCES [dbo].[Beneficio] ([CodBene]) ON UPDATE CASCADE
ALTER TABLE [dbo].[ProgAli200A] CHECK CONSTRAINT [FK_ProgAli200A_Beneficio]
ALTER TABLE [dbo].[ProgAli200A]  WITH NOCHECK ADD  CONSTRAINT [FK_ProgAli200A_Hogar200]
 	FOREIGN KEY([Amb], [Cong], [Viv], [Hog], [IdM])
 	REFERENCES [dbo].[Hogar200] ([Amb], [Cong], [Viv], [Hog], [IdM])
 	ON UPDATE CASCADE
ALTER TABLE [dbo].[ProgAli200A] CHECK CONSTRAINT [FK_ProgAli200A_Hogar200]
ALTER TABLE [dbo].[ProgAli200A]  WITH NOCHECK ADD  CONSTRAINT [FK_ProgAli200A_Programas]
 	FOREIGN KEY([CodProg]) REFERENCES [dbo].[Programas] ([CodProg]) ON UPDATE CASCADE
ALTER TABLE [dbo].[ProgAli200A] CHECK CONSTRAINT [FK_ProgAli200A_Programas]
ALTER TABLE [dbo].[Progras]  WITH NOCHECK ADD  CONSTRAINT [FK_Progras_Vivienda100]
 	FOREIGN KEY([Amb], [Cong], [Viv], [Hog])
 	REFERENCES [dbo].[Vivienda100] ([Amb], [Cong], [Viv], [Hog]) ON UPDATE CASCADE
ALTER TABLE [dbo].[Progras] CHECK CONSTRAINT [FK_Progras_Vivienda100]
ALTER TABLE [dbo].[Salud300C]  WITH NOCHECK ADD  CONSTRAINT [FK_Salud300C_Hogar200]
 	FOREIGN KEY([Amb], [Cong], [Viv], [Hog], [IdNino])
 	REFERENCES [dbo].[Hogar200] ([Amb], [Cong], [Viv], [Hog], [IdM])
 	ON UPDATE CASCADE
ALTER TABLE [dbo].[Salud300C] CHECK CONSTRAINT [FK_Salud300C_Hogar200]
ALTER TABLE [dbo].[Sched]  WITH CHECK ADD  CONSTRAINT [FK_Sched_Conglos]
 	FOREIGN KEY([Cong], [Amb])
 	REFERENCES [dbo].[Conglos] ([CONGLOME], [AMBITO]) ON UPDATE CASCADE
ALTER TABLE [dbo].[Sched] CHECK CONSTRAINT [FK_Sched_Conglos]
ALTER TABLE [dbo].[Sched]  WITH CHECK ADD  CONSTRAINT [FK_Sched_NTeam]
 	FOREIGN KEY([Equipo]) REFERENCES [dbo].[NTeam] ([Equipo]) ON UPDATE CASCADE
ALTER TABLE [dbo].[Sched] CHECK CONSTRAINT [FK_Sched_NTeam]
ALTER TABLE [dbo].[Sched]  WITH CHECK ADD  CONSTRAINT [FK_Sched_NuevoOrd]
 	FOREIGN KEY([NuevoOrd]) REFERENCES [dbo].[NuevoOrd] ([NuevoOrd]) ON UPDATE CASCADE
ALTER TABLE [dbo].[Sched] CHECK CONSTRAINT [FK_Sched_NuevoOrd]
ALTER TABLE [dbo].[Teams]  WITH CHECK ADD  CONSTRAINT [FK_Teams_Identis]
 	FOREIGN KEY([CodIden]) REFERENCES [dbo].[Identis] ([CodIden]) ON UPDATE CASCADE
ALTER TABLE [dbo].[Teams] CHECK CONSTRAINT [FK_Teams_Identis]
ALTER TABLE [dbo].[Teams]  WITH CHECK ADD  CONSTRAINT [FK_Teams_NTeam]
 	FOREIGN KEY([Equipo]) REFERENCES [dbo].[NTeam] ([Equipo]) ON UPDATE CASCADE
ALTER TABLE [dbo].[Teams] CHECK CONSTRAINT [FK_Teams_NTeam]
ALTER TABLE [dbo].[Teams]  WITH CHECK ADD  CONSTRAINT [FK_Teams_Roles]
 	FOREIGN KEY ([CodRol]) REFERENCES [dbo].[Roles] ([CodRol]) ON UPDATE CASCADE
ALTER TABLE [dbo].[Teams] CHECK CONSTRAINT [FK_Teams_Roles]
ALTER TABLE [dbo].[Users]  WITH CHECK ADD  CONSTRAINT [FK_Users_Identis]
 	FOREIGN KEY([CodIden]) REFERENCES [dbo].[Identis] ([CodIden]) ON UPDATE CASCADE
ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_Users_Identis]
ALTER TABLE [dbo].[UserXCong]  WITH NOCHECK ADD  CONSTRAINT [FK_UserXCong_Conglos]
 	FOREIGN KEY([Cong], [Amb]) REFERENCES [dbo].[Conglos] ([CONGLOME], [AMBITO])
 	ON UPDATE CASCADE
ALTER TABLE [dbo].[UserXCong] CHECK CONSTRAINT [FK_UserXCong_Conglos]
ALTER TABLE [dbo].[UserXCong]  WITH NOCHECK ADD  CONSTRAINT [FK_UserXCong_Users]
 	FOREIGN KEY([Login]) REFERENCES [dbo].[Users] ([Login]) ON UPDATE CASCADE
ALTER TABLE [dbo].[UserXCong] CHECK CONSTRAINT [FK_UserXCong_Users]
ALTER TABLE [dbo].[UserXRol]  WITH CHECK ADD  CONSTRAINT [FK_UserXRol_Roles]
 	FOREIGN KEY([CodRol]) REFERENCES [dbo].[Roles] ([CodRol]) ON UPDATE CASCADE
ALTER TABLE [dbo].[UserXRol] CHECK CONSTRAINT [FK_UserXRol_Roles]
ALTER TABLE [dbo].[UserXRol]  WITH NOCHECK ADD  CONSTRAINT [FK_UserXRol_Users]
 	FOREIGN KEY([Login]) REFERENCES [dbo].[Users] ([Login]) ON UPDATE CASCADE
ALTER TABLE [dbo].[UserXRol] CHECK CONSTRAINT [FK_UserXRol_Users]
ALTER TABLE [dbo].[Vivienda100]  WITH NOCHECK ADD  CONSTRAINT [FK_Vivienda100_Dires000]
 	FOREIGN KEY([Amb], [Cong], [Viv], [Hog])
 	REFERENCES [dbo].[Dires000] ([Amb], [Cong], [Viv], [Hog]) ON UPDATE CASCADE
ALTER TABLE [dbo].[Vivienda100] CHECK CONSTRAINT [FK_Vivienda100_Dires000]
GO

-- actualización de UserXCong (ejecutar cada vez que cambien Teams, Users, Sched o NuevoOrd
DELETE FROM UserXCong
INSERT INTO UserXCong
 	SELECT DISTINCT Users.LogIn, Sched.Amb, Sched.Cong,
 	NuevoOrd.FIni AS IniEnc, NuevoOrd.FFin AS FinEnc
	FROM ((Teams LEFT JOIN Users ON Teams.CodIden = Users.CodIden)
	JOIN Sched ON Teams.Equipo = Sched.Equipo)
	LEFT JOIN NuevoOrd ON Sched.NuevoOrd = NuevoOrd.NuevoOrd
	WHERE Users.LogIn IS NOT NULL

-- en esta versión no se han incorporado reglas de integridad adicionales (CHECK)
-- la validación ha sido controlada por la aplicación

-- Las tablas Alimenta300 Muestra700, Trans800, LogBiohaz, ColRango, ColVal, y Progras 
-- no se han usado.

