asked on

Get the max Time

Hi

I have a query that inserts and updates all records from a temp table.

The problem I am having is that there are some fields that are identical, the only thing that makes them different is the time field.

I want to update all records with the latests time field from my temp table to my live table

The query I am running is below, as you can see the time is a varchar and then I update it to a date and time when I import the data into the table

CREATE PROCEDURE sp_InsertNAVData
	@CreateDate as DateTime
AS
 
BEGIN
  SET NOCOUNT ON
 
	---Update the NAV Information if it exists already overweite NAV, Shares, Price ---
	UPDATE  tblClassNAV 
	
		SET 
			tblClassNAV.TotalNAV = NTemp.NAV,
			tblClassNAV.TotalShares = NTemp.Quantity,
			tblClassNAV.SharePrice = NTemp.UnitPrice,
			tblClassNAV.ClassCCY = NTemp.ShareCCY,
			tblClassNAV.ClassUnitPrice = NTemp.UnitPrice_CCY,
			tblClassNAV.ClassRate = NTemp.FXRate,
			tblClassNAV.CCY = NTemp.CCY,
			tblClassNAV.Consolidated = NTemp.Consolidated,
			tblClassNAV.FundName = NTemp.FundName,
			tblClassNAV.ISIN = NTemp.ISIN,
			tblClassNAV.PreviousPrice = NTemp.PreviousPrice,
			tblClassNAV.TNA = NTemp.TNA,
			tblClassNAV.UpdatedDate = @CreateDate
 
 
		FROM 
			tblNAV_Temp AS NTemp 
			INNER JOIN tblClassNAV  
			ON NTemp.FundCode = tblClassNAV.FCode
			AND NTemp.CShare = tblClassNAV.FSCode
			AND NTemp.NAVDate = tblClassNAV.NAVDATE
                                      
	INSERT INTO 
			tblClassNAV 
			(FCode, FSCode, NAVTime, NAVDate, MonthID, TotalNAV, TotalShares, SharePrice, 
			ClassCCY, ClassUnitPrice, ClassRate, CCY, Consolidated, 
			FundName, ISIN, PreviousPrice, TNA, CreateDate)
	
	SELECT 	DISTINCT
			tblNAV_Temp.FundCode, 
			tblNAV_Temp.CShare, 
			Cast(tblNAV_Temp.NAVDate + Case when Len(Cast(NAVTime as varchar(6)))=6 Then 
			Left(Cast(NAVTime as varchar(6)),2)
			+':'+ Right(Cast(NAVTime as varchar(4)),2)
			+':'+ Right(Cast(NAVTime as varchar(6)),2)		
			END AS DateTime) as 'NAVTime',
			tblNAV_Temp.NAVDate, 
			tblNAV_Temp.MonthID,
			tblNAV_Temp.NAV,
			tblNAV_Temp.Quantity,
			tblNAV_Temp.UnitPrice,
			tblNAV_Temp.ShareCCY,
			tblNAV_Temp.UnitPrice_CCY,
			tblNAV_Temp.FXRate,
			tblNAV_Temp.CCY, 
			tblNAV_Temp.Consolidated, 
			tblNAV_Temp.FundName, 
			tblNAV_Temp.ISIN, 
			tblNAV_Temp.PreviousPrice, 
			tblNAV_Temp.TNA,
			CreateDate = @CreateDate
 
	FROM  
		tblNAV_Temp 
	WHERE 	
	 	NOT EXISTS 
	(SELECT * FROM 
		tblClassNav
 	WHERE 
		tblNAV_Temp.FundCode = tblClassNAV.FCode 
	AND 	tblNAV_Temp.CShare = tblClassNAV.FSCode
	AND	tblNAV_Temp.NAVDate = tblClassNAV.NAVDate)
	
END 
 
GO

Open in new window

ASKER CERTIFIED SOLUTION

bprojoe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ITHELPME

ASKER

Thanks for your comment,
I have the primary keys set FundCode, ShareCode and NAVDate this is within my join

The insert part of it works it is not linking when I want to do an update.

I have tried the below, this gives me the correct result I am looking for but when I put the full thing together in the update section it will not get me the max time only I think it is because the prices are different

How do I get around this if some fields are different, I only want it to select the distinct values where there are more than one use the Time field to get the latest

SELECT  
			tblNAV_Temp.FundCode,
			tblNAV_Temp.CShare,
			tblNAV_Temp.CCY,
			tblNAV_Temp.Consolidated, 
			tblNAV_Temp.FundName,
			tblNAV_Temp.NAVDate,
			tblNAV_Temp.MonthID,
			tblNAV_Temp.ISIN,
			tblNAV_Temp.PreviousPrice,
			--tblNAV_Temp.Quantity,
			--tblNAV_Temp.UnitPrice,
			--tblNAV_Temp.NAV,
			--tblNAV_Temp.TNA,
			MAX(tblNAV_Temp.NAVTime) as 'NAV Time'
				
		FROM  
			tblNAV_Temp
		group by 
			tblNAV_Temp.FundCode, 
			tblNAV_Temp.CShare,
			tblNAV_Temp.CCY,
			tblNAV_Temp.Consolidated, 
			tblNAV_Temp.FundName,
			tblNAV_Temp.NAVDate,
			tblNAV_Temp.MonthID,
			tblNAV_Temp.ISIN,
			tblNAV_Temp.PreviousPrice

Open in new window

ITHELPME

ASKER

Thank you for your help
The problem was there was a trigger which was preventing the update. Once I deleted the trigger it all worked fine, I had tested it on the test server and all worked from there but triggers do not copy accross when you copy the table, learn something new every day, thanks again