Creé una consulta SQL que ejecuta un cálculo de variación de precio correctamente utilizando la siguiente lógica:
Variación de precio = (Precio medio del año actual – Precio medio del año anterior) / Volumen actual
Esto calcula cada mes frente al mismo mes del año anterior y solo cuando coincide la combinación de artículo / cliente. El problema con el código es que esto se reinicia cada año ya que estoy usando la lógica Current Year = GetDate (). ¿Hay alguna forma de cambiar este código para poder obtener un gráfico móvil, por ejemplo, diciembre de 2020 frente a diciembre de 2019 junto con enero de 2021 frente a enero de 2020?
WITH cur ([cur_item],[cur_cust],[cur_year],[cur_month],[cur_price],[cur_extprice],[cur_volume],[cur_avgprice],[pick_category])
AS
(
SELECT
inv_item_mst.item AS [cur_item],
custaddr_mst.name AS [cur_cust],
YEAR(inv_hdr_mst.inv_date) as [cur_year],
MONTH(inv_hdr_mst.inv_date) as [cur_month],
SUM(inv_item_mst.price) AS [cur_price],
SUM(inv_item_mst.price*inv_item_mst.qty_invoiced) AS [cur_extprice],
SUM(inv_item_mst.qty_invoiced) AS [cur_volume],
SUM(inv_item_mst.price*inv_item_mst.qty_invoiced)/NULLIF(SUM(inv_item_mst.qty_invoiced),0) AS [cur_avgprice],
item_mst.Uf_PickCategory AS [pick_category]
FROM inv_item_mst
LEFT JOIN inv_hdr_mst
ON inv_item_mst.inv_num=inv_hdr_mst.inv_num
LEFT JOIN custaddr_mst
ON inv_hdr_mst.cust_num=custaddr_mst.cust_num AND inv_hdr_mst.cust_seq=custaddr_mst.cust_seq
LEFT JOIN item_mst
ON inv_item_mst.item=item_mst.item
WHERE YEAR(inv_hdr_mst.inv_date)=YEAR(GETDATE()) AND (inv_item_mst.price*inv_item_mst.qty_invoiced)>0
GROUP BY inv_item_mst.item,
custaddr_mst.name,
item_mst.Uf_PickCategory,
YEAR(inv_hdr_mst.inv_date),
MONTH(inv_hdr_mst.inv_date)
)
,
prev ([prev_item],[prev_cust],[prev_year],[prev_month],[prev_price],[prev_extprice],[prev_volume],[prev_avgprice])
AS
(
SELECT
inv_item_mst.item AS [prev_item],
custaddr_mst.name AS [prev_cust],
YEAR(inv_hdr_mst.inv_date) as [prev_year],
MONTH(inv_hdr_mst.inv_date) as [prev_month],
SUM(inv_item_mst.price) AS [prev_price],
SUM(inv_item_mst.price*inv_item_mst.qty_invoiced) AS [prev_extprice],
SUM(inv_item_mst.qty_invoiced) AS [prev_volume],
SUM(inv_item_mst.price*inv_item_mst.qty_invoiced)/NULLIF(SUM(inv_item_mst.qty_invoiced),0) AS [prev_avgprice]
FROM inv_item_mst
LEFT JOIN inv_hdr_mst
ON inv_item_mst.inv_num=inv_hdr_mst.inv_num
LEFT JOIN custaddr_mst
ON inv_hdr_mst.cust_num=custaddr_mst.cust_num AND inv_hdr_mst.cust_seq=custaddr_mst.cust_seq
WHERE YEAR(inv_hdr_mst.inv_date)=YEAR(GETDATE())-1 AND (inv_item_mst.price*inv_item_mst.qty_invoiced)>0
GROUP BY inv_item_mst.item,
custaddr_mst.name,
YEAR(inv_hdr_mst.inv_date),
MONTH(inv_hdr_mst.inv_date)
)
SELECT
cur.cur_year,
cur.cur_month,
cur.cur_item,
cur.cur_cust,
cur.pick_category,
cur_avgprice,
prev_avgprice,
cur_volume,
CASE
WHEN prev.prev_volume IS NULL
THEN 0
ELSE (cur.cur_avgprice - prev.prev_avgprice)*cur.cur_volume
END AS [price_variance]
from cur
LEFT JOIN prev
ON cur.cur_item=prev.prev_item AND cur.cur_cust=prev.prev_cust AND cur.cur_month=prev.prev_month
