Mapping Previous record to next record

[wmsalamanca]

Hello I'm trying to map a previous Fractional value next record. I believe is like adding the carrying forward to the next record.

Original Table						Query Output
Grant id	Per_No	Vest Date	Shares	Fractional		Grant id	Per_No	Vest Date	Shares	Fractional
12345​	1​	6/15/2021​	5.5252​	0.5252​		12345​	1​	6/15/2021​	5.5252​
12345​	2​	7/15/2021​	5.5253​	0.5253​		12345​	2​	7/15/2021​	5.5253​	0.5252​
12345​	3​	8/15/2021​	5.5254​	0.5254​		12345​	3​	8/15/2021​	5.5254​	0.5253​
12345​	4​	9/15/2021​	5.5255​	5.5255​		12345​	4​	9/15/2021​	5.5255​	0.5254​
12345​	5​	10/15/2021​	5.5256​	5.5256​		12345​	5​	10/15/2021​	5.5256​	5.5255​
12345​	6​	11/15/2021​	5.5257​	5.5257​		12345​	6​	11/15/2021​	5.5257​	5.5256​
12345​	7​	12/15/2021​	5.5258​	0.5258​		12345​	7​	12/15/2021​	5.5258​	5.5257​
12345​	8​	1/15/2022​	5.5259​	0.5259​		12345​	8​	1/15/2022​	5.5259​	0.5258​

I have the following query but is not working.

SELECT T1.[Grant Number], Combine.[Vest Date], T1.Per_No, T1.Shares, T1.Fraction, (SELECT TOP 1 Dupe.Fraction
FROM Combine AS Dupe
WHERE Dupe.[Grant Number] = T.[Grant Number]
AND Dupe.[Vest Date] < T1.[Vest Date]
AND Dupe.[Per_No]-T1.[Per_No] = -1
ORDER BY Dupe.[Vest Date] DESC, Dupe.[Grant Number]) AS PriorValue,Combine.[Base Date]
FROM T1
WHERE (((Combine.[Grant Number])="002686"));

 

[Pat Hartman]

Relational databases are unordered sets of data. Think about a bag of marbles. They're all there but when you pull one out, you don't know what you are going to get. You need to use a query and sort the data on something unique in order to ensure any particular sequence.

Use a report to do what you are doing.

 

[arnelgp]

SELECT yourTable.[Grant id],
    yourTable.Per_No,
    yourTable.[Vest Date],
    yourTable.Shares,
    (SELECT TOP 1 [ShareS] FROM [yourTable] AS T WHERE T.[Grant ID] = [yourTable].[Grant ID] AND T.[Vest Date] < [yourTable].[Vest Date] ORDER BY [Vest Date] DESC) AS Fractional 
FROM yourTable
ORDER BY yourTable.Per_No;

 

[wmsalamanca]

Thank you Arnelgp. The sql work, but I had to exclude the ORDER BY yourTable.Per_No; because the output was order by Per_No not by grant number. Thanks you.