sql - 动态日期查找

Question

我即将弄清楚如何根据当前月份与前一年的同一月份计算项目的价格差异，但我目前使用的是静态日期。

理想情况下，无论第一张表的发票月份是什么，我都希望它是动态的，它着眼于同月但前一年的匹配项目交易。

请注意，由于我想使用当前月份的发票日期来驱动折线图，因此我需要将其添加到我的第一个表中，但是我不想按它分组，因为我认为它可能会扭曲我的数字。

示例数据存储在此处：https ://drive.google.com/file/d/1hAVfDwNw9pNDS9-Nc6U3eafaHb_2pL6f/view?usp=sharing

在上面的数据集中，我将寻找以下结果：

输出

我理想的最终输出是

库存日期 价格差异 2020 年 6 月 | -.011683918 2020 年 7 月 | .046218968

WITH cur ([cur_item],[cur_price],[cur_extprice],[cur_volume],[cur_avgprice])

AS

(
SELECT
    inv_item_mst.item AS [cur_item],
    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]

FROM inv_item_mst

LEFT JOIN inv_hdr_mst
    ON inv_item_mst.inv_num=inv_hdr_mst.inv_num
LEFT JOIN customer_mst
    ON inv_hdr_mst.cust_num=customer_mst.cust_num AND inv_hdr_mst.cust_seq=customer_mst.cust_seq

WHERE YEAR(inv_hdr_mst.inv_date)=YEAR(GETDATE()) and MONTH(inv_hdr_mst.inv_date)=MONTH('3/1/2020') 

GROUP BY inv_item_mst.item

)

,

prev ([prev_item],[prev_price],[prev_extprice],[prev_volume],[prev_avgprice])

AS

(
SELECT
    inv_item_mst.item AS [prev_item],
    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 customer_mst
    ON inv_hdr_mst.cust_num=customer_mst.cust_num AND inv_hdr_mst.cust_seq=customer_mst.cust_seq

WHERE YEAR(inv_hdr_mst.inv_date)=YEAR(GETDATE())-1 and MONTH(inv_hdr_mst.inv_date)=MONTH('3/1/2019')
GROUP BY inv_item_mst.item

)

SELECT
cur.cur_item,
CASE
WHEN cur.cur_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

标签： sqldatevariance

Answer 1

您的预期结果是否如下？

Month  | Price Variance
6      | -0.011683
7      | -0.056540

2020 年 7 月 | .046218968

当前是 2019 年 7 月，上一个是 2020 年 7 月。这是不正确的吗？

您的数据库是 SQL Server，不是吗？

作为一种简单的方法，您可以使用 CASE 表达式计算当前年份和上一年的数据。

with cte as (
select
  item,
  format(max(inv_date), 'MM/yyy') as month,
  sum(case when year(inv_date)=year(getdate()) then price end) as cur_price,
  sum(case when year(inv_date)=year(getdate()) then price*qty_invoiced end) as cur_extprice,
  sum(case when year(inv_date)=year(getdate()) then qty_invoiced end) as cur_volume,
  sum(case when year(inv_date)=year(getdate()) then 1.0*price*qty_invoiced end)/sum(case when year(inv_date)=year(getdate()) then qty_invoiced end) as cur_avgprice,
  sum(case when year(inv_date)=year(getdate())-1 then price end) as prev_price,
  sum(case when year(inv_date)=year(getdate())-1 then price*qty_invoiced end) as prev_extprice,
  sum(case when year(inv_date)=year(getdate())-1 then qty_invoiced end) as prev_volume,
  sum(case when year(inv_date)=year(getdate())-1 then 1.0*price*qty_invoiced end)/sum(case when year(inv_date)=year(getdate())-1 then qty_invoiced end) as prev_avgprice
from inv_item_mst
group by item, month(inv_date)
)
select
  month,
  sum((1.0*cur_avgprice - prev_avgprice)/cur_volume) as cur_price_variance,
  sum((1.0*prev_avgprice - cur_avgprice)/prev_volume) as prev_price_variance
from cte
group by month;

小提琴手

另一个具有相同结果的查询如下：

with
cte1 as (
  select
    inv_date,
    year(inv_date) as year,
    month(inv_date) as month,
    item,
    cust,
    qty_invoiced as cur_qty_invoiced,
    price as cur_price,
    cast(NULL as int) as prev_qty_invoiced,
    cast(NULL as int) as prev_price
  from inv_item_mst
  where year(inv_date)=year(getdate())
  union all
  select
    inv_date,
    year(inv_date),
    month(inv_date),
    item,
    cust,
    NULL,
    NULL,
    qty_invoiced,
    price
  from inv_item_mst
  where year(inv_date)=year(getdate())-1
),
cte2 as (
  select
    item,
    month,
    sum(cur_price) as cur_price,
    sum(cur_price*cur_qty_invoiced) as cur_extprice,
    sum(cur_qty_invoiced) as cur_volume,
    sum(1.0*cur_price*cur_qty_invoiced)/sum(cur_qty_invoiced) as cur_avgprice,
    sum(prev_price) as prev_price,
    sum(prev_price*prev_qty_invoiced) as prev_extprice,
    sum(prev_qty_invoiced) as prev_volume,
    sum(1.0*prev_price*prev_qty_invoiced)/sum(prev_qty_invoiced) as prev_avgprice
  from cte1
  group by item, month
)
select
  month,
  sum((1.0*cur_avgprice - prev_avgprice)/cur_volume) as cur_price_variance
from cte2
group by month;

1. cte1 使用 UNION 每年都有不同的列。
2. cte2 具有按项目和月份分组的聚合，没有年份。
3. 最后一个查询包含每个月的聚合。

小提琴手