英文:
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), ................
当下订单时,默认的 salePrice
是 price_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 <= 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 <= 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,'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
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论