sql - Select latest NOT NULL values from table -

i have table many statuses like

id | date | isenabled | isupdated | isduplicate | issuspended | ... 

statuses (isenabled, isupdated, isduplicate, issuspended...) nullable bits.

i need select latest (but not greater input date) not nullable statuses table. in case status has null value select previous not nullable value.

i've create select select latest values , can't understand how previous not nullable values.

;with cte (      select  cbs.*, rn = row_number() on (partition cbs.id order cbs.[date] desc)        [dbo].companybusinessstatus cbs     cbs.[date] <= @inputdate )  select  *    cte rn = 1 

i'm using ms sql 2016

---

data example :

1 | 2017-01-01 | 1 | 0    | 0    | 0 _______________________________________ 1 | 2017-01-03 | 1 | null | null | 1 _______________________________________ 2 | 2017-01-03 | 1 | 1    | null | 0 _______________________________________ 1 | 2017-01-05 | 0 | 1    | 0    | null 

in case @inputdate '2017-01-04' need select

   id | isenabled | isupdated | isduplicate | issuspended  _________________________________________________________     1 | 1         | 0         | 0           | 1 _________________________________________________________     2 | 1         | 1         | null        | 0 

one way (demo) be

select id,        isenabled = cast(right(max(yyyymmdd + cast(isenabled char(1))), 1) bit),        isupdated = cast(right(max(yyyymmdd + cast(isupdated char(1))), 1) bit),        isduplicate = cast(right(max(yyyymmdd + cast(isduplicate char(1))), 1) bit),        issuspended = cast(right(max(yyyymmdd + cast(issuspended char(1))), 1) bit)   dbo.companybusinessstatus cbs        cross apply (select format(date, 'yyyymmdd')) ca(yyyymmdd)  cbs.[date] <= @inputdate group  id  

if have covering index on id (or if don't hash aggregate) can produce plan no sort operations @ , may cheaper gordon's answer.