How can I generate days between differents type ?

[suzel404]

Hi all,

How can I generate all days between differents TYPE_ID ?

STORE_ID      STATE_ID   TYPE_ID   DATE_START   DATE_END

ST001	1	10	01/01/2015	08/01/2015
ST001	1	45	08/01/2015	12/01/2015
ST001	1	60	12/01/2015	22/02/2015
ST001	2	100	22/02/2015	02/03/2015
ST001	2	101	02/03/2015	08/03/2015
ST001	2	102	08/03/2015	15/03/2015
ST001	4	200	15/03/2015	25/03/2015
ST001	4	210	25/03/2015	31/03/2015
ST001	4	220	31/03/2015	05/04/2015
ST001	5	300	05/04/2015	20/04/2015
ST001	6	730	20/04/2015	17/05/2015
ST001	6	735	17/05/2015	22/05/2015
ST001	7	900	22/05/2015	28/05/2015
ST001	8	802	28/05/2015	13/06/2015
ST001	8	805	13/06/2015	18/07/2015
ST001	9	700	18/07/2015	-

I want to generate all days between differen TYPE_ID until TYPE_ID <> 9

How can I achieve it ?

Many thanks for your help.

Regards

[sunny_talwar]

Not sure I understand what the required output is, can you please elaborate

[#3 · Accepted solution]

As per my understanding you need to generate the all date between DATE_START and DATE_END.

Please find the below:

Source:

Load

     STORE_ID,

      STATE_ID,

      TYPE_ID,

      DATE_START,

      DATE_END

From source_Table;

Final:

LOAD

     STORE_ID,

      STATE_ID,

      TYPE_ID,

      DATE_START + IterNo() - 1 AS Date_ID

Resident Source

Where Not Isnull(DATE_END)

While iterNo() <= (DATE_END - DATE_START)+1

;

[suzel404]

Hi Sunny,

I want to generate a master calendar with all days between TYPE_ID

Generate all day from 01/01/2015 to 12/01/2015 until TYPE_ID <> 9.

When I click on ST001 and state = 1 and type_id = 10 I can see all days from 01/01/2015 to 08/01/2015 and for each type_id.

ST001	1	10	01/01/2015	08/01/2015
ST001	1	45	08/01/2015	12/01/2015

[suzel404]

Many thanks for help.

Thanks dathu.qv