Checking for the latest date
-
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 -
at the very end it should be ) as n not a funny face
-
Try the following :
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 = 1)--@HerdId)
And hsh.StatusEffectiveDateTime = (Select Max (HSH2.StatusEffectiveDateTime) From HerdStatusHistory hsh2
Where HSH2.HerdId = hsh.HerdId)
) as n
-
Better yet:
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,
INNER JOIN HerdStatusHistory hsh ON h.Id = hsh.HerdId
INNER JOIN HerdStatusTypeLookupItem hstli ON hsh.HerdStatusTypeLookupItemId = hstli.Id
INNER JOIN Person p ON dbo.GetHerdContact(h.Id,'L',cast(convert(varchar(10),getdate(),101) as datetime)) = p.Id
INNER JOIN Address a ON p.Id = a.ParentObjectId
AND a.ParentObjectTypeInd = 'P'
AND a.AddressTypeLookupItemId = 1
AND a.DeletedDate IS NULL
INNER JOIN (SELECT H.HerdId, MAX(H.StatusEffectiveDateTime) AS StatusEffectiveDateTime
FROM HerdStatusHistory H GROUP BY H.HerdId) AS hsh2
ON hsh.HerdId = hsh2.HerdId
AND hsh.StatusEffectiveDateTime = hsh2.StatusEffectiveDateTime
WHERE h.Id = @HerdId) as n
Andy
-
Don't you think you're mixing presentational issues in here very heavily?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply