Hi,

I have a table with several columns but the most important ones are the two 
holdiing the following data:
EnnRefNr	Datum
1017100	2010-04-18 15:24:40.000
1017100	2010-05-18 14:26:44.000
1017115	2010-04-20 14:53:56.000
1017115	2010-04-21 10:23:26.000

How can I create a table based on this data like:
1017100   2010-04-18 15:24:40.000  2010-05-18 14:26:44.000  xxx
1017115   2010-04-20 14:53:56.000  2010-04-21 10:23:26.000  xxx

where xxx is the number of hours between the two dates?

I've been playing all afternoon the get something, but I can't find it.

rg,
Eric

To give you exactly what you need we may require more information (like is 
there always exactly two rows for each EnnRefNr (and if there isn't what do 
you want done), and exactly what do you mean by the number of hours between 
the two times, for example, if the first time is 1:53 PM and the second time 
is 2:17 PM, is that 0 hours, 1 hour, some fractional number of hours, etc).

The following assumes there are always two rows for each EnnRefNr and you 
want the number of hours to be the number of complete hours that have past 
between the two values.

Select EnnRefNr, Min(Datum), Max(Datum), DateDiff(second, Min(Datum), 
Max(Datum))/3600
From YourTable
Group By EnnRefNr;

Tom

"Eric" <Eric@discussions.microsoft.com> wrote in message 
news:D541373F-9F2E-4773-AD67-73862B630189@microsoft.com...
> Hi,
>
> I have a table with several columns but the most important ones are the 
> two
> holdiing the following data:
> EnnRefNr Datum
> 1017100 2010-04-18 15:24:40.000
> 1017100 2010-05-18 14:26:44.000
> 1017115 2010-04-20 14:53:56.000
> 1017115 2010-04-21 10:23:26.000
>
> How can I create a table based on this data like:
> 1017100   2010-04-18 15:24:40.000  2010-05-18 14:26:44.000  xxx
> 1017115   2010-04-20 14:53:56.000  2010-04-21 10:23:26.000  xxx
>
> where xxx is the number of hours between the two dates?
>
> I've been playing all afternoon the get something, but I can't find it.
>
> rg,
> Eric
>