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:
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
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
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.
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.