T-SQL if value exists use it other wise use the value before

I have the following table

-----Account#----Period-----Balance

• 12345---------200901-----$11554
• 12345---------200902-----$4353
• 12345 --------201004-----$34
• 12345 --------201005-----$44
• 12345---------201006-----$1454
• 45677---------200901-----$14454
• 45677---------200902-----$1478
• 45677 --------201004-----$116776
• 45677 --------201005-----$996
• 56789---------201006-----$1567
• 56789---------200901-----$7894
• 56789---------200902-----$123
• 56789 --------201003-----$543345
• 56789 --------201005-----$114
• 56789---------201006----开发者_运维问答-$54

I want to select the account# that have a period of 201005. This is fairly easy using the code below. The problem is that if a user enters 201003-which doesnt exist- I want the query to select the previous value.*NOTE that there is an account# that has a 201003 period and I still want to select it too.*

I tried CASE, IF ELSE, IN but I was unsuccessfull. PS:I cannot create temp tables due to system limitations of 5000 rows. Thank you.

DECLARE @INPUTPERIOD INT
@INPUTPERIOD ='201005'

SELECT ACCOUNT#, PERIOD , BALANCE
FROM TABLE1
WHERE PERIOD =@INPUTPERIOD

---

SELECT t.ACCOUNT#, t.PERIOD, t.BALANCE
    FROM (SELECT ACCOUNT#, MAX(PERIOD) AS MaxPeriod
              FROM TABLE1
              WHERE PERIOD <= @INPUTPERIOD
              GROUP BY ACCOUNT#) q
        INNER JOIN TABLE1 t
            ON q.ACCOUNT# = t.ACCOUNT#
                AND q.MaxPeriod = t.PERIOD

---

select top 1 account#, period, balance
from table1
where period >= @inputperiod

---

; WITH Base AS
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY Period DESC) RN FROM #MyTable WHERE Period <= 201003
)

SELECT * FROM Base WHERE RN = 1

Using CTE and ROW_NUMBER() (we take all the rows with Period <= the selected date and we take the top one (the one with auto-generated ROW_NUMBER() = 1)

; WITH Base AS
(
    SELECT *, 1 AS RN FROM #MyTable WHERE Period = 201003
)
, Alternative AS
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY Period DESC) RN FROM #MyTable WHERE NOT EXISTS(SELECT 1 FROM Base) AND Period < 201003
)
, Final AS
(
    SELECT * FROM Base
    UNION ALL
    SELECT * FROM Alternative WHERE RN = 1
)

SELECT * FROM Final

This one is a lot more complex but does nearly the same thing. It is more "imperative like". It first tries to find a row with the exact Period, and if it doesn't exists does the same thing as before. At the end it unite the two result sets (one of the two is always empty). I would always use the first one, unless profiling showed me the SQL wasn't able to comprehend what I'm trying to do. Then I would try the second one.