Database Tables
This page contains information on the database tables in assessingsearch on VSQL01. (NOTE: assessingupdates is a clone of assessingsearch for dev)
Views
This view does not appear to be used by default.asp or any other procedures.
This view could be deleted from the DB.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[value_tax]
as
SELECT TOP 1 dbo.taxbill.*, dbo.Res_exempt.personal_exemption, dbo.Landuse_Described.Description, dbo.propertycodes_described.*,
dbo.Res_exempt.residential_exemption, dbo.parcel.condo_main condo__main, tax_preliminary.[Bill Year], tax_preliminary.[Bill Number],
tax_preliminary.[RE Tax Amt], tax_preliminary.[CPA Amt], tax_preliminary.[Downtown BID Amt], tax_preliminary.[Greenway BID Amt], tax_preliminary.[Total Billed Amt]
FROM dbo.taxbill
LEFT OUTER JOIN dbo.parcel ON dbo.taxbill.parcel_id = dbo.parcel.parcel_id
LEFT OUTER JOIN dbo.Res_exempt ON dbo.taxbill.parcel_id = dbo.Res_exempt.parcel_id
LEFT OUTER JOIN dbo.propertycodes_described ON dbo.taxbill.property_type = dbo.propertycodes_described.[property-code]
LEFT OUTER JOIN dbo.Landuse_Described ON dbo.taxbill.land_use = dbo.Landuse_Described.Short_Description
LEFT OUTER JOIN dbo.tax_preliminary ON dbo.taxbill.parcel_id = dbo.tax_preliminary.parcel_id
GOStored Procedures
Active
This stored procedure is run to complete the processing of temporary import tables into the main data tables, after the MSAccess tables have been extracted see Updating Database Tables below.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: David U
-- Create date: 2024-06-25
-- Description: Process MSAccess updates into DB
-- Assumes the current DB is a clone of the previous Quarters DB.
-- Check the dates in table names and field names before running each quarter.
-- =============================================
CREATE PROCEDURE [dbo].[sp_202YQX_update_01]
AS
BEGIN
DECLARE @fiscal_year SMALLINT;
DECLARE @quarter INT;
SET @quarter = 1; -- Controls data merge logic by Quarter. Set to 1 in June, and set to 3 in Dec.
-- -------------------------------------------
-- [additional_data] Bring in additional data updates
-- -------------------------------------------
MERGE additional_data AS base
USING _ADDITIONAL_DATA AS import ON base.parcel_id = import.parcel_id
WHEN MATCHED THEN
UPDATE SET
base.living_area = CONVERT(int, CONVERT(numeric, import.[Living Area])), -- shown in Living Area
base.gross_area = CONVERT(int, CONVERT(numeric, import.[Gross Area])), -- shown in Gross Area
base.year_built = CONVERT(smallint, import.[Year Built]), -- shown in Year Built
base.year_remodeled = CONVERT(smallint, CONVERT(numeric, import.[Year Remodel])),
base.condo_units_commercial = CONVERT(int, CONVERT(numeric, import.[Commercial Units])),
base.stories = CONVERT(numeric(4,1), import.[Story Height])
WHEN NOT MATCHED THEN
INSERT (parcel_id, living_area, gross_area, year_built,
year_remodeled, condo_units_commercial, stories)
VALUES (import.parcel_id, CONVERT(int, CONVERT(numeric, import.[Living Area])), CONVERT(int, CONVERT(numeric, import.[Gross Area])), CONVERT(smallint, import.[Year Built]),
CONVERT(smallint, CONVERT(numeric, import.[Year Remodel])), CONVERT(int, CONVERT(numeric, import.[Commercial Units])), CONVERT(numeric(4,1), import.[Story Height]));
-- -------------------------------------------
-- [condo_attributes] Bring in condo attributes data updates
-- -------------------------------------------
MERGE dbo.condo_attributes AS base -- table data shown in ATTRIBUTES when a condo (condo_main is not null)
USING dbo._CONDO_PROPERTY_ATTRIBUTES AS import ON base.parcel_id = import.parcel_id
WHEN MATCHED AND LEN(import.parcel_id) = 10 THEN
UPDATE SET
base.[Composite Land Use]=import.[Composite Land Use], -- shown in Attributes
base.[Orientation]=import.[Orientation], -- shown in Attributes
base.[Corner Unit]=import.[Corner Unit], -- shown in Attributes
base.[Floor]=import.[Floor], -- shown in Attributes
base.[Rooms]=import.[Rooms], -- shown in Attributes
base.[Bedrooms]=import.[Bedrooms], -- shown in Attributes
base.[Bedroom Type]=import.[Bedroom Type], -- shown in Attributes
base.[Full Bath]=CONVERT(numeric, import.[Full Bath]), -- shown in Attributes
base.[Half Bath]=CONVERT(numeric, import.[Half Bath]), -- shown in Attributes
base.[Other Fixtures]=CONVERT(numeric, import.[Other Fixtures]), -- shown in Attributes
base.[Bath Style 1]=import.[Bath Style 1], -- shown in Attributes
base.[Bath Style 2]=import.[Bath Style 2], -- shown in Attributes
base.[Bath Style 3]=import.[Bath Style 3], -- shown in Attributes
base.[Kitchens]=CONVERT(numeric, import.[Kitchens]), -- shown in Attributes
base.[Kitchen Type]=import.[Kitchen Type], -- shown in Attributes
base.[Kitchen Style 1]=import.[Kitchen Style 1], -- shown in Attributes
base.[Kitchen Style 2]=import.[Kitchen Style 2], -- shown in Attributes
base.[Kitchen Style 3]=import.[Kitchen Style 3], -- shown in Attributes
base.[Fireplaces]=import.[Fireplaces], -- shown in Attributes
base.[Penthouse Unit]=CONVERT(bit, import.[Penthouse Unit]), -- shown in Attributes
base.[AC Type]=import.[AC Type], -- shown in Attributes
base.[Heat Type]=import.[Heat Type], -- shown in Attributes
base.[Year Built]=import.[Year Built], -- shown in Attributes
base.[Interior Condition]=import.[Interior Condition], -- shown in Attributes
base.[Interior Finish]=import.[Interior Finish], -- shown in Attributes
base.[View]=import.[View], -- shown in Attributes
base.[Grade]=import.[Grade], -- shown in Attributes
base.[# of Parking Spots]=import.[# of Parking Spots], -- shown in Attributesv
base.[Parking Ownership]=import.[Parking Ownership], -- shown in Attributes
base.[Parking Type]=import.[Parking Type], -- shown in Attributes
base.[Tandem Parking]=import.[Tandem Parking], -- shown in Attributes
base.[Complex]=import.[Complex], -- shown in Attributes
base.[Story Height]=import.[Story Height], -- shown in Attributes
base.[Roof Cover]=import.[Roof Cover], -- shown in Attributes
base.[Roof Structure]=import.[Roof Structure], -- shown in Attributes
base.[Exterior Condition]=import.[Exterior Condition], -- shown in Attributes
base.[Exterior Finish]=import.[Exterior Finish], -- shown in Attributes
base.[Building Style]=import.[Building Style], -- shown in Attributes
base.[Foundation]=import.[Foundation] -- shown in Attributes
WHEN NOT MATCHED AND LEN(import.parcel_id) = 10 THEN
INSERT ([parcel_id],[Composite Land Use],[Orientation],[Corner Unit],[Floor],[Rooms],[Bedrooms],[Bedroom Type]
,[Full Bath],[Half Bath],[Other Fixtures]
,[Bath Style 1],[Bath Style 2],[Bath Style 3],[Kitchens],[Kitchen Type],[Kitchen Style 1],[Kitchen Style 2]
,[Kitchen Style 3],[Fireplaces],[Penthouse Unit],[AC Type],[Heat Type],[Year Built],[Interior Condition],[Interior Finish]
,[View],[Grade],[# of Parking Spots],[Parking Ownership],[Parking Type],[Tandem Parking],[Complex],[Story Height],[Roof Cover]
,[Roof Structure],[Exterior Condition],[Exterior Finish],[Building Style],[Foundation])
VALUES (CONVERT(nvarchar(10), import.[parcel_id]), import.[Composite Land Use],import.[Orientation],import.[Corner Unit],import.[Floor],import.[Rooms],import.[Bedrooms],import.[Bedroom Type]
,CONVERT(numeric, import.[Full Bath]),CONVERT(numeric, import.[Half Bath]),CONVERT(numeric, import.[Other Fixtures])
,import.[Bath Style 1],import.[Bath Style 2],import.[Bath Style 3],CONVERT(numeric, import.[Kitchens]),import.[Kitchen Type],import.[Kitchen Style 1],import.[Kitchen Style 2]
,import.[Kitchen Style 3],import.[Fireplaces],CONVERT(bit, import.[Penthouse Unit]),import.[AC Type],import.[Heat Type],import.[Year Built],import.[Interior Condition],import.[Interior Finish]
,import.[View],import.[Grade],import.[# of Parking Spots],import.[Parking Ownership],import.[Parking Type],import.[Tandem Parking],import.[Complex],import.[Story Height],import.[Roof Cover]
,import.[Roof Structure],import.[Exterior Condition],import.[Exterior Finish],import.[Building Style],import.[Foundation]);
-- -------------------------------------------
-- [RESIDENTIAL PROPERTY ATTRIBUTES] Bring in residential attributes data updates
-- -------------------------------------------
MERGE dbo.[RESIDENTIAL PROPERTY ATTRIBUTES] as base -- table shown in ATTRIBUTES for residential properties
USING dbo._RESIDENTIAL_PROPERTY_ATTRIBUTES AS import ON base.parcel_id = import.parcel_id and base.line_number = import.line_number
WHEN MATCHED THEN
UPDATE SET
[Composite Land Use] = import.[Composite Land Use] -- shown in Attributes
,[Building Style] = import.[Building Style] -- shown in Attributes
,[Rooms] = import.[Rooms] -- shown in Attributes
,[Bedrooms] = import.[Bedrooms] -- shown in Attributes
,[Full Bath] = CONVERT(numeric, import.[Full Bath]) -- shown in Attributes
,[Half Bath] = CONVERT(numeric, import.[Half Bath]) -- shown in Attributes
,[Other Fixtures] = CONVERT(numeric, import.[Other Fixtures]) -- shown in Attributes
,[Bath Style 1] = import.[Bath Style 1] -- shown in Attributes
,[Bath Style 2] = import.[Bath Style 2] -- shown in Attributes
,[Bath Style 3] = import.[Bath Style 3] -- shown in Attributes
,[Kitchens] = CONVERT(numeric, import.[Kitchens]) -- shown in Attributes
,[Kitchen Type] = import.[Kitchen Type] -- shown in Attributes
,[Kitchen Style 1] = import.[Kitchen Style 1] -- shown in Attributes
,[Kitchen Style 2] = import.[Kitchen Style 2] -- shown in Attributes
,[Kitchen Style 3] = import.[Kitchen Style 3] -- shown in Attributes
,[Fireplaces] = import.[Fireplaces] -- shown in Attributes
,[AC Type] = import.[AC Type] -- shown in Attributes
,[Heat Type] = import.[Heat Type] -- shown in Attributes
,[Interior Condition] = import.[Interior Condition] -- shown in Attributes
,[Interior Finish] = import.[Interior Finish] -- shown in Attributes
,[View] = import.[View] -- shown in Attributes
,[Grade] = import.[Grade] -- shown in Attributes
,[# of Parking Spots] = import.[# of Parking Spots] -- shown in Attributes
,[Year Built] = import.[Year Built] -- shown in Attributes
,[Story Height] = import.[Story Height] -- shown in Attributes
,[Roof Cover] = import.[Roof Cover] -- shown in Attributes
,[Roof Structure] = import.[Roof Structure] -- shown in Attributes
,[Exterior Condition] = import.[Exterior Condition] -- shown in Attributes
,[Exterior Finish] = import.[Exterior Finish] -- shown in Attributes
,[Foundation] = import.[Foundation] -- shown in Attributes
WHEN NOT MATCHED THEN
INSERT (parcel_id,[line_number],[Composite Land Use],[Building Style],[Rooms],[Bedrooms]
,[Full Bath],[Half Bath]
,[Other Fixtures],[Bath Style 1],[Bath Style 2],[Bath Style 3],[Kitchens],[Kitchen Type],[Kitchen Style 1],[Kitchen Style 2]
,[Kitchen Style 3],[Fireplaces],[AC Type],[Heat Type],[Year Built],[Interior Condition],[Interior Finish]
,[View],[Grade],[# of Parking Spots],[Story Height],[Roof Cover]
,[Roof Structure],[Exterior Condition],[Exterior Finish],[Foundation])
VALUES (CONVERT(nvarchar(10), import.[parcel_id]),import.[line_number],import.[Composite Land Use],import.[Building Style],import.[Rooms],import.[Bedrooms]
,CONVERT(numeric, import.[Full Bath]),CONVERT(numeric, import.[Half Bath])
,import.[Other Fixtures],import.[Bath Style 1],import.[Bath Style 2],import.[Bath Style 3],CONVERT(numeric, import.[Kitchens]),import.[Kitchen Type],import.[Kitchen Style 1],import.[Kitchen Style 2]
,import.[Kitchen Style 3],import.[Fireplaces],import.[AC Type],import.[Heat Type],import.[Year Built],import.[Interior Condition],import.[Interior Finish]
,import.[View],import.[Grade],import.[# of Parking Spots],import.[Story Height],import.[Roof Cover]
,import.[Roof Structure],import.[Exterior Condition],import.[Exterior Finish],import.[Foundation]);
-- -------------------------------------------
-- [Res_exempt] Update exemption info (there may be new parcels, so try to find and add those first)
-- -------------------------------------------
IF @quarter = 1
-- Q1 VERSION
BEGIN
INSERT INTO dbo.[Res_exempt](parcel_id, personal_exemption, residential_exemption)
SELECT pers.parcel_id, IIF(pers.[Personal Exemption] = 'Y',1,0), 0
FROM dbo.Res_exempt base
RIGHT OUTER JOIN dbo._FY2024_PERSONAL_EXEMPTION_FLAG pers ON base.parcel_id = pers.parcel_id
WHERE base.parcel_id IS NULL;
INSERT INTO dbo.[Res_exempt](parcel_id, residential_exemption, personal_exemption)
SELECT res.parcel_id, res.[Residential Exemption], 0
FROM dbo.Res_exempt base
RIGHT OUTER JOIN dbo._FY2024_RESIDENTIAL_EXEMPTION_FLAG res ON base.parcel_id = res.parcel_id
WHERE base.parcel_id IS NULL;
UPDATE dbo.[Res_exempt]
SET
personal_exemption = IIF(pers.[Personal Exemption]='Y',1,0)
FROM dbo.Res_exempt AS base
INNER JOIN dbo._FY2024_PERSONAL_EXEMPTION_FLAG AS pers ON base.parcel_id = pers.parcel_id;
UPDATE dbo.[Res_exempt]
SET
residential_exemption = CONVERT(varchar(1), res.[Residential Exemption])
FROM dbo.Res_exempt AS base
INNER JOIN dbo._FY2024_RESIDENTIAL_EXEMPTION_FLAG res ON base.parcel_id = res.parcel_id;
END;
IF @quarter = 3
-- Q3 VERSION
BEGIN
DROP TABLE IF EXISTS dbo.Res_exempt;
SELECT
import.parcel_id as parcel_id
,convert(tinyint, IIF(import.[clause_abatement_type_1] IS NULL AND import.[clause_abatement_type_2] IS NULL, 0, 1)) as personal_exemption -- 0/1
,convert(varchar(1), import.[residential_exemption_flag]) as residential_exemption -- Y/N
INTO dbo.Res_exempt
FROM dbo._TYLER_REAL_ESTATE_EXPORT_FILE import
END;
-- -------------------------------------------
-- [current owners] Overwrite current owners info
-- -------------------------------------------
DROP TABLE IF EXISTS dbo.current_owners;
SELECT CAST(import.parcel_id AS NCHAR(10)) as parcel_id
,CAST(import.seqno AS int) as seqno
,CAST(import.owner_name AS NVARCHAR(255)) as [owner_name]
INTO dbo.current_owners
FROM dbo._CURRENT_OWNERS as import;
-- -------------------------------------------
--[bid] overwrite the bid amounts from preliminary tax table.
-- -------------------------------------------
IF @quarter = 1
-- Q1 VERSION - Copy across from PRELIMINARY TAXES
BEGIN
DROP TABLE IF EXISTS dbo.bid;
SELECT
CONVERT(nchar(10), import.parcel_id) as parcel_id,
CONVERT(money, import.[Downtown BID Amt]) as bid_downtown,
CONVERT(money, import.[Greenway BID Amt]) as bid_greenway,
CONVERT(money, import.[Newmarket BID Amt]) as bid_newmarket
INTO dbo.bid
FROM dbo._FY2025_PRELIMINARY_TAXES AS import
WHERE import.[Downtown BID Amt] <> 0
OR import.[Greenway BID Amt] <> 0
OR import.[Newmarket BID Amt] <> 0;
END;
IF @quarter = 3
-- Q3 VERSION - Copy across from the BID import tables.
BEGIN
DROP TABLE IF EXISTS dbo.bid;
SELECT
CONVERT(nchar(10), import.parcel_id) as parcel_id,
CONVERT(money, bid_1.Billed) as bid_downtown,
CONVERT(money, bid_2.Billed) as bid_greenway,
CONVERT(money, bid_3.Billed) as bid_newmarket
INTO dbo.bid
FROM dbo._Tyler_Real_Estate_Export_File AS import
LEFT OUTER JOIN dbo._FY2024_Downtown_BID bid_1 on import.parcel_id = bid_1.parcel_id
LEFT OUTER JOIN dbo._FY2024_Greenway_BID bid_2 on import.parcel_id = bid_2.parcel_id
LEFT OUTER JOIN dbo._FY2024_Newmarket_BID bid_3 on import.parcel_id = bid_3.parcel_id;
END;
-- -------------------------------------------
-- [tax_preliminary] Bring in the preliminary tax info
-- -------------------------------------------
-- Each quarter, verify the [Total Billed Amt] calculation with Francis Gavin/Assessing
IF @quarter = 1
-- Q1 VERSION
BEGIN
MERGE dbo.tax_preliminary AS base -- Table data used in FY20YY PRELIMINARY TAX
USING dbo.[_FY2025_PRELIMINARY_TAXES] AS import ON base.parcel_id = import.parcel_id
WHEN MATCHED THEN
UPDATE SET
base.[Bill Year] = CONVERT(smallint, import.[Bill Year]),
base.[Bill Number] = CONVERT(int, import.[Bill Number]),
base.[RE Tax Amt] = import.[RE Tax], -- shown in Estimated Tax
base.[CPA Amt] = import.[CPA Tax], -- shown in Community Preservation
base.[Total Billed Amt] = import.[RE Tax]+import.[CPA Tax], -- shown in Total Tax, First Half
base.[Downtown BID Amt] = import.[Downtown BID Amt],
base.[Greenway BID Amt] = import.[Greenway BID Amt],
base.[Newmarket BID Amt] = import.[Newmarket BID Amt]
WHEN NOT MATCHED THEN
INSERT (parcel_id, [Bill Year], [Bill Number]
,[RE Tax Amt], [CPA Amt], [Downtown BID Amt], [Greenway BID Amt], [Newmarket BID Amt]
,[Total Billed Amt])
VALUES (import.parcel_id, CONVERT(smallint, import.[Bill Year]), CONVERT(int, import.[Bill Number])
,import.[RE Tax], import.[CPA Tax], import.[Downtown BID Amt], import.[Greenway BID Amt], import.[Newmarket BID Amt]
,import.[RE Tax]+import.[CPA Tax]);
END;
IF @quarter = 3
-- Q3 VERSION
BEGIN
TRUNCATE TABLE dbo.tax_preliminary;
INSERT INTO dbo.tax_preliminary (parcel_id, [Bill Year], [Bill Number]
,[RE Tax Amt], [CPA Amt], [Downtown BID Amt], [Greenway BID Amt], [Newmarket BID Amt]
,[Total Billed Amt])
SELECT tax.parcel_id
, CONVERT(smallint, tax.[Bill Year])
, CONVERT(int, tax.[Bill Number])
, tax.[Gross RE Tax]
, tax.[CPA Tax]
, bid.bid_downtown
, bid.bid_greenway
, bid.bid_newmarket
, (tax.[Gross RE Tax]+tax.[CPA Tax])
FROM dbo._FY2024_Taxes as tax
INNER JOIN dbo.bid bid ON tax.parcel_id = bid.parcel_id;
END;
-- -------------------------------------------
-- [parcel] Update parcel info (there may be new parcels, so try to find them and add them first)
-- -------------------------------------------
IF @quarter = 1
-- Q1 VERSION
BEGIN
INSERT INTO dbo.[parcel] ([parcel_id])
SELECT CONVERT(nchar(10), upd.parcel_id)
FROM dbo.[parcel] base
RIGHT OUTER JOIN dbo.[_TYLER_REAL_ESTATE_EXPORT_FILE] upd ON base.parcel_id = upd.parcel_id
WHERE base.parcel_id IS NULL;
UPDATE dbo.[parcel]
SET
[street_number]=tyler.[street_number]
,[street_name]=UPPER(stnames.[Street Name])
,[apartment_no]=tyler.apt_unit
,[suffix]=CONVERT(nvarchar(2), LEFT(stnames.[Street Way], 2)) -- left 2 chars
,[landuse]=tyler.land_use
,[owner]=tyler.owner
,[condo_main]=CONVERT(nchar(10), tyler.condo_main)
FROM dbo.parcel AS base
INNER JOIN dbo._TYLER_REAL_ESTATE_EXPORT_FILE AS tyler ON base.parcel_id = tyler.parcel_id
INNER JOIN dbo.[_STREET_NUMBER_AND_STREET_NAME] AS stnames on base.parcel_id = stnames.parcel_id;
END;
IF @quarter = 3
-- Q3 VERSION Recreate parcel info
BEGIN
DROP TABLE IF EXISTS dbo.parcel;
SELECT
CONVERT(nchar(10), tyler.parcel_id) as parcel_id
,CONVERT(nvarchar(10), tyler.[street_number]) as street_number
,CONVERT(nvarchar(50), UPPER(stnames.[Street Name])) as street_name
,CONVERT(nvarchar(20), tyler.apt_unit) as apartment_no
,CONVERT(nvarchar(2), LEFT(stnames.[Street Way], 2)) as suffix -- left 2 chars
,CONVERT(nvarchar(2), tyler.land_use) as landuse
,CONVERT(nvarchar(255), tyler.owner) as owner
,CONVERT(nchar(10), tyler.condo_main) as condo_main
INTO dbo.parcel
FROM dbo._TYLER_REAL_ESTATE_EXPORT_FILE AS tyler
INNER JOIN dbo.[_STREET_NUMBER_AND_STREET_NAME] AS stnames on tyler.parcel_id = stnames.parcel_id;
END;
-- -------------------------------------------
-- [parcel_all_owners] Recreate the all_owners table (table data used in searching functions)
-- -------------------------------------------
DROP TABLE IF EXISTS dbo.[parcel_all_owners];
SELECT CAST(parcel.parcel_id AS NCHAR(10)) as parcel_id
,CAST(parcel.street_number AS NVARCHAR(10)) as street_number
,CAST(parcel.street_name AS NVARCHAR(50)) as street_name
,CAST(parcel.apartment_no AS NVARCHAR(20)) as apartment_no
,CAST(parcel.suffix AS NVARCHAR(2)) as suffix
,CAST(parcel.landuse AS NVARCHAR(2)) as landuse
,CAST(owners.[owner_name] AS NVARCHAR(255)) as owner
,CAST(parcel.condo_main AS NCHAR(10)) as condo_main
INTO dbo.parcel_all_owners
FROM dbo.current_owners as owners
INNER JOIN dbo.parcel as parcel ON owners.parcel_id = parcel.parcel_id
-- -------------------------------------------
-- [outbuildings] Recreate the outbuildings table
-- -------------------------------------------
DROP TABLE IF EXISTS dbo.[outbuildings];
SELECT CAST(import.parcel_id AS NCHAR(10)) as parcel_id -- table data shown in OUTBUILDINGS/EXTRA FEATURES
,CAST(0 AS TINYINT) as line_number -- shown in OUTBUILDINGS/EXTRA FEATURES
,CAST(import.Code AS NVARCHAR(255)) as Code -- shown in OUTBUILDINGS/EXTRA FEATURES
,CAST(import.[Tot Units] AS DECIMAL(9,2)) as [Tot Units] -- shown in OUTBUILDINGS/EXTRA FEATURES
,CAST(import.Quality AS NVARCHAR(50)) as Quality -- shown in OUTBUILDINGS/EXTRA FEATURES
,CAST(import.Condition AS NVARCHAR(50)) as Condition -- shown in OUTBUILDINGS/EXTRA FEATURES
INTO dbo.outbuildings
FROM dbo._SPECIAL_FEATURES as import;
-- -------------------------------------------
-- [taxbill] Update the taxbill table
-- -------------------------------------------
-- Import base taxbill data
MERGE dbo.taxbill AS base
USING dbo.[_TYLER_REAL_ESTATE_EXPORT_FILE] AS import ON base.parcel_id = import.parcel_id
WHEN MATCHED THEN
UPDATE SET
street_number = CONVERT(nvarchar(10), ISNULL(import.street_number, '')) -- shown in Address
,ward_precinct_block = CONVERT(nvarchar(7), ISNULL(import.ward_precinct_block,''))
,street_number_suffix = CONVERT(nvarchar(10), ISNULL(import.street_number_suffix, '')) -- shown in Address
,street_name = CONVERT(nvarchar(50), ISNULL(import.street_name, '')) -- shown in Address
,apt_unit = CONVERT(nvarchar(20), ISNULL(import.apt_unit, '')) -- shown in Address
,location_zip_code = CONVERT(nvarchar(5), ISNULL(import.location_zip_code, '')) -- shown in Address
,property_type = CONVERT(nvarchar(4), import.property_type) -- shown in Property Type (plus lookup to [dbo].[propertycodes_described])
,land_use = CONVERT(nvarchar(2), import.land_use) -- shown in Classification Code (plus lookup to [dbo].[Landuse_Described])
,land_area = CONVERT(int, CONVERT(numeric, ISNULL(import.land_area, 0))) -- shown in Lot Size
,[owner] = CONVERT(nvarchar(50), import.owner) -- shown in Owner (as at beginning Q3 last year)
,mail_addressee = CONVERT(nvarchar(50), ISNULL(import.mail_addressee, '')) -- shown in Owner Address
,mail_street_address1 = CONVERT(nvarchar(50), ISNULL(import.mail_street_address1, '')) -- shown in Owner Address
,mail_street_address2 = CONVERT(nvarchar(50), ISNULL(import.mail_street_address2, '')) -- shown in Owner Address
,mail_city_and_state = CONVERT(nvarchar(50), ISNULL(import.mail_city_and_state, '')) -- shown in Owner Address
,mail_zip_code = CONVERT(nvarchar(5), ISNULL(import.mail_zip_code, '')) -- shown in Owner Address
,fine1_amt = 0 -- Assessing will advise if non-zero
,fine2_amt = 0 -- Assessing will advise if non-zero
,fine3_amt = 0 -- Assessing will advise if non-zero
,penalty1_amt = 0 -- Assessing will advise if non-zero
,penalty2_amt = 0 -- Assessing will advise if non-zero
,nontax_collection_amt = 0 -- Assessing will advise if non-zero
,street_betterment_amt = 0 -- Assessing will advise if non-zero
,sidewalk_betterment_amt = 0 -- Assessing will advise if non-zero
,sewer_betterment_amt = 0 -- Assessing will advise if non-zero
,workoff_credit_amt = 0 -- Assessing will advise if non-zero
,condo_main = CONVERT(nchar(10), import.condo_main)
WHEN NOT MATCHED THEN
INSERT (parcel_id
, ward_precinct_block, street_name, city
, location_zip_code_plus_four, country
, future_owner, future_mail_addressee, future_owner_mail_street_address1
, future_owner_mail_street_address2, future_owner_mail_city_and_state, future_owner_mail_zip_code
, future_owner_mail_zip_code_plus_four
, street_number, street_number_suffix, apt_unit, location_zip_code, property_type, land_use
, land_area, [owner], mail_addressee, mail_street_address1, mail_street_address2, mail_city_and_state, mail_zip_code
, fine1_amt, fine2_amt, fine3_amt, penalty1_amt, penalty2_amt, nontax_collection_amt, street_betterment_amt
, sidewalk_betterment_amt, sewer_betterment_amt, workoff_credit_amt,
condo_main)
VALUES (CONVERT(nchar(10), import.parcel_id)
,CONVERT(nvarchar(7), ISNULL(import.ward_precinct_block,'')),CONVERT(nvarchar(50), ISNULL(import.street_name,'')),CONVERT(nvarchar(50), ISNULL(import.city,''))
,CONVERT(nvarchar(4), ISNULL(import.location_zip_code_plus_four,'')),CONVERT(nvarchar(50), ISNULL(import.country,''))
,CONVERT(nvarchar(50), ISNULL(import.future_owner,'')),CONVERT(nvarchar(50), ISNULL(import.future_mail_addressee,'')),CONVERT(nvarchar(50), ISNULL(import.future_owner_mail_street_address1,''))
,CONVERT(nvarchar(50), ISNULL(import.future_owner_mail_street_address2,'')),CONVERT(nvarchar(50), ISNULL(import.future_owner_mail_city_and_state,'')),CONVERT(nvarchar(5), ISNULL(import.future_owner_mail_zip_code,''))
,CONVERT(nvarchar(4), ISNULL(import.future_owner_mail_zip_code_plus_four,''))
,CONVERT(nvarchar(10), ISNULL(import.street_number,'')), CONVERT(nvarchar(10), ISNULL(import.street_number_suffix, ''))
,CONVERT(nvarchar(20), ISNULL(import.apt_unit, '')), CONVERT(nvarchar(5), ISNULL(import.location_zip_code, '')), CONVERT(nvarchar(4), import.property_type)
,CONVERT(nvarchar(2), import.land_use), CONVERT(int, ISNULL(import.land_area, 0)), CONVERT(nvarchar(50), import.owner)
,CONVERT(nvarchar(50), ISNULL(import.mail_addressee, '')), CONVERT(nvarchar(50), ISNULL(import.mail_street_address1,'')), CONVERT(nvarchar(50), ISNULL(import.mail_street_address2, ''))
,CONVERT(nvarchar(50), ISNULL(import.mail_city_and_state, '')), CONVERT(nvarchar(5), ISNULL(import.mail_zip_code, ''))
,0, 0, 0, 0, 0, 0, 0,0, 0, 0 -- Assessing will advise if these are non-zero amts.
, CONVERT(nchar(10), import.condo_main));
-- Import exemptions into taxbill
UPDATE dbo.taxbill
SET
residential_exemption_flag = iif(import.residential_exemption = 'Y', 1, 0) -- Shown in Residential Exemption
,personal_exemption_flag = import.personal_exemption -- Shown in Personal Exemption
FROM dbo.taxbill base
INNER JOIN dbo.Res_exempt import on base.parcel_id = import.parcel_id
-- Ensure correct street address is used in taxbill
UPDATE dbo.[taxbill]
SET
[street_name_only]=p.street_name
,[street_name_suffix]=ISNULL(p.suffix, '')
FROM dbo.taxbill AS base
INNER JOIN dbo.[parcel] AS p on base.parcel_id = p.parcel_id
-- Updates assessed values in taxbill
IF @quarter = 3
-- Q3 VERSION
BEGIN
UPDATE dbo.[taxbill]
SET
[residential_building_value] = CONVERT(DECIMAL(13,2), ISNULL(import.[residential_building_value],0))
,[residential_land_value] = CONVERT(DECIMAL(13,2), ISNULL(import.[residential_land_value],0))
,[residential_air_rights_value] = CONVERT(DECIMAL(13,2), ISNULL(import.[residential_air_rights_value],0))
,[commercial_building_value] = CONVERT(DECIMAL(13,2), ISNULL(import.[commercial_building_value],0))
,[commercial_land_value] = CONVERT(DECIMAL(13,2), ISNULL(import.[commercial_land_value],0))
,[commercial_air_rights_value] = CONVERT(DECIMAL(13,2), ISNULL(import.[commercial_air_rights_value],0))
,[industrial_building_value] = CONVERT(DECIMAL(13,2), ISNULL(import.[industrial_building_value],0))
,[industrial_land_value] = CONVERT(DECIMAL(13,2), ISNULL(import.[industrial_land_value],0))
,[industrial_air_rights_value] = CONVERT(DECIMAL(13,2), ISNULL(import.[industrial_air_rights_value],0))
,[open_space_land_value] = CONVERT(DECIMAL(13,2), ISNULL(import.[open_space_land_value],0))
,[exempt_building_value] = CONVERT(DECIMAL(13,2), ISNULL(import.[exempt_building_value],0))
,[exempt_land_value] = CONVERT(DECIMAL(13,2), ISNULL(import.[exempt_land_value],0))
,[total_building_value] = CONVERT(DECIMAL(13,2), ISNULL(import.[residential_building_value],0)) + CONVERT(DECIMAL(13,2), ISNULL(import.[commercial_building_value],0)) + CONVERT(DECIMAL(13,2), ISNULL(import.[industrial_building_value],0)) + CONVERT(DECIMAL(13,2), ISNULL(import.[exempt_building_value],0))
,[total_land_value] = CONVERT(DECIMAL(13,2), ISNULL(import.[residential_land_value],0)) + CONVERT(DECIMAL(13,2), ISNULL(import.[commercial_land_value],0)) + CONVERT(DECIMAL(13,2), ISNULL(import.[industrial_land_value],0)) + CONVERT(DECIMAL(13,2), ISNULL(import.[exempt_land_value],0))
,[total_value] = ISNULL(taxes.[Total Assessed Value], 0)
,[latest_bkpgcert] = CONVERT(NVARCHAR(10), ISNULL(import.[latest_bkpgcert],''))
,[latest_sale_date] = CONVERT(date, import.[latest_sale_date])
,[coop_value] = CONVERT(DECIMAL(13,2), ISNULL(import.[coop_value],0))
,[clause_abatement_type_1] = CONVERT(NVARCHAR(6), ISNULL(import.[clause_abatement_type_1],''))
,[clause_abt_1_pct_ownership] = CONVERT(NVARCHAR(5), ISNULL(import.[clause_abt_1_pct_ownership],''))
,[clause_abt_1_pct_occupancy] = CONVERT(NVARCHAR(5), ISNULL(import.[clause_abt_1_pct_occupancy],''))
,[clause_abatement_type_2] = CONVERT(NVARCHAR(6), ISNULL(import.[clause_abatement_type_2],''))
,[clause_abt_2_pct_ownership] = CONVERT(NVARCHAR(5), ISNULL(import.[clause_abt_2_pct_ownership],''))
,[clause_abt_2_pct_occupancy] = CONVERT(NVARCHAR(5), ISNULL(import.[clause_abt_2_pct_occupancy],''))
,[paraplegic] = CONVERT(NVARCHAR(6), ISNULL(import.[paraplegic],''))
,[workoff_type] = CONVERT(NVARCHAR(6), ISNULL(import.[workoff_type],''))
,[workoff_credit_amt] = CONVERT(DECIMAL(12,2), ISNULL(import.[workoff_credit_amt],0))
,[sewer_betterment_amt] = CONVERT(DECIMAL(12,2), ISNULL(import.[sewer_betterment_amt],0))
,[street_betterment_amt] = CONVERT(DECIMAL(12,2), ISNULL(import.[street_betterment_amt],0))
,[sidewalk_betterment_amt] = CONVERT(DECIMAL(12,2), ISNULL(import.[sidewalk_betterment_amt],0))
,[penalty1_type] = CONVERT(NVARCHAR(6), ISNULL(import.[penalty1_type],''))
,[penalty1_amt] = CONVERT(DECIMAL(12,2), ISNULL(import.[penalty1_amt],0))
,[penalty2_type] = CONVERT(NVARCHAR(6), ISNULL(import.[penalty2_type],''))
,[penalty2_amt] = CONVERT(DECIMAL(12,2), ISNULL(import.[penalty2_amt],0))
,[penalty3_type] = CONVERT(NVARCHAR(6), ISNULL(import.[penalty3_type],''))
,[penalty3_amt] = CONVERT(DECIMAL(12,2), ISNULL(import.[penalty3_amt],0))
,[fine1_type] = CONVERT(NVARCHAR(6), ISNULL(import.[fine1_type],''))
,[fine1_amt] = CONVERT(DECIMAL(12,2), ISNULL(import.[fine1_amt],0))
,[fine2_type] = CONVERT(NVARCHAR(6), ISNULL(import.[fine2_type],''))
,[fine2_amt] = CONVERT(DECIMAL(12,2), ISNULL(import.[fine2_amt],0))
,[fine3_type] = CONVERT(NVARCHAR(6), ISNULL(import.[fine3_type],''))
,[fine3_amt] = CONVERT(DECIMAL(12,2), ISNULL(import.[fine3_amt],0))
,[nontax_collection_type] = CONVERT(NVARCHAR(6), ISNULL(import.[nontax_collection_type],''))
,[nontax_collection_amt] = CONVERT(DECIMAL(12,2), ISNULL(import.[nontax_collection_amt],0))
FROM dbo.taxbill AS base
LEFT OUTER JOIN dbo.[_Tyler_Real_Estate_Export_File] AS import on base.parcel_id = import.parcel_id
LEFT OUTER JOIN dbo.[_FY2024_Taxes] as taxes on base.parcel_id = taxes.parcel_id;
END;
-- -------------------------------------------
-- [taxes] Recreate the taxes table
-- -------------------------------------------
IF @quarter = 1
-- Q1 VERSION - Create the taxes table from PRELIMINARY_TAXES
BEGIN
DROP TABLE IF EXISTS dbo.[taxes];
SELECT
import.parcel_id as parcel_id
,cast(0 as DECIMAL(12,2)) as gross_tax -- Not used in Q1
,cast(0 as DECIMAL(12,2)) as net_tax -- Not used in Q1
,cast(0 as DECIMAL(12,2)) as persexempt_1 -- Not used in Q1
,cast(0 as DECIMAL(12,2)) as persexempt_2 -- Not used in Q1
,cast(0 as DECIMAL(12,2)) as persexempt_total -- Not used in Q1
,cast(0 as DECIMAL(12,2)) as resexempt -- Not used in Q1
,cast(0 as DECIMAL(12,2)) as cpa -- Not used in Q1
,cast(0 as DECIMAL(12,2)) as code_enforcement_tax -- Not used in Q1
,cast(0 as DECIMAL(12,2)) as [38D_fine] -- Not used in Q1
,cast(0 as DECIMAL(12,2)) as sidewalk_betterment -- Not used in Q1
,cast(0 as DECIMAL(12,2)) as street_betterment -- Not used in Q1
,CAST(import.[Bill Number] as int) as [bill_number]
INTO dbo.taxes
FROM dbo._FY2025_PRELIMINARY_TAXES as import
END;
IF @quarter = 3
--Q3 VERSION - Create the taxes table from FY202X_TAXES
BEGIN
DROP TABLE IF EXISTS dbo.[taxes];
SELECT
import.parcel_id as parcel_id
,cast(import.[Gross RE Tax] as DECIMAL(12,2)) as gross_tax
,cast(0 as DECIMAL(12,2)) as net_tax
,cast(ISNULL(import.[Personal Ex Amt 1],0) as DECIMAL(12,2)) as persexempt_1
,cast(ISNULL(import.[Personal Ex Amt 2],0) as DECIMAL(12,2)) as persexempt_2
,cast(ISNULL(import.[Personal Ex Amt 1],0) as DECIMAL(12,2)) + cast(ISNULL(import.[Personal Ex Amt 2],0) as DECIMAL(12,2)) as persexempt_total
,cast(import.[Resex amt] as DECIMAL(12,2)) as resexempt
,cast(import.[CPA Tax] as DECIMAL(12,2)) as cpa
,cast(ISNULL(import.[Code Enforcement Tax],0) as DECIMAL(12,2)) as code_enforcement_tax
,cast(ISNULL(import.[38D Fine],0) as DECIMAL(12,2)) as [38D_fine]
,cast(ISNULL(import.[Sidewalk Betterment],0) as DECIMAL(12,2)) as sidewalk_betterment
,cast(ISNULL(import.[Street Betterment],0) as DECIMAL(12,2)) as street_betterment
,CAST(import.[Bill Number] as int) as [bill_number]
INTO dbo.taxes
FROM dbo._FY2024_Taxes as import
-- for FY2024 Q3 need to run this to correct Net value.
UPDATE dbo.taxes
SET net_tax = cast(taxes.[Net RE Tax] as DECIMAL(12,2)) - base.persexempt_1 - base.persexempt_2
FROM dbo.taxes AS base
INNER JOIN dbo.[_FY2024_Taxes] as taxes on base.parcel_id = taxes.parcel_id;
END;
-- -------------------------------------------
-- [value_history] Adds/Updates current PY valuation info to history table
-- -------------------------------------------
IF @quarter = 3
-- Q3 VERSION
BEGIN
SET @fiscal_year = YEAR(GETDATE()) + 1; -- NOTE: Assumes this sp is run in December
MERGE dbo.value_history AS base
USING dbo.[taxbill] AS import ON base.parcel_id = import.parcel_id and base.Fiscal_Year = @fiscal_year
WHEN MATCHED THEN
UPDATE SET
Assessed_value = CONVERT(bigint, import.total_value)
,Land_use = CONVERT(nvarchar(4), import.land_use)
WHEN NOT MATCHED THEN
INSERT (parcel_id,Fiscal_Year,Assessed_value,Land_use)
VALUES (
CONVERT(nchar(10), import.parcel_id)
,@fiscal_year
,CONVERT(bigint, import.total_value)
,CONVERT(nvarchar(4), import.land_use)
);
END;
--
--****** REMOVE DELETED PARCELS ************
DELETE data FROM bid AS data
LEFT OUTER JOIN _TYLER_REAL_ESTATE_EXPORT_FILE AS import on data.parcel_id = import.parcel_id
WHERE import.parcel_id IS NULL;
DELETE data FROM current_owners AS data
LEFT OUTER JOIN _TYLER_REAL_ESTATE_EXPORT_FILE AS import on data.parcel_id = import.parcel_id
WHERE import.parcel_id IS NULL;
DELETE data FROM parcel AS data
LEFT OUTER JOIN _TYLER_REAL_ESTATE_EXPORT_FILE AS import on data.parcel_id = import.parcel_id
WHERE import.parcel_id IS NULL;
DELETE data FROM parcel_all_owners AS data
LEFT OUTER JOIN _TYLER_REAL_ESTATE_EXPORT_FILE AS import on data.parcel_id = import.parcel_id
WHERE import.parcel_id IS NULL;
DELETE data FROM Res_exempt AS data
LEFT OUTER JOIN _TYLER_REAL_ESTATE_EXPORT_FILE AS import on data.parcel_id = import.parcel_id
WHERE import.parcel_id IS NULL;
DELETE data FROM tax_preliminary AS data
LEFT OUTER JOIN _TYLER_REAL_ESTATE_EXPORT_FILE AS import on data.parcel_id = import.parcel_id
WHERE import.parcel_id IS NULL;
DELETE data FROM taxbill AS data
LEFT OUTER JOIN _TYLER_REAL_ESTATE_EXPORT_FILE AS import on data.parcel_id = import.parcel_id
WHERE import.parcel_id IS NULL;
SELECT
(SELECT count(*) FROM [dbo].[_ADDITIONAL_DATA]) _ADDITIONAL_DATA
,(SELECT count(*) FROM [dbo].[_CONDO_PROPERTY_ATTRIBUTES]) _CONDO_PROPERTY_ATTRIBUTES
,(SELECT count(*) FROM [dbo].[_CURRENT_OWNERS]) _CURRENT_OWNERS
,(SELECT count(*) FROM [dbo].[_FY2024_PERSONAL_EXEMPTION_FLAG]) _FY2024_PERSONAL_EXEMPTION_FLAG
,(SELECT count(*) FROM [dbo].[_FY2024_RESIDENTIAL_EXEMPTION_FLAG]) _FY2024_RESIDENTIAL_EXEMPTION_FLAG
-- ,(SELECT count(*) FROM [dbo].[_FY2024_Downtown_BID]) _FY2024_Downtown_BID
-- ,(SELECT count(*) FROM [dbo].[_FY2024_Greenway_BID]) _FY2024_Greenway_BID
-- ,(SELECT count(*) FROM [dbo].[_FY2024_Newmarket_BID]) _FY2024_Newmarket_BID
,(SELECT count(*) FROM [dbo].[_FY2025_PRELIMINARY_TAXES]) _FY2025_PRELIMINARY_TAXES
-- ,(SELECT count(*) FROM [dbo].[_FY2024_Taxes]) _FY2024_Taxes
,(SELECT count(*) FROM [dbo].[_RESIDENTIAL_PROPERTY_ATTRIBUTES]) _RESIDENTIAL_PROPERTY_ATTRIBUTES
,(SELECT count(*) FROM [dbo].[_Sheet]) _Sheet
,(SELECT count(*) FROM [dbo].[_SPECIAL_FEATURES]) _SPECIAL_FEATURES
,(SELECT count(*) FROM [dbo].[_STREET_NUMBER_AND_STREET_NAME]) _STREET_NUMBER_AND_STREET_NAME
,(SELECT count(*) FROM [dbo].[_Tyler_Real_Estate_Export_File]) _Tyler_Real_Estate_Export_File
--[overval_application_numbers] (appears not to be used)
END
GO
```Inactive
None of these stored procedures appear to be used by default.asp or any view or triggers.
These procedures could be deleted from the DB
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[parcel_value_history]
@parcelId nchar(10)
AS
BEGIN
SET NOCOUNT ON;
SELECT parcel_history.*, [landuse_described].[description]
FROM (SELECT * FROM [value_history] WHERE [value_history].parcel_id = @parcelId) AS parcel_history
JOIN [landuse_described] ON parcel_history.land_use=[landuse_described].Short_Description
ORDER BY parcel_history.fiscal_year DESC
END
GO
-- =============================================
-- Author: <Kumat,Rashmi>
-- Create date: <Jul 23,2008,>
-- Description: <Get Exempt status based on parcel id >
-- =============================================
CREATE PROCEDURE [dbo].[sp_get_exempt_status]
-- Add the parameters for the stored procedure here
@parcel_id nchar(10),
@land_type nchar(2)
AS
BEGIN
SET NOCOUNT ON;
declare @personal_exemption nchar(5)
if @land_type = 'r'
begin
select residential_exemption from res_exempt where parcel_id = @parcel_id
end
else
begin
select @personal_exemption = clause_abatement_type_1 from taxbillw where parcel_id = @parcel_id
if @personal_exemption is null or @personal_exemption = ''
select 'N'
else
select 'Y'
end
END
GO
-- =============================================
-- Author: <Kumat,Rashmi>
-- Create date: <Jul 30,2008,>
-- Description: <Get Application_number for overval forms >
-- Check wheteher Parcel Id exists . IF yes , then return full_appnumber
-- If parcel_id does not exist , insert one entry into table , with incremental app number and current year
-- =============================================
CREATE PROCEDURE [dbo].[sp_get_overval_application_number]
-- Add the parameters for the stored procedure here
@parcel_id nchar(10)
AS
BEGIN
SET NOCOUNT ON;
declare @count int
-- increment appyear to next calendar year after December 1, to make development easier
select @count = count(id) from overval_app_numbers where parcel_id = @parcel_id and [appyear] = Cast(YEAR(DATEADD(month,1,getdate())) as nchar(10))
--IF @count > 0 and @count = 1
-- begin
-- select full_appnumber from overval_app_numbers where parcel_id = @parcel_id
-- end
--
--else
if @count < 50
begin
declare @appyear nvarchar(10)
declare @apprange nvarchar(10)
declare @full_appnumber nvarchar(15)
set @appyear = YEAR(DATEADD(month,1,getdate()))
select @apprange = max(apprange) from overval_app_numbers where appyear = @appyear
if @apprange is null
set @apprange = 60000
else
set @apprange = @apprange + 1
set @full_appnumber = @appyear + @apprange
insert into overval_app_numbers (appyear,Parcel_id,apprange, full_appnumber)
values (@appyear,@parcel_id,@apprange,@full_appnumber)
select full_appnumber from overval_app_numbers where parcel_id = @parcel_id and apprange = @apprange
end
if @count >= 50
begin
select '50'
end
END
GO
-- =============================================
-- Author: <Kumat,Rashmi>
-- Create date: <Jul 23,2008,>
-- Description: <Get Residential or Personal Exemption data to prefill the PDF >
-- =============================================
CREATE PROCEDURE [dbo].[sp_get_overval_data]
-- Add the parameters for the stored procedure here
@parcel_id nchar(10)
AS
BEGIN
SET NOCOUNT ON;
select isnull(owner,'') , isnull(street_number,''), isnull(street_number_suffix,''),isnull(street_name,'') ,isnull(apt_unit,''),' BOSTON MA ',isnull(location_zip_code,''),isnull(land_use,''),isnull(total_value,'') ,isnull(bill_number,'')
from taxbillw a
where a.parcel_id = @parcel_id
END
GO
-- =============================================
-- Author: <Kumat,Rashmi>
-- Create date: <Jul 23,2008,>
-- Description: <Determine Overvaluation type based on parcel id >
-- =============================================
CREATE PROCEDURE [dbo].[sp_get_overval_type]
-- Add the parameters for the stored procedure here
@parcel_id nchar(10)
AS
BEGIN
SET NOCOUNT ON;
declare @land_use varchar(10)
declare @overval_type varchar(10)
select @land_use = land_use from taxbillw where parcel_id = @parcel_id
if @land_use in ('R1','R2','R3','CD')
set @overval_type = 'short'
else
set @overval_type = 'long'
select @overval_type
END
GO
-- =============================================
-- Author: Satyen
-- Create date: 2020-01
-- Description: Return parcel data to abatement/exemption .NET application
-- =============================================
CREATE PROCEDURE [dbo].[sp_get_pdf_data]
-- Add the parameters for the stored procedure here
@parcel_id nchar(10),
@form_type nvarchar(25)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @application_number int
IF @form_type = 'overval'
BEGIN
SELECT @application_number = [id] FROM [overval_application_numbers] WHERE [overval_application_numbers].[parcel_id]=@parcel_id and [id] >= (YEAR(DATEADD(month,1,getdate())) * 100000)
IF @application_number IS NULL
BEGIN
SELECT @application_number = 1 + MAX([id]) FROM [overval_application_numbers]
INSERT INTO [overval_application_numbers] VALUES (@application_number, @parcel_id)
END
END
IF ISNUMERIC(@parcel_id) = 1
SELECT [owner]
,[street_number]
,[street_number_suffix]
,[street_name]
,[apt_unit]
,[location_zip_code]
,[land_use]
,[total_value] = Format([total_value], 'N0')
,[bill_number]
,[residential_exemption]
,[personal_exemption]
,[application_number] = COALESCE(@application_number,0)
FROM [taxbill], [taxes], [Res_exempt]
WHERE [taxbill].[parcel_id] = @parcel_id
AND [taxes].[parcel_id] = @parcel_id
AND [Res_exempt].[parcel_id] = @parcel_id
END
GO
-- =============================================
-- Author: <Kumat,Rashmi>
-- Create date: <Jul 23,2008,>
-- Description: <Get Residential or Personal Exemption data to prefill the PDF >
-- =============================================
CREATE PROCEDURE [dbo].[sp_get_resex_and_persex_data]
-- Add the parameters for the stored procedure here
@parcel_id nchar(10)
AS
BEGIN
SET NOCOUNT ON;
select isnull(owner,'') , isnull(street_number,''), isnull(street_number_suffix,''), isnull(street_name,'') ,isnull(apt_unit,''),' BOSTON MA ',isnull(location_zip_code,''),isnull(land_use,'') from taxbillw where parcel_id = @parcel_id
END
GO
-- =============================================
-- Author: <Kumat,Rashmi>
-- Create date: <Jul 23,2008,>
-- Description: <Get Exempt status based on parcel id >
-- =============================================
CREATE PROCEDURE [dbo].[sp_get_resex_land_use]
-- Add the parameters for the stored procedure here
@parcel_id nchar(10)
AS
BEGIN
SET NOCOUNT ON;
declare @land_use varchar(10)
select @land_use = land_use from taxbillw where parcel_id = @parcel_id
If @land_use in ('R1','R2','R3','R4','CD','A','RC')
select 'Y'
else
select 'N'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Satyen
-- Create date: 2017-05-01
-- Description: update current owners
-- =============================================
CREATE PROCEDURE [dbo].[sp_update_current_owners]
AS
BEGIN
UPDATE [dbo].[parcel_from_vsql]
SET [owner] = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE([owner],' ',' '),' ',' '),' ',' ')))
UPDATE [current_owners]
SET [owner_name] = [parcel_from_vsql].[owner]
FROM [parcel_from_vsql]
WHERE [current_owners].[Parcel_id] <> [parcel_from_vsql].[parcelid]
AND [current_owners].[seqno] = 1
-- update all_owners table
DELETE FROM [parcel_all_owners]
INSERT INTO [parcel_all_owners]
SELECT [parcel_id], [street_number], [street_name], [apartment_no], [suffix], [landuse], [owner], [condo_main]
FROM [parcel]
UNION
SELECT [parcel].[parcel_id], [parcel].[street_number], [parcel].[street_name], [parcel].[apartment_no], [parcel].[suffix], [parcel].[landuse], coalesce([current_owners].[owner_name],''), [parcel].[condo_main]
FROM [current_owners] JOIN [parcel]
ON [current_owners].[parcel_id] = [parcel].[parcel_id]
--reindex
DBCC DBREINDEX(current_owners,'',100)
DBCC DBREINDEX(parcel_all_owners,'',100)
END
GO
Database Table Structure
additional_data
This table is accessed from default.asp.
Column
Source
Notes
parcel_id nvarchar(10)
MS Access
PK The unique parcel ID
living_area int
MS Access
gross_area int
MS Access
year_built smallint
MS Access
year_remodeled smallint
MS Access
condo_units_residential smallint
MS Access
condo_units_commercial smallint
MS Access
condo_units_mixed smallint
MS Access
stories decimal(4, 1)
MS Access
Check the columns in the MS Access database to see if new information is provided. If the unlikely event that it is, then check with Assessing Dept to see if the info needs to be displayed.
If so, then the query and HTML table in default.asp needs to be updated to display the new information.
USE assessingupdates2023Q3;
-- Create the table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP TABLE IF EXISTS dbo.additional_data;
GO
CREATE TABLE [dbo].[additional_data](
[parcel_id] [nchar](10) NOT NULL,
[living_area] [int] NULL,
[gross_area] [int] NULL,
[year_built] [smallint] NULL,
[year_remodeled] [smallint] NULL,
[condo_units_residential] [int] NULL,
[condo_units_commercial] [int] NULL,
[condo_units_mixed] [int] NULL,
[stories] [decimal](4, 1) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-115214] ON [dbo].[additional_data]
([parcel_id] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100)
ON [PRIMARY]
GO
-- Insert data from working table, delete existing contents first
TRUNCATE TABLE dbo.additional_data;
INSERT INTO dbo.additional_data
(parcel_id, living_area, gross_area, year_built, year_remodeled, condo_units_residential, condo_units_commercial, condo_units_mixed, stories)
SELECT [parcel_id], ROUND([Living Area] , 0), ROUND([Gross Area], 0), [Year Built], [Year Remodel], 0, [Commercial Units], 0, CONVERT(numeric(4,1), ISNULL(NULLIF([Story Height],''), 0))
FROM dbo._ADDITIONAL_DATA;
bid
This table is accessed from default.asp.
Potentially, there is some adjustment of the bid amount depending on billing schedule.
Column
Source
Notes
parcel_id nvarchar(10)
PK The unique parcel ID
bid_greenway money
MS Access
This is extracted and compiled from the greenway_bid table in the MSAccess database.
bid_downtown money
MS Access
This is extracted and compiled from the greenway_bid table in the MSAccess database.
Check the tables in the MS Access database to see if new BID region/categories have been created. If the unlikely event there are, then check with Assessing Dept to see if the info needs to be displayed.
If so, then this table will need an additional column for the new bid region/category, and the HTML in default.asp will need to be updated.
USE assessingupdates2023Q3;
-- Create the table.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP TABLE IF EXISTS dbo.bid;
GO
CREATE TABLE [dbo].[bid](
[parcel_id] [nvarchar](10) NOT NULL,
[bid_greenway] [money] NOT NULL,
[bid_downtown] [money] NOT NULL,
[bid_newmarket] [money] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-013148] ON [dbo].[bid]
([parcel_id] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
-- Insert the data
TRUNCATE TABLE dbo.bid;
INSERT INTO dbo.bid (parcel_id, bid_greenway, bid_newmarket, bid_downtown)
SELECT parcel.parcel_id, ISNULL(greenway.[Unpaid Balance],0) AS Greenway_BID, ISNULL(newmarket.[Unpaid Balance],0) as Newmarket_BID, ISNULL(downtown.[Unpaid Balance], 0) as Downtown_BID
FROM dbo._Taxes AS parcel
LEFT JOIN dbo._Greenway_BID AS greenway ON parcel.parcel_id = greenway.parcel_id
LEFT JOIN dbo._Newmarket_BID AS newmarket ON parcel.parcel_id = newmarket.parcel_id
LEFT JOIN dbo._Downtown_BID AS downtown ON parcel.parcel_id = downtown.parcel_id
WHERE ISNULL(greenway.[Unpaid Balance], 0) + ISNULL(newmarket.[Unpaid Balance], 0) + ISNULL(downtown.[Unpaid Balance], 0) <> 0
condo_attributes
This table is accessed from default.asp.
Column
Source
Notes
parcel_id nchar(10)
Style nvarchar(20)
Exterior Condition nvarchar(14)
Exterior Wall nvarchar(14)
Grade nvarchar(14)
Stories nvarchar(4)
'#Units' decimal(9, 0)
Street Type nvarchar(50)
Square Feet of Living area decimal(9, 0)
Base Floor nvarchar(4)
Fireplaces nvarchar(4)
'# Floors' nvarchar(4)
Total Rooms nvarchar(4)
Bedrooms nvarchar(4)
Bathrooms nvarchar(4)
Half Bathrooms nvarchar(4)
Bathroom Style1 nvarchar(14)
Bathroom Style2 nvarchar(14)
Bathroom Style3 nvarchar(14)
Kitchen Style nvarchar(14)
Kitchen Type nvarchar(14)
Heat Type nvarchar(14)
Interior Condition nvarchar(14)
Interior Finish nvarchar(14)
Orientation nvarchar(14)
Corner Unit nvarchar(14)
View nvarchar(14)
condo_main nchar(10)
CNS_BID decimal(9, 0)
USE assessingupdates2023Q3;
-- Create the table.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP TABLE IF EXISTS [dbo].[condo_attributes];
GO
CREATE TABLE [dbo].[condo_attributes](
[parcel_id] [nvarchar](10) NULL,
[Composite Land Use] [nvarchar](255) NULL,
[Orientation] [nvarchar](255) NULL,
[Corner Unit] [nvarchar](255) NULL,
[Floor] [nvarchar](255) NULL,
[Rooms] [nvarchar](255) NULL,
[Bedrooms] [nvarchar](255) NULL,
[Bedroom Type] [nvarchar](255) NULL,
[Full Bath] [float] NULL,
[Half Bath] [float] NULL,
[Other Fixtures] [float] NULL,
[Bath Style 1] [nvarchar](255) NULL,
[Bath Style 2] [nvarchar](255) NULL,
[Bath Style 3] [nvarchar](255) NULL,
[Kitchens] [float] NULL,
[Kitchen Type] [nvarchar](255) NULL,
[Kitchen Style 1] [nvarchar](255) NULL,
[Kitchen Style 2] [nvarchar](255) NULL,
[Kitchen Style 3] [nvarchar](255) NULL,
[Fireplaces] [float] NULL,
[Penthouse Unit] [bit] NOT NULL,
[AC Type] [nvarchar](255) NULL,
[Heat Type] [nvarchar](255) NULL,
[Year Built] [float] NULL,
[Interior Condition] [nvarchar](255) NULL,
[Interior Finish] [nvarchar](255) NULL,
[View] [nvarchar](255) NULL,
[Grade] [nvarchar](255) NULL,
[# of Parking Spots] [nvarchar](255) NULL,
[Parking Ownership] [nvarchar](255) NULL,
[Parking Type] [nvarchar](255) NULL,
[Tandem Parking] [nvarchar](255) NULL,
[Complex] [nvarchar](255) NULL,
[Story Height] [nvarchar](255) NULL,
[Roof Cover] [nvarchar](255) NULL,
[Roof Structure] [nvarchar](255) NULL,
[Exterior Condition] [nvarchar](255) NULL,
[Exterior Finish] [nvarchar](255) NULL,
[Building Style] [nvarchar](255) NULL,
[Foundation] [nvarchar](255) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-192043] ON [dbo].[condo_attributes]
([parcel_id] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
-- Insert data from working table, delete existing contents first
TRUNCATE TABLE dbo.condo_attributes;
GO
INSERT INTO dbo.condo_attributes
( [parcel_id], [Composite Land Use], [Orientation], [Corner Unit], [Floor], [Rooms]
,[Bedrooms], [Bedroom Type], [Full Bath], [Half Bath], [Other Fixtures], [Bath Style 1]
,[Bath Style 2], [Bath Style 3], [Kitchens], [Kitchen Type], [Kitchen Style 1], [Kitchen Style 2]
,[Kitchen Style 3], [Fireplaces], [Penthouse Unit], [AC Type], [Heat Type], [Year Built]
,[Interior Condition], [Interior Finish], [View], [Grade], [# of Parking Spots], [Parking Ownership]
,[Parking Type], [Tandem Parking], [Complex], [Story Height], [Roof Cover], [Roof Structure]
,[Exterior Condition], [Exterior Finish], [Building Style], [Foundation])
SELECT
[parcel_id], [Composite Land Use], [Orientation], [Corner Unit], [Floor], [Rooms]
,[Bedrooms], [Bedroom Type], [Full Bath], [Half Bath], [Other Fixtures], [Bath Style 1]
,[Bath Style 2], [Bath Style 3], [Kitchens], [Kitchen Type], [Kitchen Style 1], [Kitchen Style 2]
,[Kitchen Style 3], [Fireplaces], [Penthouse Unit], [AC Type], [Heat Type], [Year Built]
,[Interior Condition], [Interior Finish], [View], [Grade], [# of Parking Spots], [Parking Ownership]
,[Parking Type], [Tandem Parking], [Complex], [Story Height], [Roof Cover], [Roof Structure]
,[Exterior Condition], [Exterior Finish], [Building Style], [Foundation]
FROM dbo._CONDO_PROPERTY_ATTRIBUTEScurrent_owners
This table is accessed from default.asp.
Column
Source
Notes
Parcel_id nchar(10)
PK
seqno tinyint
PK
owner_name nvarchar(255)
It seems that this table only contains owners with the seqno >= 1, so the primary current owners.
2022 year only included seqno = 1, but the code looks like it will be OK with seqno > 1
USE assessingupdates2023Q3;
-- Create the table.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP TABLE IF EXISTS dbo.current_owners;
GO
CREATE TABLE [dbo].[current_owners](
[Parcel_id] [nchar](10) NOT NULL,
[seqno] [tinyint] NULL,
[owner_name] [nvarchar](255) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE CLUSTERED INDEX [ClusteredIndex-20190701-115106] ON [dbo].[current_owners]
([Parcel_id] ASC, [seqno] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
-- Insert the data
TRUNCATE TABLE dbo.current_owners;
GO
INSERT INTO dbo.current_owners ([Parcel_id], [seqno], [owner_name])
SELECT [parcel_id], [seqno], [owner_name]
FROM dbo._CURRENT_OWNERS
WHERE seqno >= 1
landuse_described
This table is accessed from default.asp.
Column
Source
Notes
id int
Short_Description nvarchar(10)
Description nvarchar(50)
This data should be unchanged between years, but double check with Assessing Team to be sure.
Nothing to do, just be sure the table exists from the previous year
USE assessingupdates2023Q3;
-- Create the table.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP TABLE IF EXISTS dbo.Landuse_Described
GO
CREATE TABLE [dbo].[Landuse_Described](
[id] [int] NOT NULL,
[Short_Description] [nvarchar](10) NOT NULL,
[Description] [nvarchar](50) NULL
) ON [PRIMARY]
GO
-- Insert the metadata
TRUNCATE TABLE dbo.Landuse_Described;
GO
INSERT INTO dbo.Landuse_Described ([id], [Short_Description], [Description])
VALUES (1, 'A', 'Apartment Building')
,(2, 'AH', 'Agricultural')
,(3, 'C', 'Commercial')
,(4, 'CC', 'Commercial Condo Unit')
,(5, 'CD', 'Residential Condo Unit')
,(6, 'CL', 'Commercial Land')
,(7, 'CM', 'Condo Main Building')
,(8, 'CP', 'Condo Parking')
,(9, 'E', 'Exempt')
,(10, 'EA', 'Exempt - 121A')
,(11, 'I', 'Industrial')
,(12, 'R1', 'One Family')
,(13, 'R2', 'Two Family')
,(14, 'R3', 'Three Family')
,(15, 'R4', 'Four to Six Family')
,(16, 'RC', 'Residential/Commercial')
,(17, 'RL', 'Residential Land')
outbuildings
This table is accessed from default.asp.
Column
Source
Notes
parcel_id nchar(10)
PK
line_number tinyint
PK
Code nvarchar(50)
Tot Units decimal(9, 2)
Quantity decimal(9, 2)
Quality nvarchar(50)
Condition nvarchar(50)
USE assessingupdates2023Q3;
-- Create the table.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP TABLE IF EXISTS dbo.outbuildings
GO
CREATE TABLE [dbo].[outbuildings](
[parcel_id] [nchar](10) NOT NULL,
[line_number] [tinyint] NOT NULL,
[Code] [nvarchar](255) NOT NULL,
[Tot Units] [decimal](9, 2) NOT NULL,
[Quality] [nvarchar](50) NOT NULL,
[Condition] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-135523] ON [dbo].[outbuildings]
([parcel_id] ASC, [line_number] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
-- Insert data from working table, delete existing contents first
TRUNCATE TABLE dbo.condo_attributes;
GO
INSERT INTO [dbo].[outbuildings] ([parcel_id],[line_number],[Code],[Tot Units],[Quality],[Condition])
SELECT [parcel_id]
,ROW_NUMBER() OVER (
PARTITION BY parcel_id
order by parcel_id
) AS line_number
,[Code]
,ISNULL([Tot Units], 0)
,ISNULL([Quality], ' ')
,ISNULL([Condition], ' ')
FROM [dbo].[_SPECIAL_FEATURES]Overval_application_numbers
This table does not appear to be used.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[overval_application_numbers](
[id] [int] NOT NULL,
[parcel_id] [nchar](10) NOT NULL
) ON [PRIMARY]
GOparcel
This table is accessed from default.asp.
Column
Source
Notes
parcel_id nchar(10)
PK
street_number nvarchar(10)
street_name nvarchar(50)
apartment_no nvarchar(20)
suffix nvarchar(2)
landuse nvarchar(2)
owner nvarchar(255)
condo_main nchar(10)
USE assessingupdates2023Q3;
-- Create the table.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP TABLE IF EXISTS dbo.parcel;
GO
CREATE TABLE [dbo].[parcel](
[parcel_id] [nchar](10) NOT NULL,
[street_number] [nvarchar](10) NULL,
[street_name] [nvarchar](50) NULL,
[apartment_no] [nvarchar](20) NULL,
[suffix] [nvarchar](2) NULL,
[landuse] [nvarchar](2) NULL,
[owner] [nvarchar](255) NULL,
[condo_main] [nchar](10) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-114720] ON [dbo].[parcel]
([parcel_id] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
-- Insert data from working table, delete existing contents first
TRUNCATE TABLE dbo.condo_attributes;
GO
INSERT INTO [dbo].[parcel] ([parcel_id], [street_number], [street_name],
[apartment_no], [suffix], [landuse], [owner], [condo_main])
SELECT [parcel_id], ISNULL([street_number], '') street_number, SUBSTRING(TRIM([street_name]), 1, LEN(TRIM(street_name)) - 2) street_name,
ISNULL([apt_unit], '') apartment_no, RIGHT(TRIM(street_name), 2) suffix, [land_use], [owner], [condo_main]
FROM dbo._Tyler_Real_Estate_Export_File
order by parcel_idparcel_all_owners
This table is accessed from default.asp.
Column
Source
Notes
parcel_id nchar(10)
street_number nvarchar(10)
street_name nvarchar(50)
apartment_no nvarchar(20)
suffix nvarchar(2)
landuse nvarchar(2)
owner nvarchar(255)
condo_main nchar(10)
USE assessingupdates2023Q3;
-- Create the table.SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP TABLE IF EXISTS dbo.parcel_all_owners;
GO
CREATE TABLE [dbo].[parcel_all_owners](
[parcel_id] [nchar](10) NOT NULL,
[street_number] [nvarchar](10) NULL,
[street_name] [nvarchar](50) NULL,
[apartment_no] [nvarchar](20) NULL,
[suffix] [nvarchar](2) NULL,
[landuse] [nvarchar](2) NULL,
[owner] [nvarchar](255) NULL,
[condo_main] [nchar](10) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE CLUSTERED INDEX [ClusteredIndex-114826] ON [dbo].[parcel_all_owners]
([parcel_id] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [NonClusteredIndex-115431] ON [dbo].[parcel_all_owners]
([owner] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
-- Insert data from working table, delete existing contents first
TRUNCATE TABLE dbo.parcel_all_owners;
GO
INSERT INTO [dbo].[parcel_all_owners] ([parcel_id], [street_number], [street_name], [apartment_no],
[suffix], [landuse], [owner], [condo_main])
SELECT tyler.[parcel_id], ISNULL([street_number], '') street_number, SUBSTRING(TRIM([street_name]), 1, LEN(TRIM(street_name)) - 2) street_name, ISNULL([apt_unit], '') apartment_no,
RIGHT(TRIM(street_name), 2) suffix, [land_use], owners.[owner_name], [condo_main]
FROM dbo._Tyler_Real_Estate_Export_File AS tyler
INNER JOIN dbo.current_owners as owners ON tyler.parcel_id = owners.parcel_id
order by parcel_idpropertycodes_described
This table is accessed from default.asp.
Column
Source
Notes
property-code smallint
property-class tinyint
property-class-description nvarchar(255)
property-code-description nvarchar(255)
property-code-state bit
property-code-city bit
This data should be unchanged between years, but double check with Assessing Team to be sure.This data should be unchanged between years, but double check with Assessing Team to be sure.
USE assessingupdates2023Q3;
-- Create the table.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP TABLE IF EXISTS assessingupdates2023Q3.dbo.propertycodes_described
GO
CREATE TABLE [assessingupdates2023Q3].[dbo].[propertycodes_described](
[property-code] [smallint] NOT NULL,
[property-class] [tinyint] NOT NULL,
[property-class-description] [nvarchar](255) NOT NULL,
[property-code-description] [nvarchar](255) NOT NULL,
[property-code-state] [bit] NOT NULL,
[property-code-city] [bit] NOT NULL
) ON [PRIMARY]
GO
-- Insert data from working table, delete existing contents first
TRUNCATE TABLE [assessingupdates2023Q3].[dbo].[propertycodes_described]
GO
INSERT INTO [assessingupdates2023Q3].[dbo].[propertycodes_described]
([property-code], [property-class], [property-class-description], [property-code-description]
,[property-code-state], [property-code-city])
SELECT [property-code], [property-class], [property-class-description], [property-code-description]
,[property-code-state], [property-code-city]
FROM [assessingsearch].[dbo].[propertycodes_described]Res_exempt
This table is accessed from default.asp.
Column
Source
Notes
parcel_id nvarchar(10)
MSAccess:Tyler Real Estate Export File
residential_exemption nvarchar(255)
MSAccess:Tyler Real Estate Export File
personal_exemption bit
MSAccess:Taxes
Defaults to 0 but is 1 if either of Personal Ex Type 1 or Personal Ex Type 2 is not null.
USE assessingupdates2023Q3;
-- Create the table.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP TABLE IF EXISTS [dbo].[Res_exempt]
GO
CREATE TABLE [dbo].[Res_exempt](
[parcel_id] [nvarchar](10) NULL,
[residential_exemption] [nvarchar](1) NULL,
[personal_exemption] [bit] NOT NULL
) ON [PRIMARY]
GO
-- Insert data from working table, delete existing contents first
TRUNCATE TABLE [dbo].[Res_exempt]
GO
INSERT INTO [dbo].[Res_exempt]
([parcel_id], [residential_exemption], [personal_exemption])
SELECT tyler.parcel_id, IIF(ISNULL(tyler.residential_exemption_flag, 'N') = 'Y', 'Y', 'N') as Residential, IIF(ISNULL(taxes.[Personal Ex Type 1], '0') + ISNULL(taxes.[Personal Ex Type 2], '0') = '00', 0, 1) AS personal
FROM _Tyler_Real_Estate_Export_File tyler
INNER JOIN _Taxes taxes ON tyler.parcel_id = taxes.parcel_idRESIDENTIAL PROPERTY ATTRIBUTES
This table is accessed from default.asp.
Column
Source
Notes
parcel_id nvarchar(255)
Building Seq float
Composite Land Use nvarchar(255)
Building Style nvarchar(255)
Rooms nvarchar(255)
Bedrooms nvarchar(255)
Full Bath float
Half Bath float
Other Fixtures float
Bath Style 1 nvarchar(255)
Bath Style 2 nvarchar(255)
Bath Style 3 nvarchar(255)
Kitchens float
Kitchen Type nvarchar(255)
Kitchen Style 1 nvarchar(255)
Kitchen Style 2 nvarchar(255)
Kitchen Style 3 nvarchar(255)
Fireplaces float
AC Type nvarchar(255)
Heat Type nvarchar(255)
Interior Condition nvarchar(255)
Interior Finish nvarchar(255)
View nvarchar(255)
Grade nvarchar(255)
Year Built float
Story Height nvarchar(255)
Roof Cover nvarchar(255)
Roof Structure nvarchar(255)
Exterior Finish nvarchar(255)
Exterior Condition nvarchar(255)
Foundation nvarchar(255)
USE assessingupdates2023Q3;
-- Create the table.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP TABLE IF EXISTS [dbo].[RESIDENTIAL PROPERTY ATTRIBUTES]
GO
CREATE TABLE [dbo].[RESIDENTIAL PROPERTY ATTRIBUTES](
[parcel_id] [nvarchar](10) NULL,
[line_number] [nvarchar](255) NULL,
[Composite Land Use] [nvarchar](255) NULL,
[Building Style] [nvarchar](255) NULL,
[Rooms] [nvarchar](255) NULL,
[Bedrooms] [nvarchar](255) NULL,
[Full Bath] [float] NULL,
[Half Bath] [float] NULL,
[Other Fixtures] [float] NULL,
[Bath Style 1] [nvarchar](255) NULL,
[Bath Style 2] [nvarchar](255) NULL,
[Bath Style 3] [nvarchar](255) NULL,
[Kitchens] [float] NULL,
[Kitchen Type] [nvarchar](255) NULL,
[Kitchen Style 1] [nvarchar](255) NULL,
[Kitchen Style 2] [nvarchar](255) NULL,
[Kitchen Style 3] [nvarchar](255) NULL,
[Fireplaces] [float] NULL,
[AC Type] [nvarchar](255) NULL,
[Heat Type] [nvarchar](255) NULL,
[Interior Condition] [nvarchar](255) NULL,
[Interior Finish] [nvarchar](255) NULL,
[View] [nvarchar](255) NULL,
[Grade] [nvarchar](255) NULL,
[# of Parking Spots] [nvarchar](255) NULL,
[Year Built] [float] NULL,
[Story Height] [nvarchar](255) NULL,
[Roof Cover] [nvarchar](255) NULL,
[Roof Structure] [nvarchar](255) NULL,
[Exterior Finish] [nvarchar](255) NULL,
[Exterior Condition] [nvarchar](255) NULL,
[Foundation] [nvarchar](255) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-135336] ON [dbo].[RESIDENTIAL PROPERTY ATTRIBUTES]
([parcel_id] ASC, [line_number] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
-- Insert data from working table, delete existing contents first
TRUNCATE TABLE [dbo].[RESIDENTIAL PROPERTY ATTRIBUTES]
GO
INSERT INTO dbo.[RESIDENTIAL PROPERTY ATTRIBUTES]
([parcel_id], [line_number], [Composite Land Use], [Building Style], [Rooms]
,[Bedrooms], [Full Bath], [Half Bath], [Other Fixtures], [Bath Style 1]
,[Bath Style 2], [Bath Style 3], [Kitchens], [Kitchen Type], [Kitchen Style 1]
,[Kitchen Style 2], [Kitchen Style 3], [Fireplaces], [AC Type], [Heat Type]
,[Interior Condition], [Interior Finish], [View], [Grade]
,[# of Parking Spots], [Year Built], [Story Height], [Roof Cover], [Roof Structure]
,[Exterior Condition], [Exterior Finish], [Foundation])
SELECT [parcel_id], [line_number], [Composite Land Use], [Building Style], [Rooms]
,[Bedrooms], [Full Bath], [Half Bath], [Other Fixtures], [Bath Style 1]
,[Bath Style 2], [Bath Style 3], [Kitchens], [Kitchen Type], [Kitchen Style 1]
,[Kitchen Style 2], [Kitchen Style 3], [Fireplaces], [AC Type], [Heat Type]
,[Interior Condition], [Interior Finish], [View], [Grade]
,[# of Parking Spots], [Year Built], [Story Height], [Roof Cover], [Roof Structure]
,[Exterior Condition], [Exterior Finish], [Foundation]
FROM [assessingupdates2023Q3].[dbo].[_RESIDENTIAL_PROPERTY_ATTRIBUTES]tax_preliminary
This table is accessed from default.asp.
Column
Source
Notes
parcel_id nchar(10)
Bill Year smallint
Bill Number int
RE Tax Amt decimal(12, 2)
CPA Amt decimal(12, 2)
Downtown BID Amt decimal(12, 2)
Greenway BID Amt decimal(12, 2)
Total Billed Amt decimal(12, 2)
USE assessingupdates2023Q3;
-- Create the table.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP TABLE IF EXISTS [dbo].[tax_preliminary]
GO
CREATE TABLE [dbo].[tax_preliminary](
[parcel_id] [nchar](10) NOT NULL,
[Bill Year] [smallint] NULL,
[Bill Number] [int] NULL,
[RE Tax Amt] [decimal](12, 2) NULL,
[CPA Amt] [decimal](12, 2) NULL,
[Downtown BID Amt] [decimal](12, 2) NULL,
[Greenway BID Amt] [decimal](12, 2) NULL,
[Newmarket BID Amt] [decimal](12, 2) NULL,
[Total Billed Amt] [decimal](12, 2) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-012238] ON [dbo].[tax_preliminary]
([parcel_id] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
-- Insert data from working table, delete existing contents first
TRUNCATE TABLE [dbo].[tax_preliminary]
GO
INSERT INTO [dbo].[tax_preliminary]
([parcel_id], [Bill Year], [Bill Number], [RE Tax Amt], [CPA Amt]
, [Downtown BID Amt], [Greenway BID Amt], [Newmarket BID Amt], [Total Billed Amt])
SELECT taxes.parcel_id, 2023, taxes.bill_number
, CONVERT(decimal(12,2), taxes.net_tax )
, CONVERT(decimal(12,2), isnull(taxes.cpa, 0))
, CONVERT(decimal(12,2), isnull(downtown.Billed, 0) )
, CONVERT(decimal(12,2), isnull(greenway.Billed, 0) )
, CONVERT(decimal(12,2), isnull(newmarket.Billed, 0) )
, CONVERT(decimal(12,2), (taxes.net_tax + isnull(taxes.cpa, 0) + isnull(downtown.Billed, 0) + isnull(greenway.Billed, 0) + isnull(newmarket.Billed, 0)) ) AS total
FROM dbo.taxes AS taxes
LEFT OUTER JOIN dbo.[_Downtown_BID] AS downtown ON taxes.parcel_id = downtown.parcel_id
LEFT OUTER JOIN dbo.[_Greenway_BID] AS greenway ON taxes.parcel_id = greenway.parcel_id
LEFT OUTER JOIN dbo.[_Newmarket_BID] AS newmarket ON taxes.parcel_id = newmarket.parcel_id
taxbill
This table is accessed from default.asp.
Column
Source
Notes
parcel_id nchar(10)
condo_main nchar(10)
ward_precinct_block nvarchar(7)
street_number nvarchar(10)
street_number_suffix nvarchar(10)
street_name nvarchar(50)
street_name_only nvarchar(50)
street_name_suffix nvarchar(2)
apt_unit nvarchar(20)
city nvarchar(50)
location_zip_code nvarchar(5)
location_zip_code_plus_four nvarchar(4)
owner nvarchar(50)
mail_addressee nvarchar(50)
mail_street_address1 nvarchar(50)
mail_street_address2 nvarchar(50)
mail_city_and_state nvarchar(50)
mail_zip_code nvarchar(5)
mail_zip_code_plus_four nvarchar(4)
country nvarchar(50)
future_owner nvarchar(50)
future_mail_addressee nvarchar(50)
future_owner_mail_street_address1 nvarchar(50)
future_owner_mail_street_address2 nvarchar(50)
future_owner_mail_city_and_state nvarchar(50)
future_owner_mail_zip_code nvarchar(5)
future_owner_mail_zip_code_plus_four nvarchar(4)
future_owner_country nvarchar(50)
land_area int
land_use nvarchar(2)
exempt_code nvarchar(4)
property_type nvarchar(4)
state_class_code nvarchar(1)
residential_building_value decimal(12, 2) residential_land_value decimal(12, 2) residential_air_rights_value decimal(12, 2)
commercial_building_value decimal(12, 2) commercial_land_value decimal(12, 2) commercial_air_rights_value decimal(12, 2)
industrial_building_value decimal(12, 2) industrial_land_value decimal(12, 2) industrial_air_rights_value decimal(12, 2)
open_space_land_value decimal(12, 2)
exempt_building_value decimal(12, 2) exempt_land_value decimal(12, 2)
total_building_value decimal(12, 2) total_land_value decimal(12, 2) total_value decimal(12, 2)
latest_sale_date date
latest_bkpgcert nvarchar(10)
residential_exemption_flag bit
coop_value decimal(12, 2)
clause_abatement_type_1 nvarchar(6) clause_abt_1_pct_ownership nvarchar(5) clause_abt_1_pct_occupancy nvarchar(5) clause_abatement_type_2 nvarchar(6) clause_abt_2_pct_ownership nvarchar(5) clause_abt_2_pct_occupancy nvarchar(5)
paraplegic nvarchar(6)
workoff_type nvarchar(6) workoff_credit_amt decimal(12, 2)
sewer_betterment_amt decimal(12, 2) street_betterment_amt decimal(12, 2) sidewalk_betterment_amt decimal(12, 2)
penalty1_type nvarchar(6) penalty1_amt decimal(12, 2) penalty2_type nvarchar(6) penalty2_amt decimal(12, 2) penalty3_type nvarchar(6) penalty3_amt decimal(12, 2)
fine1_type nvarchar(6) fine1_amt decimal(12, 2) fine2_type nvarchar(6) fine2_amt decimal(12, 2) fine3_type nvarchar(6) fine3_amt decimal(12, 2)
BID_type nvarchar(6) BID_amt decimal(12, 2)
nontax_collection_type nvarchar(6) nontax_collection_amt decimal(12, 2)
personal_exemption_flag bit
condo_main_value decimal(12, 2)
USE assessingupdates2023Q3;
-- Create the table.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP TABLE IF EXISTS [dbo].[taxbill]
GO
CREATE TABLE [dbo].[taxbill](
[parcel_id] [nchar](10) NOT NULL,
[condo_main] [nchar](10) NULL,
[ward_precinct_block] [nvarchar](7) NOT NULL,
[street_number] [nvarchar](10) NOT NULL,
[street_number_suffix] [nvarchar](10) NOT NULL,
[street_name] [nvarchar](50) NOT NULL,
[street_name_only] [nvarchar](50) NOT NULL,
[street_name_suffix] [nvarchar](2) NOT NULL,
[apt_unit] [nvarchar](20) NOT NULL,
[city] [nvarchar](50) NOT NULL,
[location_zip_code] [nvarchar](5) NOT NULL,
[location_zip_code_plus_four] [nvarchar](4) NULL,
[owner] [nvarchar](50) NOT NULL,
[mail_addressee] [nvarchar](50) NOT NULL,
[mail_street_address1] [nvarchar](50) NOT NULL,
[mail_street_address2] [nvarchar](50) NOT NULL,
[mail_city_and_state] [nvarchar](50) NOT NULL,
[mail_zip_code] [nvarchar](5) NOT NULL,
[mail_zip_code_plus_four] [nvarchar](4) NOT NULL,
[country] [nvarchar](50) NOT NULL,
[future_owner] [nvarchar](50) NOT NULL,
[future_mail_addressee] [nvarchar](50) NOT NULL,
[future_owner_mail_street_address1] [nvarchar](50) NOT NULL,
[future_owner_mail_street_address2] [nvarchar](50) NOT NULL,
[future_owner_mail_city_and_state] [nvarchar](50) NOT NULL,
[future_owner_mail_zip_code] [nvarchar](5) NOT NULL,
[future_owner_mail_zip_code_plus_four] [nvarchar](4) NOT NULL,
[future_owner_country] [nvarchar](50) NOT NULL,
[land_area] [int] NULL,
[land_use] [nvarchar](2) NOT NULL,
[exempt_code] [nvarchar](4) NOT NULL,
[property_type] [nvarchar](4) NOT NULL,
[state_class_code] [nvarchar](1) NOT NULL,
[residential_building_value] [decimal](12, 2) NOT NULL,
[residential_land_value] [decimal](12, 2) NOT NULL,
[residential_air_rights_value] [decimal](12, 2) NULL,
[commercial_building_value] [decimal](12, 2) NOT NULL,
[commercial_land_value] [decimal](12, 2) NOT NULL,
[commercial_air_rights_value] [decimal](12, 2) NULL,
[industrial_building_value] [decimal](12, 2) NOT NULL,
[industrial_land_value] [decimal](12, 2) NOT NULL,
[industrial_air_rights_value] [decimal](12, 2) NULL,
[open_space_land_value] [decimal](12, 2) NULL,
[exempt_building_value] [decimal](12, 2) NOT NULL,
[exempt_land_value] [decimal](12, 2) NOT NULL,
[total_building_value] [decimal](12, 2) NOT NULL,
[total_land_value] [decimal](12, 2) NOT NULL,
[total_value] [decimal](12, 2) NOT NULL,
[latest_sale_date] [date] NULL,
[latest_bkpgcert] [nvarchar](10) NOT NULL,
[residential_exemption_flag] [bit] NOT NULL,
[coop_value] [decimal](12, 2) NOT NULL,
[clause_abatement_type_1] [nvarchar](6) NOT NULL,
[clause_abt_1_pct_ownership] [nvarchar](5) NOT NULL,
[clause_abt_1_pct_occupancy] [nvarchar](5) NOT NULL,
[clause_abatement_type_2] [nvarchar](6) NOT NULL,
[clause_abt_2_pct_ownership] [nvarchar](5) NOT NULL,
[clause_abt_2_pct_occupancy] [nvarchar](5) NOT NULL,
[paraplegic] [nvarchar](6) NOT NULL,
[workoff_type] [nvarchar](6) NOT NULL,
[workoff_credit_amt] [decimal](12, 2) NOT NULL,
[sewer_betterment_amt] [decimal](12, 2) NOT NULL,
[street_betterment_amt] [decimal](12, 2) NOT NULL,
[sidewalk_betterment_amt] [decimal](12, 2) NOT NULL,
[penalty1_type] [nvarchar](6) NOT NULL,
[penalty1_amt] [decimal](12, 2) NOT NULL,
[penalty2_type] [nvarchar](6) NOT NULL,
[penalty2_amt] [decimal](12, 2) NOT NULL,
[penalty3_type] [nvarchar](6) NOT NULL,
[penalty3_amt] [decimal](12, 2) NOT NULL,
[fine1_type] [nvarchar](6) NOT NULL,
[fine1_amt] [decimal](12, 2) NOT NULL,
[fine2_type] [nvarchar](6) NOT NULL,
[fine2_amt] [decimal](12, 2) NOT NULL,
[fine3_type] [nvarchar](6) NOT NULL,
[fine3_amt] [decimal](12, 2) NOT NULL,
[BID_type] [nvarchar](6) NOT NULL,
[BID_amt] [decimal](12, 2) NOT NULL,
[nontax_collection_type] [nvarchar](6) NOT NULL,
[nontax_collection_amt] [decimal](12, 2) NULL,
[personal_exemption_flag] [bit] NOT NULL,
[condo_main_value] [decimal](12, 2) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-133903] ON [dbo].[taxbill]
([parcel_id] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_ward_precinct_block] DEFAULT (N'=') FOR [ward_precinct_block]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_street_number] DEFAULT (N'=') FOR [street_number]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_street_number_suffix] DEFAULT (N'=') FOR [street_number_suffix]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_street_name] DEFAULT (N'=') FOR [street_name]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_street_name_only] DEFAULT (N'=') FOR [street_name_only]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_street_name_suffix] DEFAULT (N'=') FOR [street_name_suffix]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_apt_unit] DEFAULT (N'=') FOR [apt_unit]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_city] DEFAULT (N'=') FOR [city]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_location_zip_code] DEFAULT (N'=') FOR [location_zip_code]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_owner] DEFAULT (N'=') FOR [owner]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_mail_addressee] DEFAULT (N'=') FOR [mail_addressee]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_mail_street_address1] DEFAULT (N'=') FOR [mail_street_address1]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_mail_street_address2] DEFAULT (N'=') FOR [mail_street_address2]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_mail_city_and_state] DEFAULT (N'=') FOR [mail_city_and_state]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_mail_zip_code] DEFAULT (N'=') FOR [mail_zip_code]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_mail_zip_code_plus_four] DEFAULT (N'=') FOR [mail_zip_code_plus_four]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_country] DEFAULT (N'=') FOR [country]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_future_owner] DEFAULT (N'=') FOR [future_owner]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_future_mail_addressee] DEFAULT (N'=') FOR [future_mail_addressee]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_future_owner_mail_street_address1] DEFAULT (N'=') FOR [future_owner_mail_street_address1]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_future_owner_mail_street_address2] DEFAULT (N'=') FOR [future_owner_mail_street_address2]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_future_owner_mail_city_and_state] DEFAULT (N'=') FOR [future_owner_mail_city_and_state]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_future_owner_mail_zip_code] DEFAULT (N'=') FOR [future_owner_mail_zip_code]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_future_owner_mail_zip_code_plus_four] DEFAULT (N'=') FOR [future_owner_mail_zip_code_plus_four]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_future_owner_country] DEFAULT (N'=') FOR [future_owner_country]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_land_area] DEFAULT ((0)) FOR [land_area]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_land_use] DEFAULT (N'=') FOR [land_use]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_exempt_code] DEFAULT (N'=') FOR [exempt_code]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_property_type] DEFAULT (N'=') FOR [property_type]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_state_class_code] DEFAULT (N'=') FOR [state_class_code]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_residential_building_value] DEFAULT ((0)) FOR [residential_building_value]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_residential_land_value] DEFAULT ((0)) FOR [residential_land_value]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_residential_air_rights_value] DEFAULT ((0)) FOR [residential_air_rights_value]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_commercial_building_value] DEFAULT ((0)) FOR [commercial_building_value]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_commercial_land_value] DEFAULT ((0)) FOR [commercial_land_value]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_commercial_air_rights_value] DEFAULT ((0)) FOR [commercial_air_rights_value]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_industrial_building_value] DEFAULT ((0)) FOR [industrial_building_value]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_industrial_land_value] DEFAULT ((0)) FOR [industrial_land_value]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_industrial_air_rights_value] DEFAULT ((0)) FOR [industrial_air_rights_value]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_open_space_land_value] DEFAULT ((0)) FOR [open_space_land_value]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_exempt_building_value] DEFAULT ((0)) FOR [exempt_building_value]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_exempt_land_value] DEFAULT ((0)) FOR [exempt_land_value]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_total_building_value] DEFAULT ((0)) FOR [total_building_value]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_total_land_value] DEFAULT ((0)) FOR [total_land_value]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_total_value] DEFAULT ((0)) FOR [total_value]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_latest_bkpgcert] DEFAULT (N'=') FOR [latest_bkpgcert]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_residential_exemption_flag] DEFAULT ((0)) FOR [residential_exemption_flag]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_coop_value] DEFAULT ((0)) FOR [coop_value]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_clause_abatement_type_1] DEFAULT (N'=') FOR [clause_abatement_type_1]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_clause_abt_1_pct_ownership] DEFAULT (N'=') FOR [clause_abt_1_pct_ownership]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_clause_abt_1_pct_occupancy] DEFAULT (N'=') FOR [clause_abt_1_pct_occupancy]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_clause_abatement_type_2] DEFAULT (N'=') FOR [clause_abatement_type_2]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_clause_abt_2_pct_ownership] DEFAULT (N'=') FOR [clause_abt_2_pct_ownership]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_clause_abt_2_pct_occupancy] DEFAULT (N'=') FOR [clause_abt_2_pct_occupancy]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_paraplegic] DEFAULT (N'=') FOR [paraplegic]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_workoff_type] DEFAULT (N'=') FOR [workoff_type]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_workoff_credit_amt] DEFAULT ((0)) FOR [workoff_credit_amt]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_sewer_betterment_amt] DEFAULT ((0)) FOR [sewer_betterment_amt]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_street_betterment_amt] DEFAULT ((0)) FOR [street_betterment_amt]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_sidewalk_betterment_amt] DEFAULT ((0)) FOR [sidewalk_betterment_amt]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_penalty1_type] DEFAULT (N'=') FOR [penalty1_type]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_penalty1_amt] DEFAULT ((0)) FOR [penalty1_amt]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_penalty2_type] DEFAULT (N'=') FOR [penalty2_type]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_penalty2_amt] DEFAULT ((0)) FOR [penalty2_amt]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_penalty3_type] DEFAULT (N'=') FOR [penalty3_type]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_penalty3_amt] DEFAULT ((0)) FOR [penalty3_amt]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_fine1_type] DEFAULT (N'=') FOR [fine1_type]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_fine1_amt] DEFAULT ((0)) FOR [fine1_amt]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_fine2_type] DEFAULT ((0)) FOR [fine2_type]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_fine2_amt] DEFAULT ((0)) FOR [fine2_amt]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_fine3_type] DEFAULT (N'=') FOR [fine3_type]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_fine3_amt] DEFAULT ((0)) FOR [fine3_amt]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_BID_type] DEFAULT (N'=') FOR [BID_type]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_BID_amt] DEFAULT ((0)) FOR [BID_amt]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_nontax_collection_type] DEFAULT (N'=') FOR [nontax_collection_type]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_nontax_collection_amt] DEFAULT ((0)) FOR [nontax_collection_amt]
GO
ALTER TABLE [dbo].[taxbill] ADD CONSTRAINT [DF_taxbill_personal_exemption_flag] DEFAULT ((0)) FOR [personal_exemption_flag]
GO
-- Insert data from working table, delete existing contents first
TRUNCATE TABLE [dbo].[taxbill]
GO
INSERT INTO [dbo].[taxbill]
([parcel_id], [condo_main], [ward_precinct_block], [street_number], [street_number_suffix]
, [street_name], [street_name_only], [street_name_suffix], [apt_unit], [city]
, [location_zip_code], [location_zip_code_plus_four], [owner], [mail_addressee], [mail_street_address1]
, [mail_street_address2], [mail_city_and_state], [mail_zip_code], [mail_zip_code_plus_four], [country]
, [future_owner], [future_mail_addressee], [future_owner_mail_street_address1], [future_owner_mail_street_address2], [future_owner_mail_city_and_state]
, [future_owner_mail_zip_code], [future_owner_mail_zip_code_plus_four], [future_owner_country], [land_area], [land_use]
, [exempt_code], [property_type], [state_class_code], [residential_building_value], [residential_land_value]
, [residential_air_rights_value], [commercial_building_value], [commercial_land_value], [commercial_air_rights_value], [industrial_building_value]
, [industrial_land_value], [industrial_air_rights_value], [open_space_land_value], [exempt_building_value], [exempt_land_value]
, [total_building_value], [total_land_value], [total_value], [latest_sale_date], [latest_bkpgcert]
, [residential_exemption_flag], [coop_value], [clause_abatement_type_1], [clause_abt_1_pct_ownership], [clause_abt_1_pct_occupancy]
, [clause_abatement_type_2], [clause_abt_2_pct_ownership], [clause_abt_2_pct_occupancy], [paraplegic], [workoff_type]
, [workoff_credit_amt], [sewer_betterment_amt], [street_betterment_amt], [sidewalk_betterment_amt], [penalty1_type]
, [penalty1_amt], [penalty2_type], [penalty2_amt], [penalty3_type], [penalty3_amt]
, [fine1_type], [fine1_amt], [fine2_type], [fine2_amt], [fine3_type]
, [fine3_amt], [BID_type], [BID_amt], [nontax_collection_type], [nontax_collection_amt]
, [personal_exemption_flag], [condo_main_value])
SELECT
tyler.[parcel_id], [condo_main], ISNULL([ward_precinct_block],''), ISNULL([street_number], ''), ISNULL([street_number_suffix], '')
, [street_name], SUBSTRING(TRIM([street_name]), 1, LEN(TRIM(street_name)) - 2), RIGHT(TRIM(street_name), 2), ISNULL([apt_unit], ''), ISNULL([city], '')
, ISNULL([location_zip_code], ''), ISNULL([location_zip_code_plus_four], ''), [owner], ISNULL([mail_addressee], ''), ISNULL([mail_street_address1], '')
, ISNULL([mail_street_address2], ''), ISNULL([mail_city_and_state], ''), ISNULL([mail_zip_code], ''), ISNULL([mail_zip_code_plus_four], ''), ISNULL([country], '')
, ISNULL([future_owner], ''), ISNULL([future_mail_addressee], ''), ISNULL([future_owner_mail_street_address1], ''), ISNULL([future_owner_mail_street_address2], ''), ISNULL([future_owner_mail_city_and_state], '')
, ISNULL([future_owner_mail_zip_code], ''), ISNULL([future_owner_mail_zip_code_plus_four], ''), ISNULL([future_owner_country], ''), ISNULL([land_area],0), ISNULL([land_use], '')
, ISNULL([exempt_code], ''), [property_type], [state_class_code], CONVERT(int, [residential_building_value]), CONVERT(int, [residential_land_value])
, CONVERT(int, [residential_air_rights_value]), CONVERT(int, [commercial_building_value]), CONVERT(int, [commercial_land_value]), CONVERT(int, [commercial_air_rights_value]), CONVERT(int, [industrial_building_value])
, CONVERT(int, [industrial_land_value]), CONVERT(int, [industrial_air_rights_value]), CONVERT(int, [open_space_land_value]), CONVERT(int, [exempt_building_value]), CONVERT(int, [exempt_land_value])
, 0, 0, 0
, ISNULL([latest_sale_date], ''), ISNULL([latest_bkpgcert], '')
, IIF([residential_exemption_flag] = 'Y', 1, 0), CONVERT(int, ISNULL([coop_value], 0)), ISNULL([clause_abatement_type_1], ''), ISNULL([clause_abt_1_pct_ownership], ''), ISNULL([clause_abt_1_pct_occupancy], '')
, ISNULL([clause_abatement_type_2], ''), ISNULL([clause_abt_2_pct_ownership], ''), ISNULL([clause_abt_2_pct_occupancy], ''), ISNULL([paraplegic], ''), ISNULL([workoff_type], '')
, ISNULL([workoff_credit_amt], 0), ISNULL([sewer_betterment_amt], 0), ISNULL([street_betterment_amt], 0), ISNULL([sidewalk_betterment_amt], 0), ISNULL([penalty1_type], '')
, ISNULL([penalty1_amt], 0), ISNULL([penalty2_type], ''), ISNULL([penalty2_amt],0), ISNULL([penalty3_type], ''), ISNULL([penalty3_amt],0)
, ISNULL([fine1_type], ''), ISNULL([fine1_amt],0), ISNULL([fine2_type], ''), ISNULL([fine2_amt], 0), ISNULL([fine3_type], '')
, ISNULL([fine3_amt], 0), ISNULL([BID_type],''), ISNULL([BID_amt], 0), ISNULL([nontax_collection_type], ''), ISNULL([nontax_collection_amt], 0)
, IIF(ISNULL(taxes.[Personal Ex Type 1], '0') + ISNULL(taxes.[Personal Ex Type 2], '0') = '00', 0, 1) AS personal
, 0 AS condo_main_value
FROM dbo.[_Tyler_Real_Estate_Export_File] tyler
INNER JOIN _Taxes taxes ON tyler.parcel_id = taxes.parcel_id
GO
-- Create the valuation totals
UPDATE [dbo].[taxbill]
SET
[total_building_value] = [residential_building_value]+[residential_air_rights_value]+[commercial_building_value]+[commercial_air_rights_value]+[industrial_building_value]+[industrial_air_rights_value]+[exempt_building_value],
[total_land_value] = [commercial_land_value]+[industrial_land_value]+[open_space_land_value]+[residential_land_value]+[exempt_land_value],
[total_value] = [residential_building_value]+[residential_air_rights_value]+[commercial_building_value]+[commercial_air_rights_value]+[industrial_building_value]+[industrial_air_rights_value]+[exempt_building_value]+[commercial_land_value]+[industrial_land_value]+[open_space_land_value]+[residential_land_value]+[exempt_land_value]
GO
-- Calculate condo complex valuations
UPDATE [dbo].[taxbill]
SET
[condo_main_value] = (
SELECT sum([total_value])
FROM dbo.taxbill i
WHERE i.condo_main = taxbill.parcel_id
)
WHERE taxbill.condo_main = taxbill.parcel_idtaxes
This table is accessed from default.asp.
Column
Source
Notes
parcel_id nchar(10)
PK
gross_tax decimal(12, 2)
net_tax decimal(12, 2)
persexempt_1 decimal(12, 2)
persexempt_2 decimal(12, 2)
persexempt_total decimal(12, 2)
resexempt decimal(12, 2)
cpa decimal(12, 2)
code_enforcement_tax decimal(12, 2)
38D_fine decimal(12, 2)
sidewalk_betterment decimal(12, 2)
street_betterment decimal(12, 2)
bill_number int
USE assessingupdates2023Q3;
-- Create the table.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP TABLE IF EXISTS [dbo].[taxes]
GO
CREATE TABLE [dbo].[taxes](
[parcel_id] [nchar](10) NOT NULL,
[gross_tax] [decimal](12, 2) NOT NULL,
[net_tax] [decimal](12, 2) NOT NULL,
[persexempt_1] [decimal](12, 2) NOT NULL,
[persexempt_2] [decimal](12, 2) NOT NULL,
[persexempt_total] [decimal](12, 2) NOT NULL,
[resexempt] [decimal](12, 2) NOT NULL,
[cpa] [decimal](12, 2) NOT NULL,
[code_enforcement_tax] [decimal](12, 2) NOT NULL,
[38D_fine] [decimal](12, 2) NOT NULL,
[sidewalk_betterment] [decimal](12, 2) NOT NULL,
[street_betterment] [decimal](12, 2) NOT NULL,
[bill_number] [int] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-211258] ON [dbo].[taxes]
([parcel_id] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
ALTER TABLE [dbo].[taxes] ADD CONSTRAINT [DF_taxes_bill_number] DEFAULT ((0)) FOR [bill_number]
GO
-- Insert data from working table, delete existing contents first
TRUNCATE TABLE [dbo].[taxes]
GO
INSERT INTO [dbo].[taxes]
([parcel_id], [gross_tax]
, [net_tax]
, [persexempt_1], [persexempt_2]
, [persexempt_total]
, [resexempt], [cpa], [code_enforcement_tax], [38D_fine]
,[sidewalk_betterment], [street_betterment], [bill_number])
SELECT
[parcel_id], [Gross RE Tax]
, [Gross RE Tax] - ISNULL([Personal Ex Amt 1], 0) - ISNULL([Personal Ex Amt 2], 0) - ISNULL([Resex amt], 0) + ISNULL([CPA Tax], 0) + ISNULL([Code Enforcement Tax], 0) + ISNULL([38D Fine], 0) + ISNULL([Sidewalk Betterment], 0) + ISNULL([Street Betterment], 0)
, ISNULL([Personal Ex Amt 1], 0), ISNULL([Personal Ex Amt 2], 0)
, ISNULL([Personal Ex Amt 1], 0) + ISNULL([Personal Ex Amt 2], 0)
, ISNULL([Resex amt], 0), ISNULL([CPA Tax], 0), ISNULL([Code Enforcement Tax], 0), ISNULL([38D Fine], 0)
, ISNULL([Sidewalk Betterment], 0), ISNULL([Street Betterment], 0), [Bill Number]
FROM [dbo].[_Taxes] value_history
This table is accessed from default.asp.
Column
Source
Notes
Parcel_id nchar(10)
Fiscal_Year smallint
Assessed_value bigint
Land_use nvarchar(4)
USE assessingupdates2023Q3;
-- Create the table.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP TABLE IF EXISTS [dbo].[value_history]
GO
CREATE TABLE [dbo].[value_history](
[Parcel_id] [nchar](10) NOT NULL,
[Fiscal_Year] [smallint] NULL,
[Assessed_value] [bigint] NULL,
[Land_use] [nvarchar](4) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-115010] ON [dbo].[value_history]
([Parcel_id] ASC, [Fiscal_Year] DESC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
-- Insert data from working table, delete existing contents first
TRUNCATE TABLE [dbo].[value_history]
GO
-- Copy data from current live database
INSERT INTO [dbo].[value_history]
([Parcel_id], [Fiscal_Year], [Assessed_value], [Land_use])
SELECT [Parcel_id], [Fiscal_Year], [Assessed_value], [Land_use]
FROM [assessingsearch].[dbo].[value_history]
-- Add in the current valuations
INSERT INTO [dbo].[value_history]
([Parcel_id], [Fiscal_Year], [Assessed_value], [Land_use])
SELECT parcel_id, 2023, [total_value], [land_use]
FROM dbo.taxbillUpdating Database Tables
As noted elsewhere, data for the MS SQL databases are provided by Assessing in MS Access database/s.
The number of rows in the database tables is high (e.g. 150k - 400k records) and the data size of the tables can be high (30 - 500 MB).
This means a strategy for updating the SQL database with the source data needs to accommodate the size of the import, and the time it will take to import.
OVERVIEW
It is assumed that a new assessingupdates20YYQX database has been created:
in Q1 this should be a copy of the previous Q3 database, and
in Q3 this should be a copy of the previous Q1 database.
Extract the schema from MS Access database and use to create new import tables in the new MS SQL database (aka the target database).
Export the data from each of the source tables in MS Access as a sequence of INSERT statements in a series of files.
Import the raw data from the files into "temporary" import tables in the target database.
Manipulate the data from the "temporary" import tables into the "permanent" data tables in the target database.
STEP BY STEP
An extraction script exists (intended to run in a bash session) which extracts data from the MS Access file and uploads to the target MS SQL database.
A stored procedure exists which updates or replaces the new data with existing data in the target database.
Script Errors: (unless they've been fixed)
The script will note an error when trying to drop a table that does not exist:
Cannot drop the table xxxx, because it does not exist or you do not have permissionThis can safely be ignored.The script will likely throw a single error per file imported:
Msg 102, Level 15, State 1, Server zPDMZSQL01, Line 1 Incorrect syntax near ')'.This is because there is a blank line at the end of each file and can safely be ignored.
This script will take some time to complete. - as much as 6 hours.
There is lots of console output, so you can pipe the stdout to a file so you have a record:
. extract.sh > out.txt
Once extract.sh has completed, all of the tables (and their data) from the MS Access database will have been copied into "temporary" import tables in the target MS SQL database.
If you do not have access to an SQL editor:
In the editor, change the sp name as per check item #2 above,
Set the
@quartervariable as per check item #3 above,Change the table names as per chec item #4 above,
Save the file locally, (e.g. updatesp.sql),
In a bash session, create the SP in the target database, run: >
export SQLCMDPASSWORD=[password]>sqlcmd -S "[host]" -U "[username]" -d "[target]" -i "updatesp.sql" -Mwhere - [host] is the network location of the MSSQL server (IPAddress or DNS entry) - [username] is username -seeextract.sh -[password] is the username's password -seeextract.sh- [target] is the database name, the name of the DB created for this quarter.In a bash session, execute the stored procedure: >
export SQLCMDPASSWORD=[password]>sqlcmd -S "[host]" -U "[username]" -d "[target]" --query "exec sp_20YYQ1_update_01;" -M
After the SP completes (it will take <10 mins to run), the data update is complete and the database is ready for use by the asp pages.
Last updated
Was this helpful?