SQLTeam.com | Weblogs | Forums

SQL Stored Procedure Return Next Lowest Record


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

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


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

FROM table_name
WHERE Year1 = @Year1
  AND PeriodId <= @PeriodId
  AND Actindx = @Actindx


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.


How about cross year scenario ?

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


Scott, thanks worked perfectly!


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.