Determine when prices have become obsolete based on the presence of another price in the same table

Question

I have a problem with a query in SQL server that is not returning the right data. I am happy to concede in advance that there is a simple and concise solution that I should be able to see but I think I have been overthinking things.

The business scenario is we are comparing the prices that we have been charged for a given item against the price we were quoted. This is the query:

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

The guts of the problem is in the subqueries. There are 2 types of Price, a list price which has a start date, no expiry date and a quoteno of '' and is superseded by a latter list price; or a quoted price which has a start date, an expiry date and a quote number.

The problem is in the first subquery. Here I am looking for the lowest priced still valid quote or list price, however, I can't work out how to exclude superseded list prices i.e. prices with quoteno='' and expiry is null (these are easy) and where there are other price lists with the same id with a later start date.

I have considered putting an expiry date in the table based on triggers but I am having some difficulty with that because of the need to deal with the possibility of 2 latter price lists coming in at once (and even earlier price lists - we are working backwards as well as forwards).

I could write a function when given a Price id to determine if it is superseded at a given date but that just seems sub-optimal.

I feel that I just need to do something to the where clause to make these superseded prices go away but I have had a few gos and have built some verbose, baroque clauses that don't work!

Here is some sample data:

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

Here are the table definitions:

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

I figured it out - it was simple as I expected.

I created this view, and referenced it in the first subquery.

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

Answer 2

your solution had a bug in the subselect: p1.Item_Id=p1.Item_Id this only references the "sub-select" table "p1" and not the "outer-select" table "p"

any solution with subselects is not scalable for large datasets.

something like this would be scalable assuming proper indexing:

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]