Can I get the Last Date a product had its Cost changed and that relevant cost from a union table I have created?

Question

I wrote a query to extract the most recent price of products and what that price was from tables in our company's SQL Server database and used UNION to join it with the most recent delivery price for those same products. Is there a way I can now nest that union query into another one to find the newest price from the unionised table?

This is the code of the working union query:

select  LastCostDate,
        [LocStock].SiteID,      
        [LastDate].PLUID,
        [LastDate].Description,
        [LastCost].Cost
From    (select max([PLUCostHistory].ChangeDate) as LastCostDate, [PLUCostHistory].PLUID, [PLU].Description From [PLUCostHistory] Inner Join [PLU]
        on [PLU].PLUID=[PLUCostHistory].PLUID group by [PLUCostHistory].PLUID, [PLU].Description ) as LastDate

Inner Join (select [PLUCostHistory].ChangeDate, [PLUCostHistory].PLUID, [PLU].Description, sum([PLUCostHistory].Cost) as Cost From [PLUCostHistory] Inner Join [PLU]
        on [PLU].PLUID=[PLUCostHistory].PLUID group by [PLUCostHistory].ChangeDate, [PLUCostHistory].PLUID, [PLU].Description ) as LastCost
On      LastDate.Description=LastCost.Description
And     LastDate.LastCostDate=LastCost.ChangeDate
And     LastDate.PLUID=LastCost.PLUID
Inner Join [LocStock]
        on [LocStock].PLUID=[LastDate].PLUID

UNION

select  LastCostDate,
        [DelMast].SiteID,       
        [LastDate].PLUID,
        [LastDate].Description,
        [LastCost].Cost
From    (select max([DelMast].DeliveryDate) as LastCostDate, [DelDets].PLUID, [PLU].Description From [DelMast] Inner Join [DelDets]
        on [DelDets].DeliveryID=[DelMast].DeliveryID Inner Join [PLU] on [PLU].PLUID=[DelDets].PLUID Inner Join [PLUGroupRef]
        on [DelDets].PLUID=[PLUGroupRef].PLUID Inner Join [PLUGroup2]
        on [PLUGroup2].PLUGroup2ID=[PLUGroupRef].PLUGroup2ID group by [DelDets].PLUID, [PLU].Description) as LastDate

Inner Join (select [DelMast].DeliveryDate, [DelDets].PLUID, [PLU].Description, sum([DelDets].Cost) as Cost From [DelMast] Inner Join [DelDets] on [DelDets].DeliveryID=[DelMast].DeliveryID Inner Join [PLU]
        on [PLU].PLUID=[DelDets].PLUID group by [DelMast].DeliveryDate, [DelDets].PLUID, [PLU].Description) as LastCost
On      LastDate.Description=LastCost.Description
And     LastDate.LastCostDate=LastCost.DeliveryDate
And     LastDate.PLUID=LastCost.PLUID
Inner Join [LocStock]
        on [LocStock].PLUID=[LastDate].PLUID
Inner Join [DelMast] 
        on [LocStock].SiteID=[DelMast].SiteID

Table 1

ChangeDate	Product	Cost
01/01/2000	Water	5
06/01/2000	Banana	2
09/01/2000	orange	3
10/01/2000	Water	3
01/01/2000	Banana	2.5

Table 2

ChangeDate	Product	Cost
08/01/2000	Water	6
03/01/2000	Banana	1
05/01/2000	Water	3
02/01/2000	Banana	3
12/01/2000	orange	4

Desired output:

ChangeDate	Product	Cost
10/01/2000	Water	3
06/01/2000	Banana	2
12/01/2000	orange	4

