获取订单日期前的最后价格。

huangapple go评论76阅读模式
英文:

Get last price before order date

问题

在SQL Server中,我有一个包含按日期的价格列表的表格。

item 表格:

itemNbr varchar(50),
itemName varchar(100),.......

price_list

itemNbr varchar(50),
startDate DateTime,
salePrice decimal(12,2)

它可能包含如下数据:

item1, 01/01/2023, 2.00
item1, 01/15/2023, 2.50
item1, 02/01/2023, 3.00
item3, 01/01/2023, 1.00

我还有一个订单表(标题和行)具有典型的列,如

orderHeader

id varchar(50), --唯一的id guid
orderDate dateTime,
orderNbr integer,
customerNbr varchar(50)...........

OrderLine

headerId varchar(50), --唯一的id guid
lineNbr integer,
itemNbr varchar(50),
price decimal(12,2)
qty decimal(12,2), ................

当下订单时,默认的 salePriceprice_list 表格中在 start_Date <= order_date 条件下定义的最后一个价格。

在上面的示例中,如果在01/08下订单,价格将是2.00,但如果在01/15之后但在二月之前下订单,价格将是2.5。

在订单录入过程中,操作员可以输入与从 price_list 表格中提出的默认 salePrice 不同的价格。

我需要运行一个查询,返回每个订单/订单行的实际价格以及下订单时提出的默认 salePrice

我尝试了以下查询,但它不起作用,始终返回0作为 salePrice

select
    l.itemNbr, i.itemName, h.orderNbr, h.orderDate, 
    l.Qty, l.price, l.total, sp.salePrice
from
    orderHeader h
join 
    orderLine l on h.id = l.headerId 
join 
    item i on i.itemNbr = l.itemNbr 
left join 
    (select top 1 
         price_list.itemNbr, price_list.salePrice, price_list.startDate
     from 
         price_list
     order by 
         startDate Desc) as sp on sp.itemNbr = l.itemNbr 
                               and sp.startDate <=  h.orderDate;

有没有简单的方法来解决这个问题?

英文:

In SQL Server, I have a table that contains the price list by date.

item table:

itemNbr varchar(50),
itemName varchar(100),.......

price_list:

itemNbr varchar(50),
startDate DateTime,
salePrice  decimal(12,2)

It could have data like this:

item1, 01/01/2023,  2.00
item1, 01/15/2023,  2.50
item1, 02/01/2023,  3.00
item3, 01/01/2023,  1.00

I also have an order tables (header and line) with typical columns like

orderHeader:

id   varchar(50), --unique id guid
orderDate dateTime,
orderNbr  integer,
customerNbr varchar(50)...........

OrderLine:

headerId  varchar(50), --unique id guid
lineNbr integer,
itemNbr varchar(50),
price  decimal(12,2)
qty decimal(12,2), ................

When an order is placed, the default salePrice is the LAST price defined in the price_list table where the start_Date &lt;= order_date.

In the example above, if an order was placed on 01/08 the price will be 2.00 but if it is placed after 01/15 but before February then it will be 2.5.

During order entry the operator can enter a price other than the default salePrice proposed from the price_list table.

I need to run a query that returns for each order/order line the actual price from the order line and the default salePrice that was proposed when the order was placed.

I tried the following query but it is not working and returning 0 as salePrice always:

select
    l.itemNbr, i.itemName, h.orderNbr, h.orderDate, 
    l.Qty, l.price, l.total, sp.salePrice
from
    orderHeader h
join 
    orderLine l on h.id = l.headerId 
join 
    item i on i.itemNbr = l.itemNbr 
left join 
    (select top 1 
         price_list.itemNbr, price_list.salePrice, price_list.startDate
     from 
         price_list
     order by 
         startDate Desc) as sp on sp.itemNbr = l.itemNbr 
                               and sp.startDate &lt;=  h.orderDate;

Is there an easy way to this?

答案1

得分: 1

这是一种方法(绝对不是唯一的方法):

;with price_list_w_enddate as (

   select 
		itemNbr ,startDate,
		enddate=lead(dateadd(day, -1, startdate),1,'9999-12-31') over (partition by itemnbr order by startdate),
		salePrice
	from price_list
)
select
    l.itemNbr, h.orderNbr, h.orderDate, 
    l.Qty, l.price, sp.salePrice
from
    orderHeader h
join 
    orderLine l on h.id = l.headerId 
left join 
   price_list_w_enddate sp
   on sp.itemNbr=l.itemNbr
   and h.orderDate between sp.startDate and sp.EndDate

在这种方法中,我为 price_list 添加了一个 endDate。

我删除了一些查询中的细节,以节省时间,因为您没有提供可用的数据;我相信您可以将它们添加回去。

英文:

This is one way (definitely not the only way):

;with price_list_w_enddate as (

   select 
		itemNbr ,startDate,
		enddate=lead(dateadd(day, -1, startdate),1,&#39;9999-12-31&#39;) over (partition by itemnbr order by startdate),
		salePrice
	from price_list
)
select
    l.itemNbr, h.orderNbr, h.orderDate, 
    l.Qty, l.price, sp.salePrice
from
    orderHeader h
join 
    orderLine l on h.id = l.headerId 
left join 
   price_list_w_enddate sp
   on sp.itemNbr=l.itemNbr
   and h.orderDate between sp.startDate and sp.EndDate

In this method I added an endDate to the price_list.

I removed some details from your query to save myself time, because you didn't give us usable data; I trust you can add them back.

huangapple
  • 本文由 发表于 2023年8月4日 08:32:58
  • 转载请务必保留本文链接:https://go.coder-hub.com/76832296.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定