how to get cross time data by SQL

Mon, 31 Mar 2014 02:54:59 GMT

in the raw data i have some data is crossed in time on the same person (A)

i want to ask how to convert crossed time into one by SQL?

Re: how to get cross time data by SQL

hic — Mon, 31 Mar 2014 07:12:01 GMT

RawData:

Load Name,

TimeStamp#(Starttime,'M/D/YYYY hh:mm') as Starttime,

TimeStamp#(Endtime,'M/D/YYYY hh:mm') as Endtime

inline

[Name,Starttime,Endtime

A,1/1/2013 10:00,1/1/2013 12:00

A,1/1/2013 14:00,1/1/2013 17:00

A,1/1/2013 13:00,1/1/2013 16:00

B,1/1/2013 13:00,1/1/2013 18:00];

Data:

Load *,

If(Name=Peek(Name) and Starttime < Peek(Endtime),Peek(TimeSlotID),

Autonumber(Name & Starttime)) as TimeSlotID

Resident RawData Order By Name, Starttime;

AggregatedData:

NoConcatenate Load

Name,

Timestamp(Min(Starttime),'M/D/YYYY hh:mm') as Starttime,

Timestamp(Max(Endtime),'M/D/YYYY hh:mm') as Endtime

Resident Data

Group By Name,TimeSlotID;

Drop Table Data, RawData;