I feel like the queries you have likely could have some significant improvements, if I am honest. The `UNION` alone is likely to be *very* expensive, due to the implicit `DISTINCT` applied to the dataset; is that intended? Perhaps you might be better off taking a step back here and describing the problem you are trying to solve with the above first query, and then we can see how we can improve that. — Thom A, Feb 02 '23 at 15:47
I'm pretty much an SQL newbie so my query is probably incredibly inefficient, apologies! so the first query is pulling the newest date/SiteLocation/Product/Cost(price) from a Cost history table in our server and then joining it with the same sort of query form the Delivery Cost table. My aim is to then use those two prices/dates for every product and date in order to again find the most recent change but this time across both tables in order to get an accurate stock cost pricing. — ted15555, Feb 02 '23 at 16:01
Can you provide consumable sample data, and expected results? — Thom A, Feb 02 '23 at 16:01
Apologies, this may take some time as I have never used stackoverflow and am unfamiliar with how to properly provide tables — ted15555, Feb 02 '23 at 16:25
DDL and DML would be the best way. Otherwise use a [markdown table](https://stackoverflow.com/editing-help#tables). — Thom A, Feb 02 '23 at 16:27
I used markdown tables, hopefully they aren't too difficult to use as sample data — ted15555, Feb 02 '23 at 16:35
So what are all the other tables in your query then, if only 2 tables are *actually* involved? — Thom A, Feb 02 '23 at 16:36
Where I included product in the sample data its actually an ID code in our database however I know how to join the ID Codes from the cost pricing table and our table that attaches those IDS to product names so that is no issue. The other tables are the same as what I just explained except with SiteLocation and Department which again I have no issue being able to join those and attach the information I need hence the simplified sample data. — ted15555, Feb 02 '23 at 16:43
I think you need more sample data and results to explain your intent. For the data you have posted, your desired output appears to be just Table 1 filtered on the latest ChangeDate for each product, with no evidence that Table 2 even had a role in the results. That part can be done using techniques discussed in answers to [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group/48412942#48412942). This technique can be used to simultaneously select the most recent date and associated price from your `PLUCostHistory` table. — T N, Feb 03 '23 at 00:32
I understand that the sample data is very simplified but it still is somewhat sufficient to test what I am trying to do. The fact the most recent dates are both from table 1 is purely coincidental as it could just have easily have been from table 2 which is why I want the query to join the two tables and check them both for the newest cost available. I provided another row of sample data to each table that makes table 2 relevant in the outcome. — ted15555, Feb 03 '23 at 09:15
Question: What should happen if there is a product with identical ChangeDates (like two changes at the same day) but different costs? — Daniel Bürckner, Feb 03 '23 at 12:34
There is a time value attached to the ChangeDate column also which would make that incredibly unlikely (i didnt include it in my sample data as i tried to keep it as simple as possible) but if that somehow did occur then the higher of the two would be ideal to be chosen. — ted15555, Feb 03 '23 at 12:40

score 0 · Accepted Answer · answered Feb 03 '23 at 12:49

Nevermind my comment above. I came up with the following SQL fiddle, but replaced your long union subquery with a static copy of your data: http://sqlfiddle.com/#!18/7907d4/1

CREATE TABLE T_Items (
  ChangeDate date          NOT NULL,
  Product    nvarchar(200) NOT NULL,
  Cost       money         NOT NULL
)

INSERT INTO T_Items SELECT '01/01/2000', 'Water', 5;
INSERT INTO T_Items SELECT '06/01/2000', 'Banana', 2;
INSERT INTO T_Items SELECT '09/01/2000', 'orange', 2;
INSERT INTO T_Items SELECT '10/01/2000', 'Water', 3;
INSERT INTO T_Items SELECT '01/01/2000', 'Banana', 2.5;
INSERT INTO T_Items SELECT '08/01/2000', 'Water', 6;
INSERT INTO T_Items SELECT '03/01/2000', 'Banana', 1;
INSERT INTO T_Items SELECT '05/01/2000', 'Water', 3;
INSERT INTO T_Items SELECT '02/01/2000', 'Banana', 3;
INSERT INTO T_Items SELECT '12/01/2000', 'orange', 4;

SELECT TOP 1 WITH TIES
   ChangeDate,
   Product,
   Cost
FROM
  T_Items
ORDER BY ROW_NUMBER() OVER (PARTITION BY Product ORDER BY ChangeDate DESC);

You can replace the part T_Items with your union subquery making it look like this:

SELECT TOP 1 WITH TIES
   ChangeDate,
   Product,
   Cost
FROM
  (select  LastCostDate,
    [LocStock].SiteID,      
    [LastDate].PLUID,[...]) AS d1
ORDER BY ROW_NUMBER() OVER (PARTITION BY Product ORDER BY ChangeDate DESC)

Do not forget the AS d1 part. It's mandatory when using subqueries.

If I see this correctly, the tables you provided do not match your posted query as the column names differ (LastCostDate versus ChangeDate). It looks more like you posted the result of your INNER JOIN query:

select [PLUCostHistory].ChangeDate, [PLUCostHistory].PLUID, [PLU].Description, sum([PLUCostHistory].Cost) as Cost From [PLUCostHistory] Inner Join [PLU]
        on [PLU].PLUID=[PLUCostHistory].PLUID group by [PLUCostHistory].ChangeDate, [PLUCostHistory].PLUID, [PLU].Description ) as LastCost

So please adjust your question if this is not what you looked for.

Can I get the Last Date a product had its Cost changed and that relevant cost from a union table I have created?

1 Answers1