Get Earliest date and Last date then subtract 1

[din2005]

Hi all,

What i would like to do in query is to get the earliest date for patient treatment (patient may have 1 to 5 or greater treatments) and the final date of the patient treatment then subtract them (using datediff which i can do).

I have over 5000 records is it possible to display all the dates in one table. i.e create an extra field or something. could someone give me an example or some criteria.

Many thanks

 

[PHV]

Something like this ?
SELECT [patient treatment], Min([date field]) AS EarliestDate, Max([date field]) AS FinalDate, Int(Max([date field])-Min([date field])) AS CountOfDays
FROM yourTable
GROUP BY [patient treatment];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244