Average price of last 5 line by items by recent date

[Vagus14]

Hey everyone,

I am trying to figure out a way to filter the average price of of a Product within the last 5 occurences (Line Items). It would pull a week back so WHERE: Between Now()-7 and Now().
Example:

PARTID        |    Price    |    Date
--------------+-------------+---------
111223344     |    5        |    3/1/2015
111223344     |    7        |    3/2/2015
111223344     |    8        |    3/4/2015
111223344     |    10       |    11/22/2014
111223344     |    20       |    10/1/2014
111223355     |    5        |    2/5/2015
111223355     |    6        |    2/1/2015

to:

What I want:

PARTID        |    avgPrice    |    MinDate
--------------+----------------+-------------
111223344     |    10          |    10/1/2014
111223355     |    5.5         |    2/1/2015

 

[pr2-eugin]

How is Average of 5 and 6 = 6?

 

[Vagus14]

Edited 6 to 5.5

 

[pr2-eugin]

Somethign like,

    SELECT 
        PARTID,
        Avg(Price) As avgPrice,
        Min([Date]) As MinDate
    FROM 
        productTable
    GROUP BY 
        PARTID

 

[Vagus14]

From the look of that code it doesn't look like it would pull the last 5 line items. I only want to pull up to five items and do an average.

SELECT
PARTID,
Avg(Price) As avgPrice,
Min([Date]) As MinDate
FROM
productTable
GROUP BY
PARTID