QUERY TROUBLE

I am using the last updated date field to give me the dates I want, but I
want to narrow that search to give me only the greatest date per project id.
Here is my query:
SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
POReqHdr.CuryReqTotal, POReqHdr.User2,
POReqHdr.LUpd_DateTime, PJPROJ.project_desc
FROM POReqHdr LEFT OUTER JOIN
PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID = 'HE017')
AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH)
I know I need to use "max(lupd_datetime)" somewhere but I am not sure how.
The is in a sql report, by the way.Hi,
Inside the sub query you can use the MAX function. See the below sample:-
select projid, lupd_datetime from projects x
where lupd_datetime >= (select max(y.lupd_datetime) from projects y
where y.projid = x.projid)
Thanks
Hari
SQL Server MVP
"Ben Watts" wrote:

> I am using the last updated date field to give me the dates I want, but I
> want to narrow that search to give me only the greatest date per project i
d.
> Here is my query:
> SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
> POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
> POReqHdr.CuryReqTotal, POReqHdr.User2,
> POReqHdr.LUpd_DateTime, PJPROJ.project_desc
> FROM POReqHdr LEFT OUTER JOIN
> PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
> WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID = 'HE017')
> AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH)
> I know I need to use "max(lupd_datetime)" somewhere but I am not sure how.
> The is in a sql report, by the way.
>
>|||Ben Watts wrote:
> I am using the last updated date field to give me the dates I want, but I
> want to narrow that search to give me only the greatest date per project i
d.
> Here is my query:
> SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
> POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
> POReqHdr.CuryReqTotal, POReqHdr.User2,
> POReqHdr.LUpd_DateTime, PJPROJ.project_desc
> FROM POReqHdr LEFT OUTER JOIN
> PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
> WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID = 'HE017')
> AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH)
> I know I need to use "max(lupd_datetime)" somewhere but I am not sure how.
> The is in a sql report, by the way.
>
I have a short post on my web site explaining how to do this, but the
site is currently being reconstructed. You can find the original post
in Google's cache by searching for
"www.realsqlguy.com/twiki/bin/view/RealSQLGuy/FindingTheLatestValue"
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I put that query in and it told me there was an error on the report server.
Here is my last query.
SELECT POReqHdr.ProjectID, POReqHdr.User5, POReqHdr.PONbr,
POReqHdr.CuryTotalExtCost, POReqHdr.Descr, POReqHdr.CuryPrevPOTotal,
POReqHdr.CuryReqTotal, POReqHdr.User2,
POReqHdr.LUpd_DateTime, PJPROJ.project_desc
FROM POReqHdr LEFT OUTER JOIN
PJPROJ ON POReqHdr.ProjectID = PJPROJ.project
WHERE (POReqHdr.User5 IN (@.reason)) AND (POReqHdr.ProjectID = 'HE017')
AND (MONTH(POReqHdr.LUpd_DateTime) = @.MONTH) AND
(POReqHdr.LUpd_DateTime >=
(SELECT MAX(LUpd_DateTime) AS Expr1
FROM POReqHdr AS POReqHdr_1
WHERE (ProjectID = PJPROJ.project)))
"Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
news:9330F636-358C-462C-8030-1CFA88035CC9@.microsoft.com...[vbcol=seagreen]
> Hi,
> Inside the sub query you can use the MAX function. See the below sample:-
> select projid, lupd_datetime from projects x
> where lupd_datetime >= (select max(y.lupd_datetime) from projects
> y
> where y.projid = x.projid)
> Thanks
> Hari
> SQL Server MVP
> "Ben Watts" wrote:
>|||never mind, it worked. Thanks very much
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44E31BD4.60003@.realsqlguy.com...
> Ben Watts wrote:
> I have a short post on my web site explaining how to do this, but the site
> is currently being reconstructed. You can find the original post in
> Google's cache by searching for
> "www.realsqlguy.com/twiki/bin/view/RealSQLGuy/FindingTheLatestValue"
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com