how to convert a period of D+30, D+60 etc to exact day and month
To avoid hard codding the months in the ADS we have been Creating 4 ADSs with historical customers data for Churn with period of 30 days instead of calendar month: Day-30, D-60, D-90, and creating the detection period with D+30, D+60, D+90. We are also creating a table called Customer DNA where we load the customers data from the ADSs with the scores. Now, the customer wants to do some reporting but with calendar month so rather than recreating the ADSs with calendar month I have been told that 'SysCalendar' could map the periods to the exact day of the month and allow the customer to manage the performance of the his company based on months not period of 30 days. Any hint that make the transition easy between periods and calendar month without changing the ADS? thanks. J.
Re: how to convert a period of D+30, D+60 etc to exact day and month
You can do quite a bit with the dates without using SysCalendar. For instance, to add or subtract months, you can use the ADD_MONTHS feature:
SELECT ADD_MONTHS(date_col, 2) /* this will add two months */
SELECT ADD_MONTHS(date_col, -2) /* this will subtract two months */
You can extract the year, month, or day out of the date as well with the EXTRACT function (ex. EXTRACT(MONTH FROM date_col)). I would only advise you to use SysCalendar if you need to set up some custom calendar (i.e. your "fiscal" or "calendar" dates do not align with the normal calendar) or you find that you can't do what you want using the date functions.