根据同一表格中是否存在其他价格来确定价格何时过时

Question

我在SQL server中遇到一个查询问题，该查询没有返回正确的数据。我很高兴提前承认，有一个简单简洁的解决方案，我应该能够看到，但我认为我想得太多了。

商业场景是，我们将某一特定商品的价格与报价进行比较。这是一个查询：

SELECT  ipl1.Id AS Id,
CASE s1.FirstName WHEN '' THEN s1.LastName ELSE s1.LastName + ', ' + s1.FirstName END AS SupplierName,
p1.Date,
p1.OrderNo,
p1.InvoiceNo,
i1.Number AS ItemNumber,
i1.Name AS ItemName,
ipl1.Quantity,
ROUND(ipl1.TaxExclusiveUnitPrice, 2) AS PriceCharged,
ROUND(ISNULL(p2.Amount, p4.Amount), 2) AS PriceQuoted,
ROUND(ROUND(ipl1.TaxExclusiveUnitPrice, 2) - ROUND(ISNULL(p2.Amount, p4.Amount), 2), 2) AS Difference,
ipl1.Quantity*ROUND(ROUND(ipl1.TaxExclusiveUnitPrice, 2) - ROUND(ISNULL(p2.Amount, p4.Amount), 2), 2) AS Overcharge,
ISNULL(p2.Starts, p4.Starts) AS QuoteDate,
ISNULL(p2.QuoteNo, p4.QuoteNo) AS QuoteNumber
FROM    pl_ItemPurchaseLines ipl1
INNER JOIN
pl_Purchases p1 ON p1.Id=ipl1.Purchase_Id
INNER JOIN
pl_Suppliers s1 ON s1.Id=p1.Supplier_Id 
INNER JOIN
pl_Items i1 ON i1.Id=ipl1.Item_Id 
LEFT JOIN
--- First Priority is the lowest Amount Current Quote or List Price
(
SELECT  p1.Id,
p1.Amount,
p1.Starts,
p1.Expires,
p1.QuoteNo,
ipl.Id AS ItemPurchaseLine_Id,
row_number() over (partition by ipl.Id order by p1.Amount ASC, p1.QuoteNo DESC, p1.Starts DESC) as Row
FROM    pl_ItemPurchaseLines ipl
INNER JOIN
pl_Purchases p ON p.Id=ipl.Purchase_Id
INNER JOIN 
pl_Prices p1 ON p1.Starts<=p.Date AND 
((p1.Expires>=p.Date AND p1.QuoteNo<>'') OR
(p1.Expires IS NULL AND p1.QuoteNo='')) AND
p1.Item_Id=ipl.Item_Id
) AS p2 ON  p2.Row = 1 AND
ipl1.Id=p2.ItemPurchaseLine_Id
LEFT JOIN           
(
SELECT  p3.Id,
p3.Amount,
p3.Starts,
p3.Expires,
p3.QuoteNo,
ipl2.Id AS ItemPurchaseLine_Id,
row_number() over (partition by ipl2.Id order by p3.Expires DESC) as Row
FROM    pl_ItemPurchaseLines ipl2
INNER JOIN
pl_Purchases p2 ON p2.Id=ipl2.Purchase_Id
INNER JOIN 
pl_Prices p3 ON p3.Starts<=p2.Date AND 
p3.QuoteNo<>'' AND
p3.Item_Id=ipl2.Item_Id
) AS p4 ON  p4.Row = 1 AND
ipl1.Id=p4.ItemPurchaseLine_Id

问题的核心在于子查询。价格有两种类型，一种是有开始日期、没有到期日期的标价，一种报价为"，并被后一种标价取代；或者具有开始日期、到期日期和报价编号的报价。

问题出现在第一个子查询中。在这里，我正在寻找价格最低但仍然有效的报价或标价，但是，我不知道如何排除被取代的标价，即报价为"且有效期为空的价格(这些很容易)，以及哪里有其他具有相同id且开始日期较晚的价目表。

我曾考虑过根据触发因素在表格中列出有效期，但我在这方面遇到了一些困难，因为需要处理后两个价目表同时出现的可能性(甚至是更早的价目表-我们正在向后和向前工作)。

当给定Price id时，我可以编写一个函数来确定它是否在给定日期被取代，但这似乎是次优的。

我觉得我只需要对where条款做点什么，让这些被取代的价格消失，但我已经做了一些gos，并建立了一些冗长的巴洛克条款，但这些条款不起作用！

以下是一些示例数据：

-- No there isn't I will put some up as soon as I format it --

以下是表格定义：

