Creation of dates from particular date to till today.

[berryandcherry6]

In my task i want to load a script which has to create dates. Date should start from 01/01/2012 and end till today. For each date i need to find weekend, year, weekday of a date.

How could i achieve this.Any kind of help is appreciated.

[sunny_talwar · Accepted solution]

Try this to create dates:

LOAD Date(MakeDate(2012, 1, 1) + RecNo() - 1) as Date

AutoGenerate Today() - MakeDate(2012, 1, 1) + RecNo() + 1;

[ramoncova06]

you can use a master calendar

The Master Calendar

Creating A Master Calendar

[berryandcherry6]

Hi stalwar1

Thanks for reply,

Here Its creating only january month dates of every year till 2016. I need to display all 12 months of every year till 2016.

Regards,

Supriya

[berryandcherry6]

Hi ramoncova06,

Thanks for your reply, My requirement is to generate date/calender from given date to till today.

Yes i went through link you have given but that is for mapping of tables(which is like joins in SQL),but how could i apply this concept to create dates.If you provide some code or sample regarding this will be really helpfull

Regards,

Supriya

[#6]

Try with this one you just need to set your date on  the variables and on the first field of the table MasterCalendar.

Hope this work for you.

LET varMinDate = NUM(Peek('YourDate',0,'YourTable'));

LET varMaxDate = NUM(Peek('YourDate',-1,'YourTable'));

LET varToday = Num(today());

DateField:

LOAD

  $(varMinDate) + RowNo()-1 AS Num,

  date($(varMinDate) + rowno() -1) AS TempDate,

  rowno()

  AUTOGENERATE

  $(varMaxDate) - $(varMinDate) +1;

//Master Calendar

MasterCalendar:

LOAD

   TempDate AS YourDate,

   Week(TempDate) AS Week,

   Day(TempDate) AS Day,

   Year(TempDate) AS Year,

   Month(TempDate) AS Month,

   WeekDay(TempDate) AS WeekDay,

   'Q' & Ceil(Month(TempDate)/3) AS Quarter

Resident DateField

Order By TempDate ASC;

DROP TABLE DateField;

[sunny_talwar]

I am seeing all dates. Check the attached out

[ramoncova06]

here goes what I normally use 

LET vMinDate = '01/01/2012'

   LET vMaxDate = Today();

CalendarTemp:

LOAD $(vMinDate) + (RecNo()) + (IterNo()-1) AS AddedDate

AUTOGENERATE 1

WHILE Num($(vMinDate) + IterNo() -1) < Num($(vMaxDate));

Calendar:

load

date(AddedDate) as [Calendar Date],

Month(AddedDate) as [Calendar Month],

Year(AddedDate) as [Calendar Year],

WeekEnd(AddedDate) as [Calendar Week Ending],

weekyear(AddedDate) as [Calendar WeekYear#],

QuarterName(AddedDate) as [Calendar Year Quarter],

date(floor(MonthEnd(AddedDate))) as [Calendar Month End],

date(floor(MonthEnd(AddedDate,-1))) as [Previous Calendar Month End],

date(floor(MonthStart(AddedDate))) as [Calendar Month Start],

date(floor(MonthStart(AddedDate,-1))) as [Previous Calendar Month Start],

'Q' & Ceil(Month(AddedDate)/3) as [Calendar Quarter]

Resident CalendarTemp;

drop table CalendarTemp;

[berryandcherry6]

Hi raul.marquez,

Thankyou for your reply,

In your answer you are mentioning to give date and tablename, why should i give tablename ,as i want to create list of dates ex: from 01/01/2012 to today().Date list  should be created without depending on any table.

Regards,

Supriya

[berryandcherry6]

It showning me lines fetched is 0,and no values are being disaplyed. I have attached snapshot please look into that.