[RESOLVED] Select Last Record according to max date?

Hey all,

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

Code:

---

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

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.

Code:

---

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

---

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?

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

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

Code:

---

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

---

Thanks alot both of you,

I went with Rite's final solution.

no problem..

Hey i've hit the 2 green cubes! :)

Grats!

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

Quote:

---

Originally Posted by Besoup

Grats!

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

---

you are making me fool since long time :cry: :p


congrates kevchadders :afrog: