1

I have a table which holds a list of transactions.

Task: To estimate the next transaction amount.

Problem: The actual payment periods for each rows is a varible, which can be weekly, monthly or anything choosen by the end user.

To estimate the next payment, based on previous data, can anyone suggest a good method?

At the moment I basically take the figure back to the daily amount then multiple by period i.e. week/month/q/year. Then given the history, choose the result that has the highest incidence (count).

This does not generate an accuarate estimations due to payments within payments that I dont need to care about i.e. £100 real payment but +20 for addition charges that are irrelevant.

Another way is to calculate the average,std,varience between payments then choose the highest probability.

Problem is, i've been unable to code this in SQL. 

SELECT [Identifier]
      ,[DateTranEntered]
      ,[Type]
      ,[TranDateFrom],
      ,[TranDateTo]
      ,[Amount]
      ,[ReferenceForTran]
      ,[CreatedDate]
  FROM .[TranTable]

Perhaps something with recursion through the table and calculate every transaction daily amount then with the variance, incidence - choose from the last 'x' what the estimate guess is ?

Problem is I have gotten stuck with the resurive query for this.

Any thoughts about this?

user3707945
  • 45
  • 1
  • 7
  • Pick up a statistics text book and start learning about regression. Then you can move on to other types of models, such as decision trees and neural networks. This is not a simple SQL query. – Gordon Linoff Jul 08 '14 at 14:56
  • check out this question... http://stackoverflow.com/questions/2536895/are-there-any-linear-regression-function-in-sql-server – Randy Jul 08 '14 at 15:09
  • I havern't used statistical maths to anywhere near this in 7 years. I could spend weeks learning! Is there any simpler resolution which, minus accuracy, will be more accurate than just returning the highest incidence in the past 'x' payments. If not then I'll stick to my original idea. – user3707945 Jul 08 '14 at 15:12

1 Answers1

1

SQL Server Analysis services has a suite of data mining tools that provide algorithms such as Linear Regressions, Decision Trees and Neural Networks. You can learn more about them here: http://msdn.microsoft.com/en-us/library/ms175595.aspx. It sounds like Linear Regressions might be the best place to start for this problem.

dennislloydjr
  • 950
  • 5
  • 13