parent nodes: SQLapuntes
Distritos
<<!
DECLARE @HOY AS DATETIME
DECLARE @ID_DISTRITO AS VARCHAR(4)
SET @HOY = GETDATE()
SET @ID_DISTRITO = (SELECT DISTRICTID FROM ANCDISTRICTTABLE DIS WHERE DATAAREAID = 'COL' AND DIS.DISTRICTNAME = 'VALENCIA')
IF LEN(@ID_DISTRITO) > 0
BEGIN
-- MUNICIPIOS DEL DISTRITO
--SELECT * FROM ANCMUNICIPALITYTABLE MUN WHERE MUN.DISTRICTID = @ID_DISTRITO AND MUN.DATAAREAID = 'COL'
/*
-- NOTARÍAS CON SU DIRECCIÓN
SELECT OT.OFFICEID, OT.OFFICENAME, OT.MUNICIPALITYID, OT.SITUATIONDESCRIPTION, LPA.ADDRESS, LPA.COUNTRYREGIONID, LPA.COUNTY, LPA.STATE, LPA.CITY, LPA.STREET, LPA.*
FROM ANCOFFICETABLE OT INNER JOIN LOGISTICSPOSTALADDRESS LPA
ON OT.LOCATION = LPA.LOCATION
WHERE OT.DATAAREAID = 'COL'
/
/
-- NOTARÍAS CON SU DIRECCIÓN Y SÓLO LAS DEL DISTRITO
SELECT OT.OFFICEID, OT.OFFICENAME, OT.MUNICIPALITYID, OT.SITUATIONDESCRIPTION, LPA.ADDRESS, LPA.COUNTRYREGIONID, LPA.COUNTY, LPA.STATE, LPA.CITY, LPA.STREET
FROM ANCOFFICETABLE OT INNER JOIN LOGISTICSPOSTALADDRESS LPA ON OT.LOCATION = LPA.LOCATION
INNER JOIN ANCMUNICIPALITYTABLE MUN ON MUN.COUNTYCODE = LPA.COUNTY AND MUN.MUNICIPALITYID = OT.MUNICIPALITYID AND MUN.DATAAREAID = OT.DATAAREAID
WHERE OT.DATAAREAID = 'COL' AND MUN.DISTRICTID = @ID_DISTRITO
*/
-- NOTARIOS QUE EN LA FECHA ESTÁN EN ESAS NOTARÍAS
WITH NOTARIAS_DISTRITO AS
(SELECT OT.OFFICEID, OT.OFFICENAME, OT.MUNICIPALITYID, OT.SITUATIONDESCRIPTION, LPA.ADDRESS, LPA.COUNTRYREGIONID, LPA.COUNTY, LPA.STATE, LPA.CITY, LPA.STREET
FROM ANCOFFICETABLE OT INNER JOIN LOGISTICSPOSTALADDRESS LPA ON OT.LOCATION = LPA.LOCATION
INNER JOIN ANCMUNICIPALITYTABLE MUN ON MUN.COUNTYCODE = LPA.COUNTY AND MUN.MUNICIPALITYID = OT.MUNICIPALITYID AND MUN.DATAAREAID = OT.DATAAREAID
WHERE OT.DATAAREAID = 'COL' AND MUN.DISTRICTID = @ID_DISTRITO
) SELECT HN.NOTARYID, HN.NOTARYNAME, HN.OFFICEID, HN.OFFICENAME, HN.FROMDATETIME, HN.TODATETIMEFROM ANCNOTARYOFFICEHISTORY HNINNER JOIN NOTARIAS_DISTRITO ON HN.OFFICEID = NOTARIAS_DISTRITO.OFFICEID
WHERE HN.DATAAREAID = 'COL' ANDHN.FROMDATETIME <= @HOY AND
(HN.TODATETIME > @HOY OR HN.TODATETIME = '1900-01-01')
ORDER BY HN.OFFICENAME, HN.NOTARYNAME
END
ELSE
BEGINPRINT 'EL DISTRITO NO SE ENCUENTRA EN LA BASE DE DATOS'
END
>>