Forum Discussion

RobinOfSheffield's avatar
RobinOfSheffield
Copper Contributor
Aug 29, 2023
Solved

Pull First Date and Price

Hi, 

 

I have a table like in the example below. I need to pull the first Date of Delivery for each Purchase Price Change. 

 

 

The expected result should look like this.

 

 

This is my query,

 

 

The problem I am having is, it should pull 13 rows but it pulls only the first 10 rows in my query as the GROUP BY removes the bottom three as those Purchase Prices are already included within the top 10. 

 

Can someone please help?

 

Thanks.

Reporting Services
  • LainRobertson's avatar
    LainRobertson
    Aug 29, 2023

    RobinOfSheffield 

     

    I couldn't bring myself to type in all of those rows so I focused on the final six and came up with the following.

     

    See if it gets you any closer to what you're after.

     

    Staging the data

    CREATE TABLE [dbo].[PurchaseHistory]
(
	[DateOfDelivery] [datetime] NOT NULL
	, [ItemID] [varchar](32) NOT NULL
	, [Vendor] [int] NOT NULL
	, [PurchasePrice] [float] NOT NULL
)
GO

INSERT INTO
	[dbo].[PurchaseHistory]
VALUES
	('2023-04-11', 'FX-1150-110', 5564100, 17.11)
	, ('2023-04-13', 'FX-1150-110', 5564100, 17.11)
	, ('2023-07-03', 'FX-1150-110', 5800500, 18.44)
	, ('2023-07-13', 'FX-1150-110', 5564100, 17.11)
	, ('2023-07-17', 'FX-1150-110', 5564100, 17.11)
	, ('2023-07-18', 'FX-1150-110', 5800500, 18.44)
GO

     

    Example

    Note: The LAG() function was introduced in SQL 2012, so if you're running an older edition (hopefully that isn't the case in this day and age), this example won't work.

     

    SELECT
	 [data].[DateOfDelivery]
	 , [data].[ItemID]
	 , [data].[Vendor]
	 , [data].[PurchasePrice]
FROM
(
	SELECT
		ph.DateOfDelivery
		, ph.ItemID
		, ph.Vendor
		, ph.PurchasePrice
		, CASE
			WHEN
				LAG(ph.PurchasePrice, 1) OVER
				(
					PARTITION BY
						ph.ItemID
					ORDER BY
						ph.DateOfDelivery
				) = ph.PurchasePrice
			THEN 0
			ELSE 1
		END AS [Include]
	FROM
		PurchaseHistory AS ph
) AS [data]
WHERE
	[data].[Include] = 1;

     

    Output

     

    Cheers,

    Lain

2 Replies

Sort By
  • LainRobertson's avatar
    LainRobertson
    Silver Contributor
    Aug 29, 2023

    RobinOfSheffield 

     

    I couldn't bring myself to type in all of those rows so I focused on the final six and came up with the following.

     

    See if it gets you any closer to what you're after.

     

    Staging the data

    CREATE TABLE [dbo].[PurchaseHistory]
(
	[DateOfDelivery] [datetime] NOT NULL
	, [ItemID] [varchar](32) NOT NULL
	, [Vendor] [int] NOT NULL
	, [PurchasePrice] [float] NOT NULL
)
GO

INSERT INTO
	[dbo].[PurchaseHistory]
VALUES
	('2023-04-11', 'FX-1150-110', 5564100, 17.11)
	, ('2023-04-13', 'FX-1150-110', 5564100, 17.11)
	, ('2023-07-03', 'FX-1150-110', 5800500, 18.44)
	, ('2023-07-13', 'FX-1150-110', 5564100, 17.11)
	, ('2023-07-17', 'FX-1150-110', 5564100, 17.11)
	, ('2023-07-18', 'FX-1150-110', 5800500, 18.44)
GO

     

    Example

    Note: The LAG() function was introduced in SQL 2012, so if you're running an older edition (hopefully that isn't the case in this day and age), this example won't work.

     

    SELECT
	 [data].[DateOfDelivery]
	 , [data].[ItemID]
	 , [data].[Vendor]
	 , [data].[PurchasePrice]
FROM
(
	SELECT
		ph.DateOfDelivery
		, ph.ItemID
		, ph.Vendor
		, ph.PurchasePrice
		, CASE
			WHEN
				LAG(ph.PurchasePrice, 1) OVER
				(
					PARTITION BY
						ph.ItemID
					ORDER BY
						ph.DateOfDelivery
				) = ph.PurchasePrice
			THEN 0
			ELSE 1
		END AS [Include]
	FROM
		PurchaseHistory AS ph
) AS [data]
WHERE
	[data].[Include] = 1;

     

    Output

     

    Cheers,

    Lain

Resources