Selecting the max date

[gjmac2]

I am having trouble getting my view to just display the maximum date from a set of records. The SQL for the view is as follows:

SELECT DISTINCT
dbo.LabProfile.SampleDateTime AS SamplDateTime, dbo.LabProfile.CaseID AS Expr1, dbo.LabItem.[Value] AS Expr2,
dbo.LabConfig.LabName AS Expr3, dbo.VW_0LGCASEID.DonorTracCaseID AS Expr4
FROM dbo.LabConfig FULL OUTER JOIN
dbo.VW_0LGCASEID FULL OUTER JOIN
dbo.LabProfile ON dbo.VW_0LGCASEID.ID = dbo.LabProfile.CaseID FULL OUTER JOIN
dbo.LabItem ON dbo.LabProfile.ID = dbo.LabItem.LabProfileID ON dbo.LabConfig.ID = dbo.LabItem.LabConfigID
WHERE (dbo.LabConfig.LabName = 'Creatinine') AND (dbo.VW_0LGCASEID.DonorTracCaseID = 77279) OR
(dbo.LabConfig.LabName = 'bun') AND (dbo.VW_0LGCASEID.DonorTracCaseID = 77279)

The result is showing all dates for creatine and bun. I need the maximum dates for these records.

I'm a noob, so any help will be appreciated.

 

[r937]

i suspect you really want INNER joins, not FULL OUTER

SELECT MAX(P.SampleDateTime) AS MaxDateTime 
     , P.CaseID
     , I.[Value]
     , C.LabName
  FROM dbo.VW_0LGCASEID AS O
INNER 
  JOIN dbo.LabProfile AS P
    ON P.CaseID = O.ID
INNER 
  JOIN dbo.LabItem AS I
    ON I.LabProfileID = P.ID
INNER 
  JOIN dbo.LabConfig AS C
    ON C.ID = I.LabConfigID
   AND C.LabName IN ('Creatinine','bun')
 WHERE O.DonorTracCaseID = 77279
GROUP
    BY P.CaseID
     , I.[Value]
     , C.LabName

r937.com | rudy.ca

 

[gjmac2]

Thanks alot for the code.

However, the results I'm getting look like this:

1/14/2008 5:20:00 AM {DF25A6CF-808E-4F54-8558-97D6A0ABCB33} 1.5 BUN
1/12/2008 8:30:00 AM {DF25A6CF-808E-4F54-8558-97D6A0ABCB33} 11 BUN
1/13/2008 5:59:00 PM {DF25A6CF-808E-4F54-8558-97D6A0ABCB33} 7 BUN
1/12/2008 8:30:00 AM {DF25A6CF-808E-4F54-8558-97D6A0ABCB33} 1.3 Creatinine
1/13/2008 5:59:00 PM {DF25A6CF-808E-4F54-8558-97D6A0ABCB33} 1.4 Creatinine
1/14/2008 5:20:00 AM {DF25A6CF-808E-4F54-8558-97D6A0ABCB33} 1.5 Creatinine

What I am looking for is to get the last date for Creatinine and BUN, which in this case is the entries for 1/14/2008. Must be something I am missing.

 

[HarleyQuinn]

It's because you've got extra fields in the display (and obviously group by). What you're getting is the max dates for each. If, for example, you remove I.[Value] that should illustrate my point.

I'd probably make an inner query to return most of the data and then link that back into the main query using the fields returned in the inner query, then add the I.[Value] back into the outer select. I think that might make sense...

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[ca8msm]

So what should the results look like? Show the records that you want to see like you have done above.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[gjmac2]

This is what i am looking for:


{DF25A6CF-808E-4F54-8558-97D6A0ABCB33} 1/14/2008 5:20:00 AM 1.5 Creatinine 77279
{DF25A6CF-808E-4F54-8558-97D6A0ABCB33} 1/14/2008 5:20:00 AM 1.5 BUN 77279

I want the results from the last sample date, in this case from 01/14/2008.

 

[navigator703]

I have a similar situation at the moment. I have tried using a derived table. I would suggest the same and see what you come up with.

~ a journey of a thousand miles must begin with a single step ~