Calendar for MTD, Day and YTD flag

[renjithpl]

HI All,

I have a challenging question,

I want to create a calendar table with day, MTD and YTD as flag,

i want a table like below:

caldate	flag	salesdate
01 January 2012	Day	01 January 2012
02 January 2012	Day	02 January 2012
03 January 2012	Day	03 January 2012
01 January 2012	MTD	01 January 2012
02 January 2012	MTD	01 January 2012
02 January 2012	MTD	02 January 2012
03 January 2012	MTD	01 January 2012
03 January 2012	MTD	02 January 2012
03 January 2012	MTD	03 January 2012

i want to create this for 365 days,

Explanation:

if the cal date is 1st jan for flag Day, sales date appears same as 1st jan, (for 365 days same as caldate)

if the cal date is 1st jan for flag MTD, sales date appears, same as 1st jan, and 2nd jan for flag MTD, sales date appears 1st and 2nd jan

the above table should include for YTD as well,

basically in my application, i will have a list box as Day, MTD and YTD, it should display Day, MTD and YTD sales without using if condition.

please let me know if you need more clarity on this question.

Guess we can use join to get this done, but i am not able to get the correct result.

Regards

Renjith

[whiteline]

Hi.

I think your question is similar to the accumulation calendar.

There are common load steps:

1) Create a table with distinct Date field.

2) Self-join without a key field (Date as AccumDate). << it gives you all combinations Date-AccumDate.

3) Discard combinations by condition AccumDate<=Date using where statement.

In your case you just have to add flag field in all steps.