| Column Name | Type | Length | Nullable | |
| Recnum | int | No | ||
| CltID | varchar | 10 | Yes | |
| Vehicle | varchar | 4 | Yes | |
| TakeReturn | varchar | 1 | Yes | |
| Lname | varchar | 20 | Yes | |
| Fname | varchar | 10 | Yes | |
| WC-AMB | varchar | 3 | Yes | |
| Agency | varchar | 3 | Yes | |
| PTime | varchar | 5 | Yes | |
| PAddress | varchar | 50 | Yes | |
| PCity | varchar | 30 | Yes | |
| PZone | varchar | 6 | Yes | |
| PGisX | numeric | Yes | ||
| PGisY | numeric | Yes | ||
| DTime | varchar | 5 | Yes | |
| DAddress | varchar | 50 | Yes | |
| DCity | varchar | 30 | Yes | |
| DZone | varchar | 6 | Yes | |
| DGisX | numeric | Yes | ||
| DGisY | numeric | Yes | ||
| D-R | varchar | 8 | Yes | |
| DVehicle | varchar | 3 | Yes | |
| DPTime | varchar | 5 | Yes | |
| DDTime | varchar | 5 | Yes | |
| CltBoarding | numeric | Yes | ||
| Status | varchar | 1 | Yes | |
| Actual Pickup Time | varchar | 5 | Yes | |
| Actual DropOff Time | varchar | 5 | Yes | |
| Odometer On | numeric | Yes | ||
| Odometer Off | numeric | Yes | ||
| Actual Vehicle | varchar | 4 | Yes | |
| Miles | numeric | Yes | ||
| Fare | money | Yes | ||
| Tripcode | varchar | 3 | Yes | |
| Paddress1 | varchar | 50 | Yes | |
| Daddress1 | varchar | 50 | Yes | |
| DriversInstructions | varchar | 100 | Yes | |
| Trip Number | varchar | 20 | Yes | |
| Pzipcode | varchar | 5 | Yes | |
| Dzipcode | varchar | 5 | Yes | |
| MedicaidID | varchar | 16 | Yes | |
| DATE | varchar | 8 | Yes | |
| DayNumber | numeric | Yes | ||
| StopDate | datetime | Yes | ||
| StartDate | datetime | Yes | ||
| CAddress | varchar | 50 | Yes | |
| Primary Key | ID | int identity | No | |
| TransactionNumber | numeric | Yes | ||
| Vehicle | varchar | 4 | Yes | |
| Tripdate | datetime | Yes | ||
| JDATE | numeric | Yes | ||
| Date | varchar | 4 | Yes | |
| ClientID | varchar | 10 | Yes | |
| LastName | varchar | 20 | Yes | |
| FirstName | varchar | 10 | Yes | |
| Agency | varchar | 3 | Yes | |
| Funding | varchar | 3 | Yes | |
| Purpose | varchar | 3 | Yes | |
| DriverInstructions | varchar | 100 | Yes | |
| ProcedureModifier | varchar | 2 | Yes | |
| PickupTime | varchar | 5 | Yes | |
| ActualPickupTime | varchar | 5 | Yes | |
| PickupLocation | varchar | 50 | Yes | |
| DropTime | varchar | 5 | Yes | |
| ActualDropTime | varchar | 5 | Yes | |
| Destination | varchar | 50 | Yes | |
| Tripcode | varchar | 3 | Yes | |
| Fare | money | Yes | ||
| TakeOrReturn | varchar | 1 | Yes | |
| Status | varchar | 1 | Yes | |
| Miles | numeric | Yes | ||
| Rate0 | money | Yes | ||
| Special | varchar | 3 | Yes | |
| DemandOrRoutine | varchar | 1 | Yes | |
| PickupZone | varchar | 6 | Yes | |
| DropZone | varchar | 6 | Yes | |
| Keydate | varchar | 6 | Yes | |
| Keytime | varchar | 5 | Yes | |
| WebVerified | varchar | 1 | Yes | |
| Verified | varchar | 1 | Yes | |
| InvoiceFlag | varchar | 1 | Yes | |
| InvoiceDate | numeric | Yes | ||
| Invnum | varchar | 12 | Yes | |
| ReferenceNumber | varchar | 20 | Yes | |
| InvoiceAmount | money | Yes | ||
| PaymentAmount | money | Yes | ||
| ScheduleRecordNumber | numeric | Yes | ||
| PaymentDate | datetime | Yes | ||
| CreditAmount | money | Yes | ||
| CreditDate | datetime | Yes | ||
| WriteOffAmount | money | Yes | ||
| WriteOffDate | datetime | Yes | ||
| Balance | money | Yes | ||
| SpecialStatus | varchar | 1 | Yes | |
| ActualVehicle | varchar | 4 | Yes | |
| Driver | varchar | 3 | Yes | |
| Provider | varchar | 4 | Yes | |
| OdometerOn | numeric | Yes | ||
| OdometerOff | numeric | Yes | ||
| DateTripRequest | datetime | Yes | ||
| TimeTripRequest | varchar | 5 | Yes | |
| UserTripRequest | varchar | 30 | Yes | |
| DefaultPickupTime | varchar | 5 | Yes | |
| DefaultDropTime | varchar | 5 | Yes | |
| DispatchTime | varchar | 5 | Yes | |
| CalculatedMiles | numeric | Yes | ||
| Timestamp | datetime | Yes | ||
| DestinationID | numeric | Yes | ||
| DestinationSource | varchar | 1 | Yes | |
| PickupLocationID | numeric | Yes | ||
| PickupLocationSource | varchar | 1 | Yes | |
| ScheduleRecordID | numeric | Yes | ||
| LastUpdate | datetime | Yes | ||
| LastUpTime | varchar | 5 | Yes | |
| LastUpUser | varchar | 30 | Yes | |
| Note | varchar | 100 | Yes | |
| BillNote | varchar | 100 | Yes | |
| Renew | bit | Yes | ||
| Primary Key | ID | int identity | No | |
| TransactionNumber | numeric | Yes | ||
| Vehicle | varchar | 4 | Yes | |
| Tripdate | datetime | Yes | ||
| JDATE | numeric | Yes | ||
| Date | varchar | 4 | Yes | |
| ClientID | varchar | 10 | Yes | |
| LastName | varchar | 20 | Yes | |
| FirstName | varchar | 10 | Yes | |
| Agency | varchar | 3 | Yes | |
| Funding | varchar | 3 | Yes | |
| Purpose | varchar | 3 | Yes | |
| DriverInstructions | varchar | 100 | Yes | |
| ProcedureModifier | varchar | 2 | Yes | |
| PickupTime | varchar | 5 | Yes | |
| ActualPickupTime | varchar | 5 | Yes | |
| PickupLocation | varchar | 50 | Yes | |
| DropTime | varchar | 5 | Yes | |
| ActualDropTime | varchar | 5 | Yes | |
| Destination | varchar | 50 | Yes | |
| Tripcode | varchar | 3 | Yes | |
| Fare | money | Yes | ||
| TakeOrReturn | varchar | 1 | Yes | |
| Status | varchar | 1 | Yes | |
| Miles | numeric | Yes | ||
| Rate0 | money | Yes | ||
| Special | varchar | 3 | Yes | |
| DemandOrRoutine | varchar | 1 | Yes | |
| PickupZone | varchar | 6 | Yes | |
| DropZone | varchar | 6 | Yes | |
| Keydate | varchar | 6 | Yes | |
| Keytime | varchar | 5 | Yes | |
| WebVerified | varchar | 1 | Yes | |
| Verified | varchar | 1 | Yes | |
| InvoiceFlag | varchar | 1 | Yes | |
| InvoiceDate | numeric | Yes | ||
| Invnum | varchar | 12 | Yes | |
| ReferenceNumber | varchar | 20 | Yes | |
| InvoiceAmount | money | Yes | ||
| PaymentAmount | money | Yes | ||
| ScheduleRecordNumber | numeric | Yes | ||
| PaymentDate | datetime | Yes | ||
| CreditAmount | money | Yes | ||
| CreditDate | datetime | Yes | ||
| WriteOffAmount | money | Yes | ||
| WriteOffDate | datetime | Yes | ||
| Balance | money | Yes | ||
| SpecialStatus | varchar | 1 | Yes | |
| ActualVehicle | varchar | 4 | Yes | |
| Driver | varchar | 3 | Yes | |
| Provider | varchar | 4 | Yes | |
| OdometerOn | numeric | Yes | ||
| OdometerOff | numeric | Yes | ||
| DateTripRequest | datetime | Yes | ||
| TimeTripRequest | varchar | 5 | Yes | |
| UserTripRequest | varchar | 30 | Yes | |
| DefaultPickupTime | varchar | 5 | Yes | |
| DefaultDropTime | varchar | 5 | Yes | |
| DispatchTime | varchar | 5 | Yes | |
| CalculatedMiles | numeric | Yes | ||
| Timestamp | datetime | Yes | ||
| DestinationID | numeric | Yes | ||
| DestinationSource | varchar | 1 | Yes | |
| PickupLocationID | numeric | Yes | ||
| PickupLocationSource | varchar | 1 | Yes | |
| ScheduleRecordID | numeric | Yes | ||
| LastUpdate | datetime | Yes | ||
| LastUpTime | varchar | 5 | Yes | |
| LastUpUser | varchar | 30 | Yes | |
| Note | varchar | 100 | Yes | |
| BillNote | varchar | 100 | Yes | |
| Renew | bit | Yes | ||
| Primary Key | ID | int identity | No | |
| TransactionNumber | numeric | Yes | ||
| Vehicle | varchar | 4 | Yes | |
| Tripdate | datetime | Yes | ||
| JDATE | numeric | Yes | ||
| Date | varchar | 4 | Yes | |
| ClientID | varchar | 10 | Yes | |
| LastName | varchar | 20 | Yes | |
| FirstName | varchar | 10 | Yes | |
| Agency | varchar | 3 | Yes | |
| Funding | varchar | 3 | Yes | |
| Purpose | varchar | 3 | Yes | |
| DriverInstructions | varchar | 100 | Yes | |
| ProcedureModifier | varchar | 2 | Yes | |
| PickupTime | varchar | 5 | Yes | |
| ActualPickupTime | varchar | 5 | Yes | |
| PickupLocation | varchar | 50 | Yes | |
| DropTime | varchar | 5 | Yes | |
| ActualDropTime | varchar | 5 | Yes | |
| Destination | varchar | 50 | Yes | |
| Tripcode | varchar | 3 | Yes | |
| Fare | money | Yes | ||
| TakeOrReturn | varchar | 1 | Yes | |
| Status | varchar | 1 | Yes | |
| Miles | numeric | Yes | ||
| Rate0 | money | Yes | ||
| Special | varchar | 3 | Yes | |
| DemandOrRoutine | varchar | 1 | Yes | |
| PickupZone | varchar | 6 | Yes | |
| DropZone | varchar | 6 | Yes | |
| Keydate | varchar | 6 | Yes | |
| Keytime | varchar | 5 | Yes | |
| WebVerified | varchar | 1 | Yes | |
| Verified | varchar | 1 | Yes | |
| InvoiceFlag | varchar | 1 | Yes | |
| InvoiceDate | numeric | Yes | ||
| Invnum | varchar | 12 | Yes | |
| ReferenceNumber | varchar | 20 | Yes | |
| InvoiceAmount | money | Yes | ||
| PaymentAmount | money | Yes | ||
| ScheduleRecordNumber | numeric | Yes | ||
| PaymentDate | datetime | Yes | ||
| CreditAmount | money | Yes | ||
| CreditDate | datetime | Yes | ||
| WriteOffAmount | money | Yes | ||
| WriteOffDate | datetime | Yes | ||
| Balance | money | Yes | ||
| SpecialStatus | varchar | 1 | Yes | |
| ActualVehicle | varchar | 4 | Yes | |
| Driver | varchar | 3 | Yes | |
| Provider | varchar | 4 | Yes | |
| OdometerOn | numeric | Yes | ||
| OdometerOff | numeric | Yes | ||
| DateTripRequest | datetime | Yes | ||
| TimeTripRequest | varchar | 5 | Yes | |
| UserTripRequest | varchar | 30 | Yes | |
| DefaultPickupTime | varchar | 5 | Yes | |
| DefaultDropTime | varchar | 5 | Yes | |
| DispatchTime | varchar | 5 | Yes | |
| CalculatedMiles | numeric | Yes | ||
| Timestamp | datetime | Yes | ||
| DestinationID | numeric | Yes | ||
| DestinationSource | varchar | 1 | Yes | |
| PickupLocationID | numeric | Yes | ||
| PickupLocationSource | varchar | 1 | Yes | |
| ScheduleRecordID | numeric | Yes | ||
| LastUpdate | datetime | Yes | ||
| LastUpTime | varchar | 5 | Yes | |
| LastUpUser | varchar | 30 | Yes | |
| Note | varchar | 100 | Yes | |
| BillNote | varchar | 100 | Yes | |
| Renew | bit | Yes | ||
| StatusReason | varchar | 50 | Yes |
CREATE VIEW [dbo].[TripData]
AS
SELECT s.ID AS Recnum, s.ClientID AS CltID, s.Vehicle, s.TakeOrReturn AS TakeReturn, s.LastName AS Lname, s.FirstName AS Fname, s.Special AS [WC-AMB],
s.Agency, s.PickupTime AS PTime, s.PickupLocation AS PAddress, c.CITY AS PCity, c.ZONCODE AS PZone, c.GISX AS PGisX, c.GISY AS PGisY,
s.DropTime AS DTime, s.Destination AS DAddress, d.City AS DCity, d.Zone AS DZone, d.Gisx AS DGisX, d.Gisy AS DGisY, s.DATE AS [D-R],
s.DefaultVehicle AS DVehicle, s.DefaultPickupTime AS DPTime, s.DefaultDropTime AS DDTime, c.DWELL AS CltBoarding, s.Status,
s.ActualPickupTime AS [Actual Pickup Time], s.ActualDropTime AS [Actual DropOff Time], s.OdometerOn AS [Odometer On],
s.OdometerOff AS [Odometer Off], s.Vehicle AS [Actual Vehicle], s.Miles, s.Fare, s.Tripcode, s.paddress1 AS Paddress1, s.daddress1 AS Daddress1,
s.DriverInstructions AS DriversInstructions, s.ReferenceNumber AS [Trip Number], c.ZIPCODE AS Pzipcode, d.Zipcode AS Dzipcode,
c.MEDID AS MedicaidID, s.DATE, s.DayNumber, s.StopDate, s.StartDate, c.ADDRESS1 AS CAddress
FROM dbo.Schedule AS s LEFT OUTER JOIN
dbo.Clients AS c ON s.ClientID = c.ClientID INNER JOIN
dbo.Vehicles AS v ON s.Vehicle = v.Vehicle INNER JOIN
dbo.Destinations AS d ON s.Destination = d.Destination
WHERE (c.PICKUPPL = s.PickupLocation)
UNION
SELECT s.ID AS Recnum, s.ClientID AS CltID, s.Vehicle, s.TakeOrReturn AS TakeReturn, s.LastName AS Lname, s.FirstName AS Fname, s.Special AS [WC-AMB],
s.Agency, s.PickupTime AS PTime, s.PickupLocation AS PAddress, d.City AS PCity, d.Zone AS PZone, d.Gisx AS PGisX, d.Gisy AS PGisY,
s.DropTime AS DTime, s.Destination AS DAddress, c.CITY AS DCity, c.ZONCODE AS DZone, c.GISX AS DGisX, c.GISY AS DGisY, s.DATE AS [D-R],
s.DefaultVehicle AS DVehicle, s.DefaultPickupTime AS DPTime, s.DefaultDropTime AS DDTime, c.DWELL AS CltBoarding, s.Status,
s.ActualPickupTime AS [Actual Pickup Time], s.ActualDropTime AS [Actual DropOff Time], s.OdometerOn AS [Odometer On],
s.OdometerOff AS [Odometer Off], s.Vehicle AS [Actual Vehicle], s.Miles, s.Fare, s.Tripcode, s.paddress1 AS Paddress1, s.daddress1 AS Daddress1,
s.DriverInstructions AS DriversInstructions, s.ReferenceNumber AS [Trip Number], c.ZIPCODE AS Dzipcode, d.Zipcode AS Pzipcode,
c.MEDID AS MedicaidID, s.DATE, s.DayNumber, s.StopDate, s.StartDate, c.ADDRESS1 AS CAddress
FROM dbo.Schedule AS s LEFT OUTER JOIN
dbo.Clients AS c ON s.ClientID = c.ClientID INNER JOIN
dbo.Vehicles AS v ON s.Vehicle = v.Vehicle INNER JOIN
dbo.Destinations AS d ON s.PickupLocation = d.Destination
WHERE (c.PICKUPPL = s.Destination)
UNION
SELECT s.ID AS Recnum, s.ClientID AS CltID, s.Vehicle, s.TakeOrReturn AS TakeReturn, s.LastName AS Lname, s.FirstName AS Fname, s.Special AS [WC-AMB],
s.Agency, s.PickupTime AS PTime, s.PickupLocation AS PAddress, d1.City AS PCity, d1.Zone AS PZone, d1.Gisx AS PGisX, d1.Gisy AS PGisY,
s.DropTime AS DTime, s.Destination AS DAddress, d2.City AS DCity, d2.Zone AS DZone, d2.Gisx AS DGisX, d2.Gisy AS DGisY, s.DATE AS [D-R],
s.DefaultVehicle AS DVehicle, s.DefaultPickupTime AS DPTime, s.DefaultDropTime AS DDTime, c.DWELL AS CltBoarding, s.Status,
s.ActualPickupTime AS [Actual Pickup Time], s.ActualDropTime AS [Actual DropOff Time], s.OdometerOn AS [Odometer On],
s.OdometerOff AS [Odometer Off], s.Vehicle AS [Actual Vehicle], s.Miles, s.Fare, s.Tripcode, s.paddress1 AS Paddress1, s.daddress1 AS Daddress1,
s.DriverInstructions AS DriversInstructions, s.ReferenceNumber AS [Trip Number], d1.Zipcode AS Pzipcode, d2.Zipcode AS Dzipcode,
c.MEDID AS MedicaidID, s.DATE, s.DayNumber, s.StopDate, s.StartDate, c.ADDRESS1 AS CAddress
FROM dbo.Schedule AS s LEFT OUTER JOIN
dbo.Clients AS c ON s.ClientID = c.ClientID INNER JOIN
dbo.Vehicles AS v ON s.Vehicle = v.Vehicle INNER JOIN
dbo.Destinations AS d1 ON s.PickupLocation = d1.Destination INNER JOIN
dbo.Destinations AS d2 ON s.Destination = d2.Destination
WHERE (NOT (c.PICKUPPL = s.Destination)) AND (NOT (c.PICKUPPL = s.PickupLocation))