Update With the Most Recent Entry

We have 2 Tables, tblMain, which contains unique products and tblHistory, which contains a history of the product Records are added to tblHistory daily and then the prices in tblMain are updated with those prices. The problem that we have is that since there are multiple instances of the same product in tblHistory, we're not always updating using the most recent price. Below is the current SQL that we are using.

Update TM SET TM.Price = TH.PriceHist,
	   TM.DateUpdated = TH.DateUpdatedHist
FROM tblHistory TH LEFT JOIN tblMain TM ON TH.ProdCode = TM.ProdCode 

Below is a sample of data in tblHistory and the records that I would need to be used for the update.

ProdID		PriceHist		DateUpdateHist
1			15.25			7/20/2015
1			17.15			7/15/2015
1			20.35			7/23/2015
5			20.10			7/25/2015

With the above. I would need corresponding records in tblMain to be updated with the entry on 7/23/2015 for ProdID 1 and with ProdID 5
Any Assistance would be appreciated. Thanks


July 24th, 2015 6:47am

Hi Dan

you could try something like this, the syntax may be off as i could not test it but concept should be fine.

 update tblMain 
 set Price = TH.PriceHist,
DateUpdated = TH.DateUpdatedHist
 from tblMain TM inner join
 (select Row_number() over (Partition by ProdId order by DateUpdateHist desc) [RowVer],ProdId,PriceHist,DateUpdatedHist from tblHistory) t
 on t.ProdId = TM.ProdId and t.[RowVer] = 1


Free Windows Admin Tool Kit Click here and download it now
July 24th, 2015 7:28am

declare @tblHistory table (ProdID int,		PriceHist int,		DateUpdateHist date)
insert into @tblHistory values
(1,			15.25,			'7/20/2015'),
(1,			17.15,			'7/15/2015'),
(1,			20.35,			'7/23/2015'),
(5,			20.10,			'7/25/2015')


declare @tblMain table (ProdID int,		Price int,		DateUpdated date)
insert into @tblMain values
(1,			15.25,			'6/20/2015'),
(2,			17.15,			'6/15/2015'),
(3,			20.35,			'6/23/2015'),
(5,			20.10,			'6/25/2015')

;with cte as (
select *,row_number() over (partition by prodid order by DateUpdateHist desc) as rn
from @tblHistory
)
update @tblMain 
SET Price = cte.PriceHist,
	DateUpdated = cte.DateUpdateHist
FROM cte LEFT JOIN @tblMain TM ON cte.ProdId = TM.ProdId
WHERE rn=1

select * from @tblMain 		
July 24th, 2015 7:34am

Thanks all. I will try both of these and let you know how it went
Free Windows Admin Tool Kit Click here and download it now
July 24th, 2015 10:13am

Ok, I over simplified it too much. I can't get it to work. Theres actually 3 tables that are used. I'll try to explain this as best as I can. 

There are actually 3 tables with this, which are below.

1. Master Table - Contains all customers, prices and last price update. There will be one one instance of a product and each customer has their own field for price and last updated. So, it appears like below.

'MTID     Product     CompADate     CompAPrice    CompBDate   CompBPrice
'1       ABC         7/15/2015       15.00      7/18/2015   17.50
'2       Test        7/17/2015       18.90      7/15/2015   15.25

2. Customer Product Table - Each customer e.g. CompA, CompB, etc. have their own Product Master table, which uses SKU as an identifer. For example sake, we'll say that the below is the CompA customer table

' 'CRID    SKU         Update          UpdatePrice
'200     13575        7/18/2015       18.00
'200     18312        7/10/2015       23.00
'200     19212        7/3/2015        15.00
'275     15312        7/3/2015        18.00
'275     11231        7/15/2015       18.90

3. Cross Reference Table - The SKUs in the customers table are generally a store driven id, which are not set in stone, so they do change. This means that the same exact product could have 15 different skus. Because of this, there is a cross-reference table.

'CRID   MT    SKU
'200    1     13575
'200    1     18312
'200    1     19212
'275    2     15312
'275    2     11231
The cross-reference table ties the Product Tables and Master Tables together. As you can see in the product table CRID 1 for example has 3 different skus but it is the same product. Using that as an example, we would want to Update the CompADate to 7/18/2015 CompAPrice 18.00 in the Master table. I hope that makes sense.


July 24th, 2015 10:28am

So, it still seems to be easy enough:

;with cte as (select CR.MT, CP.[Update], Cp.[UpdatePrice],

ROW_NUMBER() over (partition by CR.MT order by CP.[Update] DESC) as Rn

from [CustomerProduct] CP INNER JOIN [CrossReference] CR

ON CP.SKU = CR.SKU)

