Complex SQL Problem..

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

Open in new window

LVL 13
rfwoolfAsked:
Who is Participating?
 
tigin44Commented:
a procedure like the one may solve your problem. You may pass the threshold value(s) for Flight Cycles, Flight Hours or Days each or all...
CREATE PROCEDURE spFLIGHT_TASK (
	@FlightCyclesTilDue		int,
	@FlightHoursTilDue		int,
	@DaysTilDue				int
)
AS
SELECT ID, FlightCyclesTilDue, FlightHoursTilDue, DaysTilDue
FROM 
	(SELECT M.ID, CASE WHEN M.DueInFlightCycles <> 0 THEN A.FlightCycles-M.DueInFlightCycles ELSE 0 END AS FlightCyclesTilDue,
				 CASE WHEN M.DueInFlightHours <> 0 THEN M.DueInFlightHours - (A.FlightHours - F.StampFH) ELSE 0 END AS FlightHoursTilDue,
				 CASE WHEN M.DueInDays <> 0 THEN M.DueInDays - DATEDIFF(d, F.TimeStampDone, GETDATE()) ELSE 0 END AS DaysTilDue
	--SELECT *                       
	FROM TblAircraft A
					INNER JOIN TblTaskMasters M ON A.MODEL = M.AircraftModel 
					LEFT OUTER JOIN (SELECT I.ID, I.MasterTask, I.AircraftReg, I.StampFC, I.StampFH, I.TimeStampDone 
									 FROM TblTaskInstances I 
										INNER JOIN (SELECT MasterTask, AircraftReg, MAX(TimeStampDone) AS TimeStampDone 
													FROM TblTaskInstances
													GROUP BY MasterTask, AircraftReg) D ON I.MasterTask = D.MasterTask 
																						AND I.AircraftReg = D.AircraftReg 
																						AND I.TimeStampDone = D.TimeStampDone ) F ON A.REGISTRATION = F.AircraftReg 
																																																													AND M.ID = F.MasterTask  
	WHERE A.REGISTRATION = 'TN-AIC') A
WHERE FlightCyclesTilDue < ISNULL(@FlightCyclesTilDue, FlightCyclesTilDue)
   OR FlightHoursTilDue < ISNULL(@FlightHoursTilDue, FlightHoursTilDue)
   OR DaysTilDue < ISNULL(@DaysTilDue, DaysTilDue)

Open in new window

0
 
tigin44Commented:
as I understand your logic a query like this one may help you
SELECT M.ID, CASE WHEN M.DueInFlightCycles <> 0 THEN A.FlightCycles-I.DueInFlightCycles ELSE 0 END AS FlightCyclesTilDue,
			 CASE WHEN M.DueInFlightHours <> 0 THEN M.DueInFlightHours - (A.FlightHours - I.StampFH) ELSE 0 END AS FlightHoursTilDue,
			 CASE WHEN M.DueInDays <> 0 THEN M.DueInDays - DATEDIFF(d, D.TimeStampDone, GETDATE()) ELSE 0 END AS DaysTilDue,
		 
FROM TblAircraft A
		INNER JOIN TblTaskMasters M ON A.MODEL = M.AircraftModel 
		INNER JOIN TblTaskInstances I ON M.ID = I.MasterTask
		INNER JOIN (SELECT MasterTask, AircraftReg, MAX(TimeStampDone) AS TimeStampDone 
					FROM TblTaskInstances
					GROUP BY MasterTask, AircraftReg) D ON M.MasterTask = D.MasterTask AND M.AircraftReg = D.AircraftReg AND M.TimeStampDone = D.TimeStampDone   
WHERE A.REGISTRATION = 'TN-AIC'		

Open in new window

0
 
rfwoolfAuthor Commented:
Wow... this is some mind-blowing stuff...
I'm having trouble with the last clause which I'm trying to understand as well, but here's the problem:

"M.AircraftReg = D.AircraftRegistration"
  The problem is that M (TblMasterTasks) doesn't keep the Aircraft Registrations, it just keeps the Model of the aircraft.
