You can use the LAG and LEAD analytic functions to access fields from relatively positioned rows. You just need to be sure that you are ordering the data correctly. From what you've shown it looks like you are partitioning the counts by CardCode,ItemCode and ordering by Amount . You can then use that in the OVER clause when using the analytic function. (They are also called a window functions.)

Specifically this will get the results you are after:

SELECT T2.CardCode,
  T2.ItemCode,
  T2.Amount AS 'From',
  LEAD([Amount], 1, NULL) OVER(PARTITION BY T2.CardCode, T2.ItemCode ORDER BY T2.Amount)-1 AS 'To',
  T2.Price
FROM SPP2 T2
WHERE T2.ItemCode IN ('QMB01','QMB02')
  AND T2.CardCode IN ('*12','*13');

Specifically this is using the LEAD() function (because we want the next row) and getting the [Amount] field offset by 1 row and will return NULL if there is no row in the partition group. The offset is in the context of the OVER clause which defines our partition group as CardCode,ItemCode and then orders the rows by Amount . To get 399 instead of 400 it then subtracts 1 (which will still give NULL for empty results.)

But do double-check the ordering and final values. You should be able to get it to work how you need it to.

What version of SQL Server are you using? An easy way of doing this may be the lead function .

Outside of that you can do a self join on CardCode and ItemCode where From in the first table is less than from in the second table. Something like this (using a temp table as an intermediary here, but you could do it on the base tables if you want)

SELECT 
    T2.CardCode,
    T2.ItemCode,
    T2.Amount AS 'From',
    'xxx' AS 'To',
    T2.Price
    INTO #temp
    FROM SPP2 T2
    WHERE T2.ItemCode IN ('QMB01','QMB02')
    AND T2.CardCode IN ('*12','*13')

select t1.CardCode
, t1.ItemCode
, t1.[From]
, t2.[From] - 1 as [To]
, t1.Price
from #temp t1
        left join #temp t2 on t1.CardCode = t2.CardCode 
                                and t1.ItemCode = t2.ItemCode
                                and t1.[From] < t2.[From]

Thanks for the help,

Code below shows how i used the "LEAD" Function to partition by both CardCode and ItemCode.

LEAD (T2.Amount,1) OVER (PARTITION BY T2.CardCode, T2.ItemCode ORDER BY T2.ItemCode) -1 AS V5B