Thank you for continuing with this thread, Ryan. I don't have nuclear
launch codes in my database, but it is over 4MB in size. But the data in
it is not that important. Let me post the view I am interested in:
select Trip.TripID as 'Trip Number',
Mode.Mode as 'Mode',
TripOD.TripOD as 'Origin&Destination',
TripDescription.TripDescription as 'Description',
Trip.Distance as 'Distance (KM)',
TripTimings.TripDates as 'Trip Dates',
TripTimings.TripTimings as 'Trip Timings',
TripTimings.TripScheduledDates as 'Scheduled Dates',
TripTimings.TripScheduledTimings as 'Scheduled Timings',
TripTimes.DepartureDelay as 'Departure Delay',
TripTimes.ArrivalDelay as 'Arrival Delay',
TripTimes.TripTime as 'Trip Time (HH:MM)',
TripCost.TotalCostUSD as 'Cost (USD)',
TripCost.OutOfPocketCostUSD as 'Out of Pocket Cost (USD)',
TripCalculatedValues.Speed as 'Speed in KMPH',
TripCalculatedValues.CentsPerKM as 'Cost (c/KM)',
TripCalculatedValues.OutOfPocketCentsPerKM as 'Out of Pocket Cost (c/KM)',
TripCalculatedValues.DollarPerHour as 'Cost ($/Hour)',
TripCalculatedValues.OutOfPocketDollarPerHour as 'Out of Pocket Cost
($/Hour)',
AllTripNotes.AllTripNotes as 'Trip Notes',
P1.PlaceAlternates as 'Origin Alternates',
P2.PlaceAlternates as 'Destination Alternates',
P3.PlaceDetails as 'Origin Details',
P4.PlaceDetails as 'Destination Details',
P5.AllPlaceNotes as 'Origin Notes',
P6.AllPlaceNotes as 'Destination Notes',
AllTripGroups.AllTripGroups as 'Trip Groups',
AllTripGroupTripNotes.AllTripGroupTripNotes as 'Trip Group Notes',
AllVehicleNotes.AllVehicleNotes as 'Vehicle Notes',
AllModeNotes.AllModeNotes as 'Mode Notes'
from Trip
inner join Mode on Trip.ModeNumber = Mode.ModeID
inner join TripCost on Trip.TripID = TripCost.TripID
inner join TripDescription on Trip.TripID = TripDescription.TripID
inner join TripOD on Trip.TripID = TripOD.TripID
inner join TripTimes on Trip.TripID = TripTimes.TripID
inner join TripTimings on Trip.TripID = TripTimings.TripID
inner join TripCalculatedValues on Trip.TripID = TripCalculatedValues.TripID
inner join AllTripNotes on Trip.TripID = AllTripNotes.TripID
inner join AllTripGroups on Trip.TripID = AllTripGroups.TripID
inner join AllTripGroupTripNotes on Trip.TripID =
AllTripGroupTripNotes.TripID
inner join AllVehicleNotes on Trip.VehicleNumber = AllVehicleNotes.VehicleID
inner join AllModeNotes on Trip.ModeNumber = AllModeNotes.ModeID
inner join PlaceAlternateNames P1 on Trip.Origin = P1.PlaceID
inner join PlaceAlternateNames P2 on Trip.Destination = P2.PlaceID
inner join PlaceDetails P3 on Trip.Origin = P3.PlaceID
inner join PlaceDetails P4 on Trip.Destination = P4.PlaceID
inner join AllPlaceNotes P5 on Trip.Origin = P5.PlaceID
inner join AllPlaceNotes P6 on Trip.Destination = P6.PlaceID
As you can see it is a join of very many tables and views. And I have
given the columns nice names to make it easier to read and understand what
they are rather than guessing from cryptic camelCase names.
Now, this is the text of the TripTimes view that is one of the sources of
the view above:
select TripID,
(cast(strftime('%s',EndDateTime) - strftime('%s',StartDateTime)
+(StartGMTOffset - EndGMTOffset)*3600 as integer)/3600)
|| ':' ||
substr('00'|| (cast(strftime('%s',EndDateTime) -
strftime('%s',StartDateTime) +(StartGMTOffset - EndGMTOffset)*3600 as
integer)%3600/60), -2,2)
as TripTime,
cast(strftime('%s',EndDateTime) - strftime('%s',StartDateTime) as
float)/3600.0 +(StartGMTOffset - EndGMTOffset) as TripTimeRaw,
case when (strftime('%s',StartDateTime) -
strftime('%s',ScheduledStartDateTime) +(ScheduledStartGMTOffset -
StartGMTOffset)*3600) >= 0 then
(cast(strftime('%s',StartDateTime) - strftime('%s',ScheduledStartDateTime)
+(ScheduledStartGMTOffset - StartGMTOffset)*3600 as integer)/3600)
|| ':' ||
substr('00'|| (cast(strftime('%s',StartDateTime) -
strftime('%s',ScheduledStartDateTime) +(ScheduledStartGMTOffset -
StartGMTOffset)*3600 as integer)%3600/60), -2,2) else
'-'||(cast(strftime('%s',ScheduledStartDateTime) -
strftime('%s',StartDateTime) +(StartGMTOffset -
ScheduledStartGMTOffset)*3600 as integer)/3600)
|| ':' ||
substr('00'|| (cast(strftime('%s',ScheduledStartDateTime) -
strftime('%s',StartDateTime) +(StartGMTOffset -
ScheduledStartGMTOffset)*3600 as integer)%3600/60), -2,2) end
as DepartureDelay,
cast(strftime('%s',StartDateTime) - strftime('%s',ScheduledStartDateTime)
as float)/3600.0 +(ScheduledStartGMTOffset - StartGMTOffset) as
DepartureDelayRaw,
case when (strftime('%s',EndDateTime) - strftime('%s',ScheduledEndDateTime)
+(ScheduledEndGMTOffset - EndGMTOffset)*3600) >= 0 then
(cast(strftime('%s',EndDateTime) - strftime('%s',ScheduledEndDateTime)
+(ScheduledEndGMTOffset - EndGMTOffset)*3600 as integer)/3600)
|| ':' ||
substr('00'|| (cast(strftime('%s',EndDateTime) -
strftime('%s',ScheduledEndDateTime) +(ScheduledEndGMTOffset -
EndGMTOffset)*3600 as integer)%3600/60), -2,2) else
'-'||(cast(strftime('%s',ScheduledEndDateTime) - strftime('%s',EndDateTime)
+(EndGMTOffset - ScheduledEndGMTOffset)*3600 as integer)/3600)
|| ':' ||
substr('00'|| (cast(strftime('%s',ScheduledEndDateTime) -
strftime('%s',EndDateTime) +(EndGMTOffset - ScheduledEndGMTOffset)*3600 as
integer)%3600/60), -2,2) end
as ArrivalDelay,
cast(strftime('%s',EndDateTime) - strftime('%s',ScheduledEndDateTime) as
float)/3600.0 +(ScheduledEndGMTOffset - EndGMTOffset) as ArrivalDelayRaw
from Trip
As you can see, I calculate times in HH:MM format for display and I also
calculate the raw number of hours as a floating point number. I want to be
able to filter my original view based on the value of things like
TripTimeRaw rather than TripTime (which is a string in HH:MM format). The
problem is that I don't want to expand my display grid any further (it is
already way too wide) by including TripTimeRaw as one of the selected
columns of my main view. But I do want to be able to filter that view
based on this value.
Now, I can copy the text of the view definition and paste it in as a plain
old query, add any filter conditions I want based on the "raw" columns in
my sub-views, and it works perfectly fine. I can also add ORDER BY clauses
to this query based on these "raw" columns, and that also works fine. I
just wish there was an easier way to do this using just the view rather
than copying and pasting massive amounts of text between windows.
