Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

how do I only show the record with the max note created date?

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
                      tm_Notes ON tm_AircraftDutySchedules.AircraftDutyScheduleID = tm_Notes.EntityKeyID
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


I tried max on tm_Notes.DateCreated but didn't have any luck.
0
fwstealer
Asked:
fwstealer
  • 2
  • 2
  • 2
  • +2
1 Solution
 
Christopher GordonSenior Developer AnalystCommented:
SELECT  top 1 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,
                     
                      ROW_NUMBER() over (Order by tm_Notes.DateCreated DESC) as Instance_Counter
                     
FROM         tm_AircraftDutySchedules INNER JOIN
                      tl_Aircraft ON tm_AircraftDutySchedules.AircraftID = tl_Aircraft.AircraftID INNER JOIN
                      tm_Notes ON tm_AircraftDutySchedules.AircraftDutyScheduleID = tm_Notes.EntityKeyID
WHERE     (tm_AircraftDutySchedules.StartTime <> tm_AircraftDutySchedules.OrigStartTime) AND (tm_AircraftDutySchedules.StartTime >= GETDATE())
                  and (notetext like '%This aircraft schedule was auto-delayed%')
                 
order by tm_Notes.DateCreated desc
0
 
Christopher GordonSenior Developer AnalystCommented:
Also, you can get rid of that ROW_NUMBER() / Instance_Counter field.  It's not necessary.
0
 
Scott PletcherSenior DBACommented:
...unchanged...
FROM         tm_AircraftDutySchedules INNER JOIN
                      tl_Aircraft ON tm_AircraftDutySchedules.AircraftID = tl_Aircraft.AircraftID INNER JOIN (
                      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.AircraftDutyScheduleID = tm_Notes.EntityKeyID AND tm_Notes.rownum = 1
...unchanged...
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.

 
Ephraim WangoyaCommented:
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

Open in new window

0
 
Ephraim WangoyaCommented:
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 asc

Open in new window

0
 
fwstealerAuthor Commented:
scott - not quite following you on that
ewangoya - that query returned no rows
0
 
Scott PletcherSenior DBACommented:
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 *, ROW_NUMBER() OVER (PARTITION BY EntityKeyID ORDER BY DateCreated DESC) AS rownum
                      FROM tm_Notes tm_Notes_Rownum) AS                      
                      tm_Notes ON tm_AircraftDutySchedules.AircraftDutyScheduleID = tm_Notes.EntityKeyID AND tm_Notes.rownum = 1                    

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
0
 
AshokCommented:
Did you try gohord's solution?

I think he has it the correct way.

Ashok
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.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now