sql - DB2 query to find average sale for each item 1 year previous -

having trouble figuring out how make these query.

in general have table with

• sales_id
• employee_id
• sale_date
• sale_price

what want have view shows each sales item how employee on average sells 1 year previous of sale_date.

example: suppose have in sales table

sales_id    employee_id    sale_date    sale_price 1           bob            2016/06/10    100 2           bob            2016/01/01    75 3           bob            2014/01/01    475 4           bob            2015/12/01    100 5           bob            2016/05/01    200 6           fred           2016/01/01    30 7           fred           2015/05/01    50 

for sales_id 1 record want pull sales bob 1 year month of sale (so 2015-05-01 2016-05-31 has 3 sales 75, 100, 200) final output be

sales_id    employee_id    sale_date    sale_price    avg_sale 1           bob            2016/06/10    100          125 2           bob            2016/01/01    75           275 3           bob            2014/01/01    475          null 4           bob            2015/12/01    100          475 5           bob            2016/05/01    200          87.5 6           fred           2016/01/01    30           50 7           fred           2015/05/01    50           null 

what tried doing this

select a.sales_id, a.sale_price, a.employee_id, a.sale_date, b.avg_price sales left join (      select employee_id, avg(sale_price) avg_price      sales       sale_date between date(varchar(year(a.sale_date)-1) ||'-'|| varchar(month(a.sale_date)-1) || '-01')                   , date(varchar(year(a.sale_date)) ||'-'|| varchar(month(a.sale_date)) || '-01') -1 day     group employee_id ) b on a.employee_id = b.employee_id 

which db2 doesn't using parent table in sub query, can't think of how write query. thoughts?

ok. think figured out. please note 3 things.

1. i couldn't test in db2, used oracle. syntax more or less same.
2. i didn't use 1 year logic exactly. counting current_date minus 365 days, can change between part in where clause in inner query, mentioned in question.

3. the expected output mentioned incorrect. every sale_id, took date, found employee_id, took sales of employee last 1 year, excluding current date, , took average. if want change it, can change clause in subquery.

   select t1.*,t2.avg_sale   sales t1 left join  (     select a.sales_id     ,avg(b.sale_price) avg_sale     sales         inner join      sales b      on a.employee_id=b.employee_id         b.sale_date between  a.sale_date - 365 ,  a.sale_date -1     group a.sales_id ) t2 on t1.sales_id=t2.sales_id order t1.sales_id 

output

+----------+-------------+-------------+------------+----------+ | sales_id | employee_id |  sale_date  | sale_price | avg_sale | +----------+-------------+-------------+------------+----------+ |        1 | bob         | 10-jun-2016 |        100 | 125      | |        2 | bob         | 01-jan-2016 |         75 | 100      | |        3 | bob         | 01-jan-2014 |        475 |          | |        4 | bob         | 01-dec-2015 |        100 |          | |        5 | bob         | 01-may-2016 |        200 | 87.5     | |        6 | fred        | 01-jan-2016 |         30 | 50       | |        7 | fred        | 01-may-2015 |         50 |          | +----------+-------------+-------------+------------+----------+