How to Create Week

[alvinford]

Hi ,

Request your help to achieve this...

I have the following Data.

Year	City	StartDate	EndDate
2013	AZ	2/8/2014	15/8/2014
2013	CA	12/10/2013	1/11/2013
2013	AX	9/8/2013	22/8/2013

Would like to derive the Weeks between StartDate and End Date. The First 7 Days between StartDate and EndDate will be considered as Week1 and Next 7 Days will be Week 2 and So on for each City. The Final Output will be as below.

I have highlighted the City and Week for better understanding indicating every City has Week1 and so on..

Thank You in Advance...

Start Date	End Date	Date	Year	City	Week
12/10/2013	20/12/2013	12/10/2013	2013	CA	Week1
12/10/2013	20/12/2013	13/10/2013	2013	CA	Week1
12/10/2013	20/12/2013	14/10/2013	2013	CA	Week1
12/10/2013	20/12/2013	15/10/2013	2013	CA	Week1
12/10/2013	20/12/2013	16/10/2013	2013	CA	Week1
12/10/2013	20/12/2013	17/10/2013	2013	CA	Week1
12/10/2013	20/12/2013	18/10/2013	2013	CA	Week1
12/10/2013	20/12/2013	19/10/2013	2013	CA	Week2
12/10/2013	20/12/2013	20/10/2013	2013	CA	Week2
12/10/2013	20/12/2013	21/10/2013	2013	CA	Week2
12/10/2013	20/12/2013	22/10/2013	2013	CA	Week2
12/10/2013	20/12/2013	23/10/2013	2013	CA	Week2
12/10/2013	20/12/2013	24/10/2013	2013	CA	Week2
12/10/2013	20/12/2013	25/10/2013	2013	CA	Week2
12/10/2013	20/12/2013	26/10/2013	2013	CA	Week3
12/10/2013	20/12/2013	27/10/2013	2013	CA	Week3
12/10/2013	20/12/2013	28/10/2013	2013	CA	Week3
12/10/2013	20/12/2013	29/10/2013	2013	CA	Week3
12/10/2013	20/12/2013	30/10/2013	2013	CA	Week3
12/10/2013	20/12/2013	31/10/2013	2013	CA	Week3
12/10/2013	20/12/2013	01/11/2013	2013	CA	Week3
02/08/2014	15/08/2014	02/08/2014	2013	AZ	Week1
02/08/2014	15/08/2014	03/08/2014	2013	AZ	Week1
02/08/2014	15/08/2014	04/08/2014	2013	AZ	Week1
02/08/2014	15/08/2014	05/08/2014	2013	AZ	Week1
02/08/2014	15/08/2014	06/08/2014	2013	AZ	Week1
02/08/2014	15/08/2014	07/08/2014	2013	AZ	Week1
02/08/2014	15/08/2014	08/08/2014	2013	AZ	Week1
02/08/2014	15/08/2014	09/08/2014	2013	AZ	Week2
02/08/2014	15/08/2014	10/08/2014	2013	AZ	Week2
02/08/2014	15/08/2014	11/08/2014	2013	AZ	Week2
02/08/2014	15/08/2014	12/08/2014	2013	AZ	Week2
02/08/2014	15/08/2014	13/08/2014	2013	AZ	Week2
02/08/2014	15/08/2014	14/08/2014	2013	AZ	Week2
02/08/2014	15/08/2014	15/08/2014	2013	AZ	Week2
09/08/2014	10/10/2014	09/08/2014	2014	AX	Week1
09/08/2014	10/10/2014	10/08/2014	2014	AX	Week1
09/08/2014	10/10/2014	11/08/2014	2014	AX	Week1
09/08/2014	10/10/2014	12/08/2014	2014	AX	Week1
09/08/2014	10/10/2014	13/08/2014	2014	AX	Week1
09/08/2014	10/10/2014	14/08/2014	2014	AX	Week1
09/08/2014	10/10/2014	15/08/2014	2014	AX	Week1
09/08/2014	10/10/2014	16/08/2014	2014	AX	Week2
09/08/2014	10/10/2014	17/08/2014	2014	AX	Week2
09/08/2014	10/10/2014	18/08/2014	2014	AX	Week2
09/08/2014	10/10/2014	19/08/2014	2014	AX	Week2
09/08/2014	10/10/2014	20/08/2014	2014	AX	Week2
09/08/2014	10/10/2014	21/08/2014	2014	AX	Week2
09/08/2014	10/10/2014	22/08/2014	2014	AX	Week2

Regards,

Alvin.

[hic · Accepted solution]

You can use the method described in Creating Reference Dates for Intervals.

Load Year, City, StartDate, EndDate

          Div( IterNo() + 6, 7 ) as ReferenceWeek,

          Date( StartDate + IterNo() – 1 ) as ReferenceDate

          From SourceTable

          While IterNo() <= EndDate - StartDate + 1 ;

HIC

[jmmayoral3]

Try this.

Dates:

LOAD * INLINE [

Year, City, StartDate, EndDate

2013, AZ, 2/8/2014, 15/8/2014

2013, CA, 12/10/2013, 1/11/2013

2014, AX, 9/8/2014, 22/8/2014

];

NOCONCATENATE

Temp1:

LOAD

     *,

     date(StartDate + IterNo()-1,'DD/MM/YYYY') as Tdate,

     'Week '&ceil(interval(date(StartDate + IterNo())-StartDate,'D')/7) as Week  

RESIDENT Dates

WHILE date(StartDate + IterNo()) <= date(EndDate+1)

;