Getting Maxdate for each record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Getting Maxdate for each record

Author

Topic

cmerchan
Starting Member

5 Posts

Posted - 2008-07-28 : 13:49:53

Hi I'm new to the site, so forgive me if i exclude something.
I'm trying to get records and their approvaldates.
Here's my problem each record has 2-3 approvaldates and all i want is the most recent date.

Select
A.Carrier
A.InvoiceNumber,
A.InvoiceDate,
A.Totalcost,
MAX(IA.ApprovalDate)

From AllocationDetail A
Left Outer JOIN InvoiceApproval IA 0n A.invoiceid=ia.invoiceid

basically the results just give me one record for all invoices, the one with the highest date. I need the all invoices and their most recent approvaldates.
thanks in advance

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-28 : 13:53:02

You need to add a GROUP BY clause ie:

Select
A.Carrier
A.InvoiceNumber,
A.InvoiceDate,
A.Totalcost,
MAX(IA.ApprovalDate)

From AllocationDetail A
Left Outer JOIN InvoiceApproval IA 0n A.invoiceid=ia.invoiceid

group by
A.Carrier
A.InvoiceNumber,
A.InvoiceDate,
A.Totalcost

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-28 : 13:58:49

or take only max date from second table

Select
A.Carrier
A.InvoiceNumber,
A.InvoiceDate,
A.Totalcost,
IA.MaxDate
From AllocationDetail A
Left Outer JOIN 
(
SELECT invoiceid,MAX(IA.ApprovalDate) as MaxDate
FROM InvoiceApproval 
GROUP BY invoiceid)IA 
0n A.invoiceid=ia.invoiceid

cmerchan
Starting Member

5 Posts

Posted - 2008-07-28 : 13:59:22

My Apologies I do have a group by clause. Still gives me multiple dates.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-28 : 14:01:20

quote:
Originally posted by cmerchan

My Apologies I do have a group by clause. Still gives me multiple dates.

then that means you've multiple records for same date. can you post what you're using now?

cmerchan
Starting Member

5 Posts

Posted - 2008-07-28 : 14:09:51

There are mulitple records for each invoice, each of those records are either the same day or a few days apart. all i need to the most recent record

SELECT
A.Carrier,
A.MasterAccountNumber [Account Number],
A.InvoiceNumber [InvNumber],
A.InvoiceDate [InvDate],
A.TotalCost [InvAmount],
P.Name,
MAX(IA.ApprovalDate),
A.AllocTotalCost as AllocTotalCost,
A.BatchName

FROM V_Allocationdetail A
Left Outer JOIN InvoiceApproval IA ON A.InvoiceID = IA.InvoiceID
Left Outer JOIN Principal P ON IA.UserPrincipalID=P.PrincipalID

WHERE
A.BatchName = 'A/P Batch 7/24/08'

Group by
A.Carrier, A.MasterAccountNumber,A.InvoiceNumber,A.InvoiceDate,A.TotalCost,
P.Name,IA.ApprovalDate,A.AllocTotalCost,A.BatchName

cmerchan
Starting Member

5 Posts

Posted - 2008-07-28 : 14:16:08

This is an example of the resulys I get. all I need is the recent record (Gerald Guger)

Carrier MstAcct InvNum InvDate Name Approvaldate Ttlcst batchname
Sprint 13161454 04789801 2008-07-07 00:00:00.000 Meghan Sloan 2008-07-23 14:15:12.220 3419.6200 A/P Batch 7/24/08
Sprint 13161454 04789801 2008-07-07 00:00:00.000 Gerald Guger 2008-07-23 17:01:41.613 3419.6200 A/P Batch 7/24/08

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-07-28 : 15:41:47

You can do as Visakh suggested and use a derived table:

SELECT 
	A.Carrier, 
	A.MasterAccountNumber [Account Number],
	A.InvoiceNumber [InvNumber],
	A.InvoiceDate [InvDate],
	A.TotalCost [InvAmount],
	P.Name,
	IA.MaxDate AS ApprovalDate,
	A.AllocTotalCost as AllocTotalCost,
	A.BatchName
FROM 
	V_Allocationdetail AS A
LEFT OUTER JOIN 
	(
		SELECT invoiceid,MAX(IA.ApprovalDate) as MaxDate
		FROM InvoiceApproval 
		GROUP BY invoiceid
	) AS IA 
	ON A.InvoiceID = IA.InvoiceID
LEFT OUTER JOIN 
	Principal AS P 
	ON IA.UserPrincipalID=P.PrincipalID

Or if you are using SQL 2005 or 2008 you can use the ROW_NUMBER function:

SELECT
	*
FROM
	(
		SELECT 
			A.Carrier, 
			A.MasterAccountNumber [Account Number],
			A.InvoiceNumber [InvNumber],
			A.InvoiceDate [InvDate],
			A.TotalCost [InvAmount],
			P.Name,
			IA.ApprovalDate,
			A.AllocTotalCost as AllocTotalCost,
			A.BatchName,
			ROW_NUMBER() OVER(PARTITION BY IA.InvoiceID ORDER BY IA.ApprovalDate DESC) AS RowNum
		FROM 
			V_Allocationdetail A
		LEFT OUTER JOIN 
			InvoiceApproval IA 
			ON A.InvoiceID = IA.InvoiceID
		LEFT OUTER JOIN 
			Principal P 
			ON IA.UserPrincipalID=P.PrincipalID
	) AS T
WHERE 
	T.RowNum = 1

cmerchan
Starting Member

5 Posts

Posted - 2008-07-28 : 16:16:09

Thank you all for your input. I was able to get it working.
I added the following to the script:

Where
IA.Approvaldate IN (Select MAX(ApprovalDate) from InvoiceApproval IA Where IA.Invoiceid=A.Invoiceid and IA.ApprovalDate <GetDate())

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources