Start and End Date from Multiple Rows

[sjhussain]

Dear All,

Hope everyone is fine.

We have a HLR Table consisting of IMEI_NO, IMSI_NO, DATE.  What we want to do is for each combination of IMEI_NO and IMSI_NO have a start and end date.

The start date would be the first occurance of the IMEI + IMSI comination and then the end date would be today's date.

For example:

IMEI_NO, IMSI_NO, DATE

IMEI_1, IMSI_1, Jan 12, 2016

IMEI_1, IMSI_1, Jan 16, 2016

IMEI_1, IMSI_1, Feb 13, 2016

IMEI_2, IMSI_2, Jan 15, 2016

IMEI_2, IMSI_3, Jan 31, 2016

IMEI_2, IMSI_2, Feb 18, 2016

Resultant should be

IMEI_NO, IMSI_NO, START_DATE, END_DATE

IMEI_1, IMSI_1, JAN 12, 2016, MAR 5, 2016

IMEI_2, IMSI_2, Jan 15, 2016, MAR 5, 2016

IMEI_2, IMSI_3, Jan 31, 2016, MAR 5, 2016

Will appreciate if someone can assist us.

Thanks.

[swuehl]

Make sure your dates are read in correctly as dual values:

Get the Dates Right

Why don’t my dates work?

Then something like this should work

LOAD IMEI_NO,

           IMSI_NO,

          Date(Min(DATE)) as START_DATE,

           Today(1) as END_DATE

RESIDENT HLR

GROUP BY IMEI_NO, IMSI_NO;

DROP TABLE HLR;

[Digvijay_Singh]

Like this -

T1:

Load IMEI_NO, IMSI_NO,Date(Date#(DATE,'MMM DD, YYYY'),'MMM DD, YYYY') as DATE;

Load * inline [

IMEI_NO, IMSI_NO, DATE

IMEI_1, IMSI_1, "Jan 12, 2016"

IMEI_1, IMSI_1, "Jan 16, 2016"

IMEI_1, IMSI_1, "Feb 13, 2016"

IMEI_2, IMSI_2, "Jan 15, 2016"

IMEI_2, IMSI_3, "Jan 31, 2016"

IMEI_2, IMSI_2, "Feb 18, 2016" ];

F1:

Load

  IMEI_NO, IMSI_NO,

  Min(Date(Date#(DATE,'MMM DD, YYYY'),'MMM DD, YYYY')) as START_DATE,

  DATE(Today(),'MMM DD, YYYY') as END_DATE

Resident T1

Group By IMEI_NO, IMSI_NO;

Drop table T1;