I have a report that contains basic sales data for items sold in the month. I would like to compare the item’s price in the report to the average net price from the last year the item sold.
Example: If item # 007750 was sold in 2024. I would like to know the average net price it sold for in 2023, if it didn’t sell in 2023 then the report should look at 2022 etc… until it finds a year it sold in.
Sample Data
| Date | Order No. | Item No. | Qty. | Unit Price | Line Disc % | Line Amount |
|---|---|---|---|---|---|---|
| 02/04/2022 | SO1999 | 001178 | 4 | 161.00 | 30% | 450.80 |
| 04/13/2022 | SO2045 | 001178 | 6 | 161.00 | 0% | 966.00 |
| 12/17/2022 | SO2203 | 001178 | 1 | 117.60 | 0% | 117.60 |
| 05/23/2024 | SO2468 | 001178 | 4 | 176.00 | 30% | 492.80 |
Expected Results
The database doesn’t have a net price field so that will need to be solved in SQL as well
| Date | Order No. | Item No. | Qty. | Unit Price | Line Disc % | Line Amount | Net Price Avg. |
|---|---|---|---|---|---|---|---|
| 02/04/2022 | SO1999 | 001178 | 4 | 161.00 | 30% | 450.80 | 130.43 |
| 04/13/2022 | SO2045 | 001178 | 6 | 161.00 | 0% | 966.00 | 130.43 |
| 12/17/2022 | SO2203 | 001178 | 1 | 117.60 | 0% | 117.60 | 130.43 |
| 05/23/2024 | SO2468 | 001178 | 4 | 176.00 | 30% | 492.80 | 130.43 |
- The net price average is coming from the 3 sales in 2022. Net unit prices were 112.70, 161.00 and 117.60 for the 3 sales.
- If there was a sale in 2023, the report would use that net price average, so needs to look at the last year that had at least 1 sale.
Sample Code
I’m not sure where to start to retrieve the average net price. Maybe using a CTE in some way?
SELECT
InvoiceDate,
OrderNo,
ItemNo,
Qty,
UnitPrice,
LineDiscPct,
LineAmt
FROM SalesInvoiceLine