Khalid Hanif-458693 (7/16/2013)
The columns would beprevcostcenter, prevpaymentcode, previnvno, nextcostcenter, nextpaymentcode, nextinvno
I have tried the rownumber the estimated rows in the execution plan is always the total rows Iin the table. If there are 100,000 rows it take 1-2 seconds to get the data.
Well this is one rather wordy approach, wonder how it would do??? Pretty much a straight compare.
DECLARE @Current_Cost_Center_code int
DECLARE @Current_Payment_code int
DECLARE @Current_INV_No int
SET @Current_Cost_Center_code = 1
SET @Current_Payment_code = 4
SET @Current_INV_No = 2
SELECT * FROM
(
SELECT TOP 1 'Previous' WHICH_ROW,
Cost_Center_code,
Payment_code,
INV_No
FROM INVOICE_T
WHERE Cost_Center_code < @Current_Cost_Center_code
OR (Cost_Center_Code = @Current_Cost_Center_code AND Payment_code < @Current_Payment_code)
OR (Cost_Center_Code = @Current_Cost_Center_code AND Payment_code = @Current_Payment_code AND INV_No < @Current_INV_No)
ORDER BY Cost_Center_code DESC,
Payment_code DESC,
INV_No DESC
) PREV
UNION
SELECT * FROM
(
SELECT TOP 1 'Next' WHICH_ROW,
Cost_Center_code,
Payment_code,
INV_No
FROM INVOICE_T
WHERE Cost_Center_code > @Current_Cost_Center_code
OR (Cost_Center_Code = @Current_Cost_Center_code AND Payment_code > @Current_Payment_code)
OR (Cost_Center_Code = @Current_Cost_Center_code AND Payment_code = @Current_Payment_code AND INV_No > @Current_INV_No)
ORDER BY Cost_Center_code ASC,
Payment_code ASC,
INV_No ASC
) NEXT