I have a table that I need to find the maximum date per Doc_No per Parent_Doccategory. I can get the maximum date per Doc_No but I can't get the information for the next level. The following script is getting the maximum date per airplane but I should get a maximum date each time the parent_doccategory changes. Can anyone help? This is the script so far:
CREATE PROCEDURE [dbo].[sp_Maximum_Change_Date] AS
select Effective_Updated , parent_doc_no, parent_doccategory from gdb_01_4.dbo.aircraft_changes As S
where effective_updated = (select max(effective_updated)
from gdb_01_4_test.dbo.aircraft_changes where
parent_doc_no = S.parent_doc_no) group by parent_doc_no,parent_doccategory, effective_updated, order by parent_doc_no
GO
PARENT_DOC_NO PARENT_DOCCATEGORY EFFECTIVE_UPDATED
129 AC 3/24/2004 1:54:29 PM
129 AC 4/2/2004 1:44:39 PM
129 AE 6/24/2004 8:49:13 AM
129 AU 9/28/2004 12:16:30 PM
I want to get the one record for AC with the 4/2 date, the one for AE and the one for AU. My script is only returning the AU record because it has the maximum date for Parent_Doc_No 129.