Wizuriel Wizuriel - 5 months ago 25
SQL Question

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

Having some trouble figuring out how to make these query.

In general I have a table with


  • sales_ID

  • Employee_ID

  • sale_date

  • sale_price



what I want to do is have a view that shows for each sales item how much the employee on average sells for 1 year previous of the sale_date.

example: Suppose I have this in the 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 I want to pull all sales from Bob by 1 year up to the month of the sale (so 2015-05-01 to 2016-05-31 which has 3 sales for 75, 100, 200) so the final output would 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 I tried doing is something like this

select a.sales_ID, a.sale_price, a.employee_ID, a.sale_date, b.avg_price
from sales a
left join (
select employee_id, avg(sale_price) as avg_price
from sales
where sale_date between Date(VARCHAR(YEAR(a.sale_date)-1) ||'-'|| VARCHAR(MONTH(a.sale_date)-1) || '-01')
and Date(VARCHAR(YEAR(a.sale_date)) ||'-'|| VARCHAR(MONTH(a.sale_date)) || '-01') -1 day
group by employee_id
) b on a.employee_id = b.employee_id


which DB2 doesn't like using the parent table a in the sub query, but I can't think of how to properly write this query. any thoughts?

Answer

Ok. I think I figured it out. Please note 3 things.

  1. I couldn't test it in DB2, so I used Oracle. But syntax would be more or less same.
  2. I didn't use your 1 year logic exactly. I am counting current_date minus 365 days, but you can change the between part in where clause in inner query, as you mentioned in the question.
  3. The expected output you mentioned is incorrect. So for every sale_id, I took the date, found the employee_id, took all the sales of that employee for last 1 year, excluding the current date, and then took average. If you want to change it, you can change the where clause in subquery.

    select t1.*,t2.avg_sale 
    from 
    sales t1
    left join 
    (
        select a.sales_id
        ,avg(b.sale_price) as avg_sale
        from sales a
            inner join 
        sales b
         on a.employee_id=b.employee_id
            where b.sale_date between  a.sale_date - 365 and  a.sale_date -1
        group by a.sales_id
    ) t2
    on t1.sales_id=t2.sales_id
    order by 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 |          |
+----------+-------------+-------------+------------+----------+
Comments