0

Hello I have below data for a table and I'm looking for moving average.

Original Data 

date    qty 
06/11/15    5   
08/11/15    7   
09/11/15    8   
10/11/15    12  
11/11/15    34  
15/11/15    45  
16/11/15    12  
17/11/15    7

The moving average should consider last 90 days of data and give me the average of the current data like below

Moving Average Data     
date    qty movingAvg
06/11/15    5   5
07/11/15    0   2.5
08/11/15    7   4
09/11/15    8   5
10/11/15    12  6.4
11/11/15    34  11
12/11/15    0   9.43
13/11/15    0   8.25
14/11/15    0   7.33
15/11/15    45  11.1
16/11/15    12  11.18
17/11/15    7   10.83

The query I'm using 

SELECT t1.date,t1.qty, AVG(t2.qty) AS 'movingAvg'
FROM tbl t1
JOIN tbl t2 ON t1.company_id=t2.company_id 
AND DATEDIFF(t1.date, t2.date) BETWEEN 0 AND 90
WHERE t1.company_id=1234
GROUP BY t1.date
ORDER BY t1.date;

which is ignoring date without entry, I want to include them as well.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Goutham P N
  • 65
  • 9

1 Answers1

0

use MySQL Str_to_date like that 

Str_to_date(t1.date,'%d/%m/%y')

or change this 

DATEDIFF(Str_to_date(t1.date,'%d/%m/%y'), Str_to_date(t2.date,'%d/%m/%y'))

MySQL STR_TO_DATE() function to convert a string into a date time value

Abhishek Sharma
  • 6,689
  • 1
  • 14
  • 20