USE [tempdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[pl_Suppliers](
[Id] [int] IDENTITY(1,1) NOT NULL,
[LastName] [nvarchar](100) NOT NULL,
[FirstName] [nvarchar](100) NOT NULL,
[ShortName] [nvarchar](100) NOT NULL,
[Prefix] [nvarchar](100) NOT NULL,
[UserID] [nvarchar](100) NOT NULL,
[Password] [nvarchar](1000) NOT NULL,
[SentToMyob] [bit] NOT NULL,
CONSTRAINT [PK_Suppliers] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[pl_ItemPurchaseLines](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Quantity] [float] NOT NULL,
[TaxExclusiveUnitPrice] [float] NOT NULL,
[TaxExclusiveTotal] [float] NOT NULL,
[TaxInclusiveTotal] [float] NOT NULL,
[TaxBasisAmount] [float] NOT NULL,
[TaxCode] [nvarchar](max) NOT NULL,
[Received] [float] NOT NULL,
[TaxInclusiveUnitPrice] [float] NOT NULL,
[Purchase_Id] [int] NOT NULL,
[Item_Id] [int] NOT NULL,
CONSTRAINT [PK_pl_ItemPurchaseLines] PRIMARY KEY NONCLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[pl_Purchases](
[Id] [int] IDENTITY(1,1) NOT NULL,
[OrderNo] [nvarchar](max) NOT NULL,
[InvoiceNo] [nvarchar](max) NOT NULL,
[Date] [datetime] NOT NULL,
[ShipToAddressLine1] [nvarchar](max) NOT NULL,
[ShipToAddressLine2] [nvarchar](max) NOT NULL,
[ShipToAddressLine3] [nvarchar](max) NOT NULL,
[ShipToAddressLine4] [nvarchar](max) NOT NULL,
[Comment] [nvarchar](max) NOT NULL,
[TotalLines] [float] NOT NULL,
[TotalTax] [float] NOT NULL,
[Supplier_Id] [int] NOT NULL,
[SentToMyob] [bit] NOT NULL,
[Job_ID] [int] NULL,
CONSTRAINT [PK_Purchases] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[pl_Items](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Number] [nvarchar](100) NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[Supplier_Id] [int] NOT NULL,
[SentToMyob] [bit] NOT NULL,
CONSTRAINT [PK_Items] PRIMARY KEY NONCLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[pl_Prices](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Amount] [float] NOT NULL,
[Starts] [datetime] NOT NULL,
[Expires] [datetime] NULL,
[QuoteNo] [nvarchar](100) NOT NULL,
[Item_Id] [int] NOT NULL,
CONSTRAINT [PK_Prices] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[pl_Prices]  WITH CHECK ADD  CONSTRAINT [FK_ItemPrice] FOREIGN KEY([Item_Id])
REFERENCES [dbo].[pl_Items] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[pl_Prices] CHECK CONSTRAINT [FK_ItemPrice]
GO
ALTER TABLE [dbo].[pl_Items]  WITH CHECK ADD  CONSTRAINT [FK_SupplierItem] FOREIGN KEY([Supplier_Id])
REFERENCES [dbo].[pl_Suppliers] ([Id])
GO
ALTER TABLE [dbo].[pl_Items] CHECK CONSTRAINT [FK_SupplierItem]
GO
ALTER TABLE [dbo].[pl_Items] ADD  CONSTRAINT [DF_pl_Items_SentToMyob]  DEFAULT ((0)) FOR [SentToMyob]
GO
ALTER TABLE [dbo].[pl_Purchases]  WITH CHECK ADD  CONSTRAINT [FK_PurchasesSuppliers] FOREIGN KEY([Supplier_Id])
REFERENCES [dbo].[pl_Suppliers] ([Id])
GO
ALTER TABLE [dbo].[pl_Purchases] CHECK CONSTRAINT [FK_PurchasesSuppliers]
GO
ALTER TABLE [dbo].[pl_Purchases] ADD  CONSTRAINT [DF_pl_Purchases_SentToMyob]  DEFAULT ((0)) FOR [SentToMyob]
GO
ALTER TABLE [dbo].[pl_ItemPurchaseLines]  WITH CHECK ADD  CONSTRAINT [FK_ItemPurchaseLineItem] FOREIGN KEY([Item_Id])
REFERENCES [dbo].[pl_Items] ([Id])
GO
ALTER TABLE [dbo].[pl_ItemPurchaseLines] CHECK CONSTRAINT [FK_ItemPurchaseLineItem]
GO
ALTER TABLE [dbo].[pl_ItemPurchaseLines]  WITH CHECK ADD  CONSTRAINT [FK_Purchase_ItemPurchaseLines] FOREIGN KEY([Purchase_Id])
REFERENCES [dbo].[pl_Purchases] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[pl_ItemPurchaseLines] CHECK CONSTRAINT [FK_Purchase_ItemPurchaseLines]
GO
ALTER TABLE [dbo].[pl_Suppliers] ADD  CONSTRAINT [DF_pl_Suppliers_SentToMyob]  DEFAULT ((0)) FOR [SentToMyob]
GO

Answer 1

您的解决方案在子选择中有一个错误：p1。Item_Id=p1.Item_Id这只引用"子选择"表"p1"，而不是"外部选择"表的"p">

任何具有子选择器的解决方案对于大型数据集都是不可扩展的。

这样的东西是可扩展的，假设正确的索引：

SELECT p.[Id]
,p.[Amount]
,p.[Starts] 
,ISNULL(p.Expires, DATEADD(DAY,-1,MIN(p1.Starts))
,p.[QuoteNo]
,p.[Item_Id]
FROM    pl_Prices p
LEFT JOIN pl_Prices p1 ON
p1.Item_Id=p.Item_Id AND    
p1.Starts>p.Starts
GROUP BY 
p.[Id]
,p.[Amount]
,p.[Starts] 
,p.[QuoteNo]
,p.[Item_Id]

Answer 2

我想明白了——它和我预期的一样简单。

我创建了这个视图，并在第一个子查询中引用了它。

ALTER VIEW [dbo].[pl_PricesWithExpiry]
AS
SELECT [Id]
,[Amount]
,[Starts] 
,ISNULL(p.Expires, (SELECT DATEADD(DAY,-1,MIN(p1.Starts))
FROM pl_Prices p1
WHERE p1.Item_Id=p.Item_Id AND
p1.Starts>p.Starts)) AS Expires
,[QuoteNo]
,[Item_Id]
FROM    pl_Prices p