I guess what we need to do is to take D.AircraftRegistration, find out what model it is (by looking in A (TblAircraft) and getting the MODEL value, then going to M (TblMasterTasks) which has a MODEL value.

Basically the best way to explain it is this...
TaskMasters (which are task rules that get applied every X flighht hours or X flight cycles) apply to Aircraft Models. What happens if you have 7 aircraft all of the same model? So the theory is to create Tasks for models, and then TaskInstances for the aircraft.

Thanks for your help
  INNER JOIN 
    (SELECT MasterTaskID, AircraftRegistration, MAX(TimeStampCompleted) AS TimeStampDone FROM TblTaskInstances 
      GROUP BY MasterTaskID, AircraftRegistration) D ON 
       M.ID = D.MasterTaskID AND M.AircraftRegistrati = D.AircraftRegistration AND M.TimeStampDone = D.TimeStampDone   
WHERE A.REGISTRATION = 'TN-AIC'    
     

Open in new window

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
tigin44Commented:
this one is the correct query... the above one has errors...
SELECT M.ID, CASE WHEN M.DueInFlightCycles <> 0 THEN A.FlightCycles-M.DueInFlightCycles ELSE 0 END AS FlightCyclesTilDue,
			 CASE WHEN M.DueInFlightHours <> 0 THEN M.DueInFlightHours - (A.FlightHours - F.StampFH) ELSE 0 END AS FlightHoursTilDue,
			 CASE WHEN M.DueInDays <> 0 THEN M.DueInDays - DATEDIFF(d, F.TimeStampDone, GETDATE()) ELSE 0 END AS DaysTilDue
--SELECT *			 
FROM TblAircraft A
		INNER JOIN TblTaskMasters M ON A.MODEL = M.AircraftModel 
		LEFT OUTER JOIN (SELECT I.ID, I.MasterTask, I.AircraftReg, I.StampFC, I.StampFH, I.TimeStampDone 
						 FROM TblTaskInstances I 
							INNER JOIN (SELECT MasterTask, AircraftReg, MAX(TimeStampDone) AS TimeStampDone 
										FROM TblTaskInstances
										GROUP BY MasterTask, AircraftReg) D ON I.MasterTask = D.MasterTask 
																			AND I.AircraftReg = D.AircraftReg 
																			AND I.TimeStampDone = D.TimeStampDone ) F ON A.REGISTRATION = F.AircraftReg 
																														AND M.ID = F.MasterTask  
WHERE A.REGISTRATION = 'TN-AIC'

Open in new window

0
 
tigin44Commented:
maybe the case parts of the query need some modification based on your logic...
0
 
FVERCommented:
This one should work even if aircraf have never been maintained before, but only for FlightCyclesTilDue and FlightHoursTilDue columns.
You would need a "First flight time" column in TblAircraft table to make the DaysTilDue column OK for first maintenance.
SELECT TA.REGISTRATION AS AircraftReg,
       TTM.ID AS MasterTask,
       case when TTM.DueInFlightCycles > 0 then TTM.DueInFlightCycles - (TA.FlightCycles - isnull(TTI.StampFC,0))
            else 0
       end AS FlightCyclesTilDue,
       case when TTM.DueInFlightHours > 0 then TTM.DueInFlightHours - (TA.FlightHours - isnull(TTI.StampFH,0))
            else 0
       end AS FlightHoursTilDue ,
       case when (TTM.DueInDays > 0 and TTI.TimeStampDone is not null) then TTM.DueInDays - Datediff(day, TTI.TimeStampDone, getdate())
            else 0
       end AS DaysTilDue
  FROM TblAircraft TA
       inner join TblTaskMasters TTM
               on TTM.AircraftModel = TA.MODEL
       left join (Select AircraftReg, MasterTask, max(TimeStampDone) AS TimeStampDone
                    from TblTaskInstances
                   group by AircraftReg, MasterTask) TTI_max
              on TTI_max.AircraftReg = TA.REGISTRATION
             and TTI_max.MasterTask = TTM.ID
       left join TblTaskInstances TTI
              on TTI.AircraftReg = TTI_max.AircraftReg
             and TTI.MasterTask = TTI_max.MasterTask
             and TTI.TimeStampDone = TTI_max.TimeStampDone
 WHERE TA.REGISTRATION = 'TN-AIC'

Open in new window

0
 
rfwoolfAuthor Commented:
Thanks guys..

so far I have tested tigin44's and it works beautifully.
One last thing that would be the cherry on top is to allow the user to set a filter on either the Flight Cycles, Flight Hours or Days.
This should work on more than one interval, for example I could ask to see tasks due in the 100 cycles, 200 hours, and 30 days, and then you might have a task that's due in 150 cycles, 190 hours, and 33 days, and it should come up.

I know this might be a major complication, so if necessary, let's put that in a view and then simply select from the view or something??

FVER I will try yours now :p
0
 
rfwoolfAuthor Commented:
FVER>
Yours is almost exactly the same as tigin44 except the improvement that you mentioned, I see you used ISNULL which is nice. tigin44's solution will display tasks even if they've never been done before and will say NULL under the due period.
I did play with ISNULL after tigin44's solution and tried a similar thing that you did, but then I decided that "NULL" makes more sense.
I will try award you some points for reminding me about this problem and for your addition to the solution :)
0
 
rfwoolfAuthor Commented:
tigin44 is a genius!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.