Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.