0

I have a task to find sales forecast for upcoming dates in current month. If the current month is May we have to calculate sales using the formula May 11 = (May10+(Map10*(1-(Apr10/Apr11 + Mar10/Mar11 + Feb10/Feb11)/3))) and so on till end of current month. But when it comes to May 30 we have a problem May 30 = (May29+(May29*(1-(Apr29/Apr30 + Mar29/Mar30 + Feb??/Feb??)/3))) So inorder to avoid this we came up with a logic enter image description here

Now as the current month has 31 days we need to calculate additional values for Feb(28) and Apr(30). So for Feb we will move feb 26, 27, 28 to Feb 29, 30, 31 and for Feb 26 we will do average of Feb24 and Feb25, Feb 27 will be Average of Feb25 and Previously calculated Feb26 and so on.

Similarly if we are forecasting for Jun we will remove march and april 28 value to make it 30 days even.

My initial thought is to normalize the all months to 31 days and then depending on month we are forecasting we can remove unwanted dates?

How do we write this in sql?

Chakara Varthy
  • 1
  • 2
    Edit question to provide sample data as text table, add a tag for database used. – June7 May 12 '21 at 00:52
  • With respect, why do you assume you need dynamic SQL and a cursor? Why are you writing your own date arithmetic when your DBMS undoubtedly has a library of such functions? In SQL Server, for example, subtracting a month from Mar 31 will return Feb 28 or 29 as appropriate. Why is hysteresis needed for only some calculations? I also feel your requirements need questions answered such as if your business logic should be based on weeks rather than months, if weekends or holidays count, or if the compared dates should be relative to start/end of a month rather than simple numeric value. – Tim Lehner May 12 '21 at 01:37
  • @TimLehner For monthly calculations I had used cursor, now that daily values more complicated I thought Dynamic SQL will be more helpful. Nevertheless I just input my formula they had given in excel to replicate. Our logic based on all calendar dates. Formula is May12 Forecast = Previousdate + (Previousdate * (1 - (Average(PreviousMonthPreviousdate/Previousmonthsamedate)+(Previous2MonthPreviousdate/Previous2monthsamedate)+(Previous3MonthPreviousdate/Previous3monthsamedate)))) If previous date value is not available then we need to use value which we have forecasted. – Chakara Varthy May 12 '21 at 14:23

0 Answers0