动态日期查找

dtcbnfnu 于 2021-07-27 发布在 Java

关注(0)|答案(1)|浏览(300)

我很快就要弄清楚如何根据当前月份与上年同期的价格差异来计算商品的价格差异，但我目前使用的是静态日期。
理想情况下，我希望这是动态的，无论第一个表的发票月份是什么，它都会查看同一个月但上一年的匹配项目事务。
注意，因为我想使用当前月份的发票日期来驱动线图，所以我需要将它添加到我的第一个表中，但是，我不希望按它分组，因为我认为它可能会扭曲我的数字。
示例数据存储在此处：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

sql Date variance

来源：https://stackoverflow.com/questions/63185389/dynamic-date-lookup

1条答案

按热度按时间

insrf1ej1#

你的预期结果在下面吗？

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;

D小提琴
另一个具有相同结果的查询如下：

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;

cte1每年使用union有不同的列。
cte2具有按项目和月份（不含年份）分组的聚合。
最后一个查询具有每个月的聚合。
D小提琴

赞(0）回复(0）举报 2021-07-27

我来回答

动态日期查找

1条答案

相关问题

热门标签

最新问答