MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### table based on another table

• Follow

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

 0

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
> 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
>

 0

1 Replies
211 Views

8/16/2012 3:52:05 AM