Getting latest date -- sql server new users

Nat Johnson
6/7/2005 8:37:02 PM

I have a query that is returning two rows of data. I need to only get the
one with the newest date in it. I gather I can use MAX to do this. Not sure
where or how tho.

here is my code for my letter(report) so far, I need to get the MAX of the
hsh.StatusEffectiveDateTime, any help would be greatly appreciated...

SELECT 'Dear ' + n.FirstName AS Salutation,
'RE: BOVINE TB STATUS OF YOUR HERD NUMBER ' + CONVERT(varchar, n.HerdId) AS
Regarding,
'This letter is to advise that the Bovine Tb Status of the above herd has
been altered.' AS FirstParagraph,
'The new Tb status is ' + n.HerdStatus + '.' AS SecondParagraph,
'You must use this status in any declaration of Tb status reguarding this
herd. This status may only be changed by written notice from the Animal
Health Board. ' AS ThirdParagraph,
n.*

FROM (Select h.Id as HerdId, p.FirstName, p.FullAddressName,
a.AddressLine1, a.AddressLine2, a.Town, a.PostCode, p.FullLegalName,
hstli.Name AS HerdStatus, hsh.StatusEffectiveDateTime

FROM Herd h, HerdStatusHistory hsh, HerdStatusTypeLookupItem hstli, Person
p, Address a
where h.Id = hsh.HerdId
and hstli.Id = hsh.HerdStatusTypeLookupItemId
and p.Id = dbo.GetHerdContact
(h.Id,'L',cast(convert(varchar(10),getdate(),101) as datetime))
and a.ParentObjectId = p.Id AND a.ParentObjectTypeInd = 'P'
and (a.AddressTypeLookupItemId = 1)
and (a.DeletedDate IS NULL)
and (h.Id = @HerdId)
) as n

Cristian Lefter
6/8/2005 11:17:13 AM

Hi Nat,
Your script will look like:
[code]
SELECT 'Dear ' + n.FirstName AS Salutation,
'RE: BOVINE TB STATUS OF YOUR HERD NUMBER ' + CONVERT(varchar, n.HerdId) AS
Regarding,
'This letter is to advise that the Bovine Tb Status of the above herd has
been altered.' AS FirstParagraph,
'The new Tb status is ' + n.HerdStatus + '.' AS SecondParagraph,
'You must use this status in any declaration of Tb status reguarding this
herd. This status may only be changed by written notice from the Animal
Health Board. ' AS ThirdParagraph,
n.*

FROM (
Select TOP 1

h.Id as HerdId, p.FirstName, p.FullAddressName,
a.AddressLine1, a.AddressLine2, a.Town, a.PostCode, p.FullLegalName,
hstli.Name AS HerdStatus, hsh.StatusEffectiveDateTime

FROM Herd h, HerdStatusHistory hsh, HerdStatusTypeLookupItem hstli, Person
p, Address a
where h.Id = hsh.HerdId
and hstli.Id = hsh.HerdStatusTypeLookupItemId
and p.Id = dbo.GetHerdContact
(h.Id,'L',cast(convert(varchar(10),getdate(),101) as datetime))
and a.ParentObjectId = p.Id AND a.ParentObjectTypeInd = 'P'
and (a.AddressTypeLookupItemId = 1)
and (a.DeletedDate IS NULL)
and (h.Id = @HerdId)
ORDER BY hsh.StatusEffectiveDateTime DESC
) as n
[/code]

Cristian Lefter, SQL Server MVP

[quoted text, click to view]

sql server new users : Getting latest date