SQL Stored Procedure Return Next Lowest Record

Transact-SQL
1

I'm working with a sql table that contains a column for Year1, PeirodId, Actindx and PeriodBalance. The table gets populated with summary data as transactions from another application are processed. If no transactions are processed for a given Year, Period and Actindx then no new record set exists. For example the data table has:

Year1, PeriodId, Actindx, PeriodBalance
2015, 1, 6, 10012.32
2015, 2, 6, 2312.45
2015, 3, 6, 328.08
2015, 7, 6, 49234.82

I'm trying to create a stored procedure that will return the Period Balance for the Year, Periodid and Actindx. This works fine if a record exists in the table for that combination. For example if I pass the following parameters to my stored procedure '2015', 2, 6 it returns 2312.45. However if I pass '2015', 5, 6 nothing is returned which makes sense, however I want it to then return 328.08 (the highest returned PeriodId which is lower than the value passed). So my stored procedure will send 2015, 5, 6 but the returned value would be for the recordset 2015, 3, 6 or 328.08

2 
select top 1 *
from   table
where  (Year1 * 100) + PeriodId < (2015 * @year) + @month
order by Year1 desc, Periodid desc

You will also want to create a PERSISTED computed column for (Year1 * 100) + PeriodId for performance reason

3

You NEVER want to use a function on a table column in a WHERE clause unless it's absolutely unavoidable, because SQL then can't make best use of any index(es). [The technical term is that it makes it "non-sargable".]

Especially in this case, since presumably the table will be clustered on ( Year1, PeriodId, Actindx ), any function would force a full table scan versus a quick index seek.

DECLARE @Year1 smallint
DECLARE @PeriodId tinyint
DECLARE @Actindx smallint

SET @Year1 = 2015
SET @PeriodId = 5
SET @Actindx = 6

SELECT TOP (1) *
FROM table_name
WHERE Year1 = @Year1
  AND PeriodId <= @PeriodId
  AND Actindx = @Actindx
ORDER BY PeriodId DESC
4

Why materialize this? An SQL programmer would use VIEW that is always current. Back in the daYs of tapes and punch cards, we had to PHYSICALLY materialize data.

5

How about cross year scenario ?

What if there isn't any record for 2015 and the last record is 2014 ?

6

Scott, thanks worked perfectly!

7

Then you write the WHERE conditions according, if necessary even loading the relevant years and periodids into a separate table to join to. Again, you would use a function / calculation on the table columns themselves ONLY if you have NO other reasonable way to write the code.