dates in between

[alec1982]

have a table with the following month close dates

8/28/2017

9/21/2017

10/28/2017

need to add a new column with all dates in between so I can see all dates related to the month close dates as follow

8/28/2017     8/1/2017

8/28/2017     8/2/2017

8/28/2017     8/3/2017

...

8/28/2017     8/28/2017

9/21/2017     8/29/2017

9/21/2017     8/30/2017

9/21/2017     8/31/2017

9/21/2017     9/1/2017

...

9/21/2017     9/21/2017

and so on..

any quick solution you can share?

[Anil_Babu_Samineni]

Like this?

SET DateFormat='M/D/YYYY';

LOAD FieldName, MonthStart, Date(MonthStart + IterNo() - 1) as Date

While Date(MonthStart + IterNo() - 1) <= FieldName;

LOAD *, MonthStart(FieldName) as MonthStart Inline [

FieldName

8/28/2017

9/21/2017

10/28/2017

];

[alec1982]

almost right but it is not. We dont need to use month start. the first date here on my sample happen to be a month start but if you look at the dates between august and September.. month start doesnt work. let me know if you know how to adjust the logic

[Anil_Babu_Samineni]

Not sure, I followed. Can you make output for one date to test

[alec1982]

if you take this example. if you notice when the first column value is 9/21/2017, the second column shows next value (8/29/2017) which is next value after 8/28/2017 showing above 9/21/2017

8/28/2017     8/28/2017

9/21/2017     8/29/2017

9/21/2017     8/30/2017

9/21/2017     8/31/2017

9/21/2017     9/1/2017

...

9/21/2017     9/21/2017

[Anil_Babu_Samineni]

Still not sure, When i say i am not understand your output. You shouldn't copy paste from previous one. I would request you to share sample output in excel from given data..

[alec1982]

the sample I provided is very clear.. sorry you cannot understand it and thank you or your input. will wait for others to take a look.

thanks again.

[Anil_Babu_Samineni]

Yes, We will wait for other reply. You are not provided answer for 10/28/2017

Are you looking for this? It captures from providing data as 8,9,10 months whole data..

LOAD FieldName, MonthStart, Date(MonthStart + IterNo() - 1) as Date

While Date(MonthStart + IterNo() - 1) <= MonthEnd(FieldName);

[jonathandienst]

How about interval matching the date range from previous month close(+1) to month close:

// Load the month close dates

MonthCloses:

LOAD Date(Date#(MonthClose, 'M/dd/yyyy')) as MonthClose

INLINE

[

  MonthClose

  8/28/2017

  9/21/2017

  10/28/2017

];

// Create a match table for the month ranges

Match:

LOAD Alt(Previous(MonthClose) + 1, 0) as FromDate,

  MonthClose as ToDate

Resident MonthCloses;

Fact:  // load your fact table with the date field....

LOAD

  ...

  Date,

  ...

;

// Apply the month table to the fact

Left Join(Fact)

IntervalMatch(Date)

LOAD FromDate, ToDate as CloseDate

Resident Match;

// Cleanup

DROP Field FromDate;

DROP Tables MonthCloses, Match;