City Maintenance

Deleting a City

If you are having trouble deleting a City in Patriot you can use the below query to identify all locations where the City is being used and remove them. You can run the query through SQL Server Management Studio or any interface that allows you to execute tSQL queries. The query requires you to define the exact name of the City and then has four queries that return different information.

The first query returns the total number of cities in the database that have the exact name that you have entered. You want this to return 1, meaning there are no other Cities with the same name. If it is greater than 1 then results from the other there queries may include entries related to other cities with the same name. To resolve this you should edit the name of the city you are trying to delete to something unused such as "TO DELETE"

The second query returns all the clients where the City is assigned and whether it is assigned to the client on the Contact Tab in the Address section or on the Billing Tab in the Postal Address section.

The third query returns all the users where the City is assigned and whether it is assigned to the user on the Address tab or the Dispatch tab.

The fourth query returns any filters the City is assigned to in the Right Activations Panel.

Declare @City varchar(60)
-- Input the Exact name of the city you wish to delete here inside the brackets.
Set @City = N'CITYNAME'

-- First Query.
select count(*)
from cities as c
where c.name like @City

-- Second Query
select c.Name, m.Client_No [Client No], m.Name [Client Name],
CASE WHEN c.IDCities = m.IDCities THEN 'YES' ELSE 'NO' END AS [Contact Tab],
CASE WHEN m.PostalCityId = c.IDCities THEN 'YES' ELSE 'NO' END AS [Billing Tab]
from cities as c join memalarm as m on m.IDCities = c.IDCities or m.PostalCityId = c.idcities
where c.name like @City order by Client_No

-- Third Query
select c.Name, u.userid [User ID], u.user_name [User Name],
CASE WHEN u.CityID = c.IDCities THEN 'YES' ELSE 'NO' END AS [User Address],
CASE WHEN dc.DispatchRank IS NOT NULL THEN 'YES' ELSE 'NO' END AS [Dispatch City]
from cities as c left join muser as u on u.CityId = c.IDCities left join dispatchcity as dc on dc.userid = u.userid and dc.idcities = c.idcities
where c.name like @City and (u.cityid = c.IDCities or dc.idcities = c.idcities) order by u.userid

-- Forth Query
select c.Name, cf.idcityfilter
from cities as c join cityfilter as cf on cf.IDCities = c.idcities
where c.name like @City