[RESOLVED] Select Last Record according to max date?

[Besoup]

Hey all,

I got this SP that I am using in SQL 2000.

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


ALTER  PROCEDURE dbo.usp_Approved_Subs

@p_Status NVARCHAR(25),
@p_Year INT

AS

SELECT 
	pv.VendorName as Subcontractor, 
	pv.SentBy_Name as InvitedByName,
	pv.SentTo_Name as ContactName,
	al.ActionDate as DateApproved,
	al.fk_Action as Status,	
	al.ActionDate as ActionDate	
FROM PreQual_Vendors pv
LEFT OUTER JOIN ActionLog al ON  pv.pk_PreQual_Vendor = al.fk_PreQual_Vendor
WHERE 
	al.ExtraText = @p_Status AND DATEPART(yyyy, al.ActionDate) = @p_Year
ORDER BY  pv.VendorName
	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

This returns records but I need to make sure the record that is returning is the last record for that given year. Some contractors could recieve a new status after the returned record therefore what is returning isn't very accurate.

How would I return the max date that meets the status and year condition?

Thanks

[kevchadders]

Do you have some sort of pk field so that you could get the max value for that year, or could you use the actiondate field in some way.

e.g.

SELECT 
	pv.VendorName as Subcontractor, 
	pv.SentBy_Name as InvitedByName,
	pv.SentTo_Name as ContactName,
	al.ActionDate as DateApproved,
	al.fk_Action as Status,	
	al.ActionDate as ActionDate	
FROM PreQual_Vendors pv
LEFT OUTER JOIN ActionLog al ON  pv.pk_PreQual_Vendor = al.fk_PreQual_Vendor
WHERE 
	al.ExtraText = @p_Status AND DATEPART(yyyy, al.ActionDate) = @p_Year
	AND al.ActionDate = (
				SELECT MAX(al2.ActionDate) 
				FROM ActionLog al2
				WHERE al2.fk_PreQual_Vendor = al.fk_PreQual_Vendor
			)
ORDER BY  pv.VendorName

[riteshjain1982]

Do your ActionLog table has PK?also is ActionDate value is Incremental in your table?I mean for each new ros value of ActionDate will be always higher compare to previous row value?

[riteshjain1982]

i am too late ,but i think above query will return only one record right?

[riteshjain1982]

you can also try this if Post #3 condition holds true

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


ALTER  PROCEDURE dbo.usp_Approved_Subs

@p_Status NVARCHAR(25),
@p_Year INT

AS

SELECT 
	pv.VendorName as Subcontractor, 
	pv.SentBy_Name as InvitedByName,
	pv.SentTo_Name as ContactName,
	al.ActionDate as DateApproved,
	al.fk_Action as Status,	
	al.ActionDate as ActionDate	
FROM PreQual_Vendors pv
LEFT OUTER JOIN ActionLog al ON  pv.pk_PreQual_Vendor = al.fk_PreQual_Vendor
INNER JOIN 
(SELECT MAX(Pk1) A_PK FROM ActionLog 

WHERE 
	 ExtraText = @p_Status AND YEAR(ActionDate) = @p_Year
GROUP BY fk_PreQual_Vendor
) A2 ON A1.Pk1 = A2.A_PK
ORDER BY  pv.VendorName
	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

[Besoup]

Thanks alot both of you,

I went with Rite's final solution.

[kevchadders]

no problem..

Hey i've hit the 2 green cubes!

[Besoup]

Grats!

I need to spread some more to hook up Rite. :P

[riteshjain1982]

Grats!

I need to spread some more to hook up Rite. :P

you are making me fool since long time


congrates kevchadders