Projects

Intranet System Alerts

Training Application

Sales Route Map

Employee Database HR

Multilingual Software

Sales Report Builder

Drag & Drop Report

Online SQL Manager

Webinar Management

Really Quick Books

Auction Site

Online Employment App

Enterprise CRM

Call Center Management

SEO Management

Email Blaster App

eCommerce Site

Tourism Web Portal

Domain Reg Portal

Paperless Office

ISP


Sales Route Map Generator 2011

Sales Route Map Generator 2011 [Harmon Law, PC]



Technologies:
SQL, ASP, JavaScript, CSS and HTML

Challenge:
14 employees were out on sales routes every work day of the year. Their appointments were ever changing due to various delays and last minute changes. Planning an efficient route, that included about a half-dozen new addresses each day, was difficult to say the least.

Solution:
Create a data-driven application that would plot the most efficient sales route using Google Maps. Provide it in an email to the employee’s Smartphone, updating it whenever there were any cancellations.

I wrote a SQL Stored Proc/Job to query the sales locations for a specific employee and date (they were pre-assigned to an employee/regional rep using the ZIP code).

Once the list of sales stops was queried, it would find the closest distance to the office and the farthest distance from that point, using a ZIP code and coordinate database which I purchased. These became the start and end points. All the other addresses were tagged with a distance from the starting point. Basically, using a bubble sort, the addresses were ordered by proximity, and the coordinates and location were copied to a table of sale dates/locations.

A SSIS package would create a concatenated link to Google Maps, and then send the email to the employee.

Job done.

If there were any cancellations or rescheduled appointments, a new email would send the updated route every 15 minutes.

Project Involvement:
100%
 

 
Website © Copyright 2012-16 David Johndrow - All Rights Reserved (Individual Copyrights are Maintained by Clients as well as Trade and Service Marks)