By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,730 Members | 1,540 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,730 IT Pros & Developers. It's quick & easy.

Maximum Date in a group of records

P: 3
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.
Nov 20 '06 #1
Share this Question
Share on Google+
2 Replies


P: 93
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.
Try adding another argument in the first where clause saying which one you want like example add

and parent_doccategory = 'AE'

before the group by clause
Nov 21 '06 #2

P: 3
Try adding another argument in the first where clause saying which one you want like example add

and parent_doccategory = 'AE'

before the group by clause
That will work but then it eliminates the other two lines that I also need. I basinally need two group levels, one for the Parent_doc_No and the other for the Parent_DocCategory so that it first looks at the Parent_Doc_No and then at the Parent_Doc_Category and gets the most up to date for each category.
Nov 21 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.