Having Min(time)

[#1]

Hi,

I have to add a new flag field to a table

I got a table with

date,who,place,Time,key,Type.

I'd like to add 'like 'First_appointment'.' which could be true or false

It should mean:

In the day 13/01/2012 in the place A, which is the first appointment of that day in that place?

In oracle you should do smt like:

select a.date,a.where,a.key,1 as First_appointment

from TAB a

where not exists (select 1 from TAB b where a.date=b.date and a.place = b.place and b.time >a.time)

Do you think you can handle it?

many thanks

[#2]

You can not extract the data the way you need, as in the example?

[Gysbert_Wassenaar · Accepted solution]

Something like this

T1:

load autonumber(place&date&min(Time)) as Key

from ...somewhere...

group by place, date,Time;

left join

load autonumber(place&date&Time) as Key,

date,who,place,Time,key,Type

from ...somewhere...;

drop field Key;

[#4]

Hi,

see attached files.

Hope it helps

regards

Giampiero

[#5]

No, I can't use SQL, because the DB used as source is a view which has already undergone an ETL process.

[#6]

Without using aggr in the pivot.

[#7]

I cannot make it works properly, because with autonumber I've got different increasing number in T1 and in the left join (ex T1: 1,2,3  in the second table T2(if I cancel the left join, I've got 4,5,6).

Can you tell me where is my mistake?

Without the autonumber it works with  place,date and min(time) used as multiple keys  in T1