how do I only show the record with the max note created date?
SELECT tl_Aircraft.Registration, tm_AircraftDutySchedules.A
tm_AircraftDutySchedules.E
tm_AircraftDutySchedules.S
tm_AircraftDutySchedules.I
tm_AircraftDutySchedules.E
tm_Notes.DateCreated, tm_Notes.NoteText, tm_Notes.isactive,
datediff(mi,origstarttime,
FROM tm_AircraftDutySchedules INNER JOIN
tl_Aircraft ON tm_AircraftDutySchedules.A
tm_Notes ON tm_AircraftDutySchedules.A
WHERE (tm_AircraftDutySchedules.
and (notetext like '%This aircraft schedule was auto-delayed%')
order by tl_Aircraft.Registration asc
I tried max on tm_Notes.DateCreated but didn't have any luck.
tm_AircraftDutySchedules.E
tm_AircraftDutySchedules.S
tm_AircraftDutySchedules.I
tm_AircraftDutySchedules.E
tm_Notes.DateCreated, tm_Notes.NoteText, tm_Notes.isactive,
datediff(mi,origstarttime,
FROM tm_AircraftDutySchedules INNER JOIN
tl_Aircraft ON tm_AircraftDutySchedules.A
tm_Notes ON tm_AircraftDutySchedules.A
WHERE (tm_AircraftDutySchedules.
and (notetext like '%This aircraft schedule was auto-delayed%')
order by tl_Aircraft.Registration asc
I tried max on tm_Notes.DateCreated but didn't have any luck.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
...unchanged...
FROM tm_AircraftDutySchedules INNER JOIN
tl_Aircraft ON tm_AircraftDutySchedules.A
SELECT *, ROW_NUMBER() OVER (PARTITION BY EntityKeyID ORDER BY DateCreated DESC) AS rownum
FROM tm_Notes tm_Notes_Rownum) AS
tm_Notes ON tm_AircraftDutySchedules.A
...unchanged...
FROM tm_AircraftDutySchedules INNER JOIN
tl_Aircraft ON tm_AircraftDutySchedules.A
SELECT *, ROW_NUMBER() OVER (PARTITION BY EntityKeyID ORDER BY DateCreated DESC) AS rownum
FROM tm_Notes tm_Notes_Rownum) AS
tm_Notes ON tm_AircraftDutySchedules.A
...unchanged...
Here is another variation
SELECT tl_Aircraft.Registration, tm_AircraftDutySchedules.AircraftDutyScheduleID, tm_AircraftDutySchedules.AircraftID, tm_AircraftDutySchedules.StartAirportID,
tm_AircraftDutySchedules.EndAirportID, tm_AircraftDutySchedules.AircraftDutyLookupID, tm_AircraftDutySchedules.StartTime, tm_AircraftDutySchedules.EndTime,
tm_AircraftDutySchedules.StartTimeLocal, tm_AircraftDutySchedules.EndTimeLocal, tm_AircraftDutySchedules.HasUserNotes, tm_AircraftDutySchedules.Description,
tm_AircraftDutySchedules.IsTraxManaged, tm_AircraftDutySchedules.BWAircraftMaintenanceNoteID, tm_AircraftDutySchedules.StartVendorID,
tm_AircraftDutySchedules.EndVendorID, tm_AircraftDutySchedules.OrigStartTime, tm_AircraftDutySchedules.OrigEndTime, tm_Notes.NoteTypeID,
tm_Notes.DateCreated, tm_Notes.NoteText, tm_Notes.isactive,
datediff(mi,origstarttime,starttime) as TotalMinMoved
FROM tm_AircraftDutySchedules
INNER JOIN tl_Aircraft ON tm_AircraftDutySchedules.AircraftID = tl_Aircraft.AircraftID
INNER JOIN (select EntityKeyID, max(DateCreated) DateCreated from tm_Notes group by AircraftDutyScheduleID) A on tm_AircraftDutySchedules.AircraftDutyScheduleID = A.EntityKeyID
INNER JOIN tm_Notes ON A.EntityKeyID = tm_Notes.EntityKeyID and A.DateCreated = tm_Notes.DateCreated
WHERE (tm_AircraftDutySchedules.StartTime <> tm_AircraftDutySchedules.OrigStartTime)
AND (tm_AircraftDutySchedules.StartTime >= GETDATE())
and (notetext like '%This aircraft schedule was auto-delayed%')
order by tl_Aircraft.Registration asc
correction
SELECT tl_Aircraft.Registration, tm_AircraftDutySchedules.AircraftDutyScheduleID, tm_AircraftDutySchedules.AircraftID, tm_AircraftDutySchedules.StartAirportID,
tm_AircraftDutySchedules.EndAirportID, tm_AircraftDutySchedules.AircraftDutyLookupID, tm_AircraftDutySchedules.StartTime, tm_AircraftDutySchedules.EndTime,
tm_AircraftDutySchedules.StartTimeLocal, tm_AircraftDutySchedules.EndTimeLocal, tm_AircraftDutySchedules.HasUserNotes, tm_AircraftDutySchedules.Description,
tm_AircraftDutySchedules.IsTraxManaged, tm_AircraftDutySchedules.BWAircraftMaintenanceNoteID, tm_AircraftDutySchedules.StartVendorID,
tm_AircraftDutySchedules.EndVendorID, tm_AircraftDutySchedules.OrigStartTime, tm_AircraftDutySchedules.OrigEndTime, tm_Notes.NoteTypeID,
tm_Notes.DateCreated, tm_Notes.NoteText, tm_Notes.isactive,
datediff(mi,origstarttime,starttime) as TotalMinMoved
FROM tm_AircraftDutySchedules
INNER JOIN tl_Aircraft ON tm_AircraftDutySchedules.AircraftID = tl_Aircraft.AircraftID
INNER JOIN (select EntityKeyID, max(DateCreated) DateCreated from tm_Notes group by EntityKeyID) A on tm_AircraftDutySchedules.AircraftDutyScheduleID = A.EntityKeyID
INNER JOIN tm_Notes ON A.EntityKeyID = tm_Notes.EntityKeyID and A.DateCreated = tm_Notes.DateCreated
WHERE (tm_AircraftDutySchedules.StartTime <> tm_AircraftDutySchedules.OrigStartTime)
AND (tm_AircraftDutySchedules.StartTime >= GETDATE())
and (notetext like '%This aircraft schedule was auto-delayed%')
order by tl_Aircraft.Registration ascASKER
scott - not quite following you on that
ewangoya - that query returned no rows
ewangoya - that query returned no rows
SELECT tl_Aircraft.Registration, tm_AircraftDutySchedules.A
tm_AircraftDutySchedules.E
tm_AircraftDutySchedules.S
tm_AircraftDutySchedules.I
tm_AircraftDutySchedules.E
tm_Notes.DateCreated, tm_Notes.NoteText, tm_Notes.isactive,
datediff(mi,origstarttime,
FROM tm_AircraftDutySchedules INNER JOIN
tl_Aircraft ON tm_AircraftDutySchedules.A
SELECT *, ROW_NUMBER() OVER (PARTITION BY EntityKeyID ORDER BY DateCreated DESC) AS rownum
FROM tm_Notes tm_Notes_Rownum) AS
tm_Notes ON tm_AircraftDutySchedules.A
WHERE (tm_AircraftDutySchedules.
and (notetext like '%This aircraft schedule was auto-delayed%')
order by tl_Aircraft.Registration asc
tm_AircraftDutySchedules.E
tm_AircraftDutySchedules.S
tm_AircraftDutySchedules.I
tm_AircraftDutySchedules.E
tm_Notes.DateCreated, tm_Notes.NoteText, tm_Notes.isactive,
datediff(mi,origstarttime,
FROM tm_AircraftDutySchedules INNER JOIN
tl_Aircraft ON tm_AircraftDutySchedules.A
SELECT *, ROW_NUMBER() OVER (PARTITION BY EntityKeyID ORDER BY DateCreated DESC) AS rownum
FROM tm_Notes tm_Notes_Rownum) AS
tm_Notes ON tm_AircraftDutySchedules.A
WHERE (tm_AircraftDutySchedules.
and (notetext like '%This aircraft schedule was auto-delayed%')
order by tl_Aircraft.Registration asc
Did you try gohord's solution?
I think he has it the correct way.
Ashok
I think he has it the correct way.
Ashok
tm_AircraftDutySchedules.E
tm_AircraftDutySchedules.S
tm_AircraftDutySchedules.I
tm_AircraftDutySchedules.E
tm_Notes.DateCreated, tm_Notes.NoteText, tm_Notes.isactive,
datediff(mi,origstarttime,
ROW_NUMBER() over (Order by tm_Notes.DateCreated DESC) as Instance_Counter
FROM tm_AircraftDutySchedules INNER JOIN
tl_Aircraft ON tm_AircraftDutySchedules.A
tm_Notes ON tm_AircraftDutySchedules.A
WHERE (tm_AircraftDutySchedules.
and (notetext like '%This aircraft schedule was auto-delayed%')
order by tm_Notes.DateCreated desc