merge MasterTable MT using (select * from cte where Rn = 1) cte ON Mt.MtID = cte.MT

when matched then update

set ComAPrice = cte.UpdatePrice, compADate = cte.UpdateDate

-------------

So, if each customer has their own table you would have to run as many updates as you have cust

Free Windows Admin Tool Kit Click here and download it now
July 24th, 2015 10:44am

Hi Dan

you could try something like this, the syntax may be off as i could not test it but concept should be fine.

 update tblMain 
 set Price = TH.PriceHist,
DateUpdated = TH.DateUpdatedHist
 from tblMain TM inner join
 (select Row_number() over (Partition by ProdId order by DateUpdateHist desc) [RowVer],ProdId,PriceHist,DateUpdatedHist from tblHistory) t
 on t.ProdId = TM.ProdId and t.[RowVer] = 1


July 24th, 2015 11:28am

Hi Dan

you could try something like this, the syntax may be off as i could not test it but concept should be fine.

 update tblMain 
 set Price = TH.PriceHist,
DateUpdated = TH.DateUpdatedHist
 from tblMain TM inner join
 (select Row_number() over (Partition by ProdId order by DateUpdateHist desc) [RowVer],ProdId,PriceHist,DateUpdatedHist from tblHistory) t
 on t.ProdId = TM.ProdId and t.[RowVer] = 1


Free Windows Admin Tool Kit Click here and download it now
July 24th, 2015 11:28am

>> We have 2 Tables, tblMain, which contains unique products and tblHistory, which contains a history of the product <<

Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI-ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You are a failrue. You should follow ISO-8601 rules for displaying temporal data. Again a total failure! Is it embarassign to be told this in a pulbic forum by someone who has credentials in this language? I hope so; maybe you will learn. 

We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI-ISO Standard SQL. 

And you need to read and download the PDF for: https:--www.simple-talk.com-books-sql-books-119-sql-code-smells-

the tbl_ affix on the table is called a tibble and we laugh at it  for the incompetentcy shown. When we pull them off, we have to ask about the vague generic main and history as tables. That main is really awful; this what we called the master tape or network DB file back in the 1970's. It has nothing whats over with RDBMS. 

>> Records [sic] are added to tblHistory [sic] daily and then the prices in tblMain [sic] are updated with those prices. <<

Please stop programming;  it is painful to read your non-code. I spent a decade of my life on SQL and this hurts to see. I feel like musician  hearing bad bagpipe music :(   Your crap is not how to create a history table. 

https://www.simple-talk.com/sql/t-sql-programming/temporal-data-techniques-in-sql-/

July 24th, 2015 2:42pm

Hi DanHaf,

Based on my understanding on your requirement, the MTID 2 is not updated, is it due to the last entry in Customer table earlier than the row in Master? If your logic is only update the rows in Master which have a later entry in Customer, you can reference sample.

DECLARE @Master TABLE
(
MTID INT,
Product VARCHAR(10),
CompADate DATE,
CompAPrice Money,
CompBDate DATE,
CompBPrice Money
);

INSERT INTO @Master
VALUES
(1,'ABC','20150715',15,'20150718',17.5),
(2,'Test','20150717',18.9,'20150715',15.25); 

SELECT * FROM @Master

DECLARE @Customer TABLE
(
CRID INT,
SKU INT,
[Update] DATE,
UpdatePrice Money
);
INSERT INTO @Customer 
VALUES
(200,13575,   '7/18/2015',  18.00),
(200,18312,   '7/10/2015',  23.00),
(200,19212,   '7/3/2015',   15.00),
(275,15312,   '7/3/2015',   18.00),
(275,11231,   '7/15/2015',  18.90);
 

DECLARE @CrossRef TABLE
(
CRID INT,
MT INT,
SKU INT
);

INSERT INTO @CrossRef
VALUES
(200,    1,     13575),
(200,    1,     18312),
(200,    1,     19212),
(275,    2,     15312),
(275,    2,     11231);
 
;WITH Cte AS
(
SELECT M.*,C.*
	FROM @Master M INNER JOIN @CrossRef CR
					ON M.MTID=CR.MT
				   INNER JOIN @Customer C
					ON CR.CRID=C.CRID AND CR.SKU=C.SKU
)
UPDATE c 
SET CompADate=c.[Update],CompAPrice=c.UpdatePrice
FROM Cte c
WHERE c.CompADate<c.[Update] AND NOT EXISTs(SELECT 1 FROM Cte WHERE c.[Update]<[Update] AND MTID=C.MTID)

SELECT * FROM @Master

If you have any feedback on our support, you can click here
July 26th, 2015 11:08pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics