troubleshooting Question

Complex SQL Problem..

Avatar of rfwoolf
rfwoolfFlag for South Africa asked on
DelphiMicrosoft SQL Server 2008SQL
9 Comments2 Solutions349 ViewsLast Modified:
I need some help (ideally the SQL or Stored Procedure) or advice on a challenging SQL problem that relates to scheduling maintenance on aircraft...
Is this so complex that I should use a Stored Procedure to create a view?

Attached you will see the table structure and desired result set.

Database is SQL Server 2008 Express

Below I will give you the algorithm with some SQL samples of how I think it should work, but as you can see I can't quite get to where it needs to be...

INPUT: An aircraft Registration, e.g. TN-AIC
1. Take your Aircraft Registration and get the Model (e.g. BAE 146).
>>>SAMPLE SQL: Select MODEL FROM TblAircraft WHERE REGISTRATION = 'TN-AIB'
2. Get all the Task Masters that apply to that model, (e.g. TaskMasters 1, 3 and 4.)
>>>SELECT * FROM TblTaskMasters
  WHERE AircraftModel = (SELECT MODEL FROM TblAircraft WHERE REGISTRATION = 'TN-AIC')
3. For each of those Task Masters, we need to see when they are next due for our aircraft (TN-AIC) depending on the interval(s) (note: if an interval is 0 we ignore it).
3a. For each Task Master we find the latest TaskInstance of that TaskMaster that was performed on our Aircraft.
For example, TaskMaster ID 1 was performed on TN-AIC twice.
3b. We take our latest one (TaskInstance # 4). We see that the interval on the Task Master is every 5000 FH (Flight Hours). But we last performed it on 13000 FH for that aircraft, and that aircraft is currently at 15000 FH. 5000 - (15000 - 13000) = 3000 FH.

====
Addendum: an explanation:
Basically we have a table of "TaskMasters" which have rules about when a task should be done. These rules are 'intervals' such as every 5,000 flying hours or 3,000 flying cycles or 15 calendar days etc...
Then we have a table "TaskInstances" which records each time a TaskMaster was done, with a snapshot of the Flying Hours and Flying Cycles of the aircraft when the task was performed.
We now want to see when all the Task Masters that apply to a particular aircraft are due.
TABLE: TblAircraft
===
REGISTRATION  MODEL    FlightCycles  FlightHours
TN-AIC        BAE 146  11000         15000
LYB-137       SD-360   3700          5200         
XY-MWX        BAE 146  7000          8300

TABLE: TblTaskMasters
===
ID AircraftModel DueInFlightCycles DueInFlightHours DueInDays
1  BAE 146       0                 5000             0  
2  SHORTS SD-360 0                 1000             0
3  BAE 146       1000              0                0
4  BAE 146       0                 0                30
  
TABLE: TblTaskInstances
===
ID MasterTask AircraftReg StampFC StampFH TimeStampDone
1  1          TN-AIC      7240    8000    2009-08-04 08:23:12
2  2          LYB-137     3100    5130    2009-10-07 13:12:31       
3  1          XY-MWX      5800    7400    2009-10-11 17:03:44
4  1          TN-AIC      10500   13000   2009-10-16 04:08:21
5  4          TN-AIC      10800   13750   2009-12-01 05:23:09
=========================================


QUERY INPUT EXAMPLE:
TN-AIC (The Aircraft Registration)

See above for the algorithm...

DESIRED RESULT SET:
TaskMasterID FlightCyclesTilDue FlightHoursTilDue DaysTilDue 
1            0                  3000              0
3            11000              0                 0
4            0                  0                 15
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 